PostgreSQL^® Optimizer Bits - Custom Query Plans

Jede Anwendung oder Anwender kommt direkt oder indirekt mit ihnen in Berührung: Prepared Statements, oder auf gut Deutsch, Vorbereitete Abfragen. Hierbei handelt es sich einfach erklärt um parametrisierte Abfragen, die einfach wiederverwendet werden können. Ziel ist es, die Analyse und Planerstellung für die späteren Ausführungen der Abfrage nur einmal durchzuführen und den dabei entstehenden Abfrageplan einfach wieder zu verwenden. In PostgreSQL^® kommen Prepared Statements in vielfältiger Weise zum Zuge: Per SQL mittels PREPARE und EXECUTE Kommandos oder auf Protokollebene, indem der Datenbanktreiber wie JDBC oder DBD:Pg hierfür per API-Aufrufe mit bspw. execute() diese implizit verwenden.

Fast jede PostgreSQL^®-Anwendung nutzt heutzutage daher Prepared Statements. Bis PostgreSQL^® 9.1 hatte diese Funktionalität jedoch eine Schwachstelle. Wurde eine Abfrage vorbereitet, so erzeugte der PostgreSQL^® Optimizer stets einen sogenannten generischen Plan. Ein Plan ist eine Art Arbeitsanweisung, wie die Abfrage innerhalb der Datenbank abgewickelt werden soll. Mittels PREPARE wird zum Beispiel zur Laufzeit bis PostgreSQL^® 9.1 ein generischer Abfrageplan für alle weiteren Ausführungen dieses Prepared Statements festgelegt:

PREPARE ps_get_customerid(text) AS SELECT customerid FROM customers WHERE username LIKE $1;

Dies erzeugt ein Prepared Statement mit dem Bezeichner ps_get_customer_id, das entsprechend mit EXECUTE wiederverwendet werden kann:

EXECUTE ps_get_customerid('user26');

Die zugrundeliegende Abfrage nutzt den LIKE Operator, um auch eine Prefixsuche zu ermöglichen:

EXECUTE ps_get_customerid('user26%');

Beide Abfragen haben jedoch denselben generischen Abfrageplan:

EXPLAIN EXECUTE ps_get_customerid('user26');
                          QUERY PLAN
 -------------------------------------------------------------
  Seq Scan on customers  (cost=0.00..738.00 rows=100 width=4)
    Filter: ((username)::text ~~ $1)
 (2 rows) 
 
 EXPLAIN EXECUTE ps_get_customerid('user26%');
                          QUERY PLAN
 -------------------------------------------------------------
  Seq Scan on customers  (cost=0.00..738.00 rows=100 width=4)
    Filter: ((username)::text ~~ $1)
 (2 rows)

Beide Abfragen könnten jedoch auch einen Index verwenden, denn die customers Tabelle definiert bereits einen Index mit text_pattern_ops Operatorklasse über die username Spalte. Dies ermöglicht die Indexbenutzung auch für LIKE-Operatoren, die das Patternmatching am Ende des Suchbegriffes verwenden. Die verwendete Indexdefinition sei hier der vollständighalber ebenfalls genannt:

CREATE INDEX ON customers (username text_pattern_ops);

Die Abfrage direkt ohne PREPARE auszuführen zeigt das Dilemma:

EXPLAIN SELECT * FROM customers WHERE username LIKE 'user26%';
                                                QUERY PLAN
 --------------------------------------------------------------------------------------------------------
  Bitmap Heap Scan on customers  (cost=6.30..382.35 rows=202 width=268)
    Filter: ((username)::text ~~ 'user26%'::text)
    ->  Bitmap Index Scan on customers_username_idx  (cost=0.00..6.25 rows=200 width=0)
          Index Cond: (((username)::text ~>=~ 'user26'::text) AND ((username)::text ~<~ 'user27'::text))
 (4 rows)

In PostgreSQL^® 9.1 kann dieser Index in Prepared Statements jedoch nicht verwendet werden. Der generische Abfrageplan kann eine derartige Parametrisierung nicht berücksichtigen. Ab PostgreSQL^® 9.2 jedoch versucht der Optimizer einen spezifischen Abfrageplan für jeden möglichen Zugriffspfad zu erzeugen. Die Tiefe ist jedoch begrenzt, maximal fünf Pläne werden berücksichtigt. Diese Pläne nennen sich Custom Plans und können auch parametrisierte Zugriffspade berücksichtigen. Wiederholt man das Beispiel von oben ergibt sich in PostgreSQL^® 9.3 dann folgendes Bild:

EXPLAIN SELECT * FROM customers WHERE username LIKE 'user26%';
                                                QUERY PLAN
 --------------------------------------------------------------------------------------------------------
  Bitmap Heap Scan on customers  (cost=6.30..382.35 rows=202 width=268)
    Filter: ((username)::text ~~ 'user26%'::text)
    ->  Bitmap Index Scan on customers_username_idx  (cost=0.00..6.25 rows=200 width=0)
          Index Cond: (((username)::text ~>=~ 'user26'::text) AND ((username)::text ~<~ 'user27'::text))
 (4 rows)
 
 EXPLAIN EXECUTE ps_get_customerid('user26%');
                                                QUERY PLAN
 --------------------------------------------------------------------------------------------------------
  Bitmap Heap Scan on customers  (cost=6.30..382.35 rows=202 width=4)
    Filter: ((username)::text ~~ 'user26%'::text)
    ->  Bitmap Index Scan on customers_username_idx  (cost=0.00..6.25 rows=200 width=0)
          Index Cond: (((username)::text ~>=~ 'user26'::text) AND ((username)::text ~<~ 'user27'::text))
 (4 rows)

Der Optimizer wählt nun denselben Ausführungsplan für beide Abfragen, analog zur direkt ausgeführten Abfrage.

Besonders interessant ist dies für alle implizit erzeugten Prepared Statements. Diese spielen eine besondere Rolle in PL/pgSQL, da hier jede Abfrage implizit ein Prepared Statement ist. Der Artikel zur Serie

CREATE SEQUENCE test_id_seq;
CREATE TABLE test
AS
SELECT nextval('test_id_seq'::regclass), t.datum 
FROM generate_series('2008-01-01'::timestamptz, '2013-12-31'::timestamptz, interval '1 hour') AS t(datum);
 
CREATE INDEX ON test(datum);
CREATE OR REPLACE FUNCTION get_test_datum_ids(p_datum timestamp)
RETURNS SETOF integer
STABLE
LANGUAGE plpgsql
AS
$$
 DECLARE
    v_id int;
 BEGIN
    FOR v_id IN SELECT * FROM test WHERE datum < p_datum
    LOOP
       RETURN
          NEXT v_id;
    END LOOP; 
    RETURN;
 END;
 $$;