02 August 2013

PostgreSQL Optimizer Bits - Custom Query Plans

Kategorien: PostgreSQL
Tags: PostgreSQL

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 PostgreSQL Optimizer Bits – Auto Explain hatte sich am Ende seiner Ausführungen bereits mit dieser Problematik am Rande befasst. Wiederholen wir das Beispiel aus diesem Artikel mit folgendem Listing, so ergibt sich mit PostgreSQL 9.2 ein völlig anderes Bild:

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;
 $$;

Analog zum Artikel wird anschließend die Analyse mittels auto_explain wiederholt:

LOAD 'auto_explain';
SET auto_explain.log_min_duration TO '0ms';
SET auto_explain.log_nested_statements TO 'on';
SELECT get_test_datum_ids('01.02.2008'::timestamp);

Ist auto_explain korrekt konfiguriert, findet man den Abfrageplan für den SELECT innerhalb der FOR-Schleife der Prozedur im Logfile der PostgreSQL-Instanz:

	Query Text: SELECT * FROM test WHERE datum < p_datum
 	Index Scan using test_datum_idx on test  (cost=0.00..33.89 rows=779 width=16)
 	  Index Cond: (datum < '2008-02-01 00:00:00'::timestamp without time zone)

Es zeigt sich, dass der Optimizer nun tatsächlich einen Custom Plan auf Basis eines Indexscan für diesen Parameter wählt. In PostgreSQL 9.1 wäre dies ein generischer Plan, der in diesem Beispiel einfach wieder einen sequentiellen Scan (SeqScan) der Tabellen zur Folge hätte (sieher hierzu auch das Listing im bereits genannten vorhergehenden Blogartikel dieser Serie). Dies vereinfacht die Entwicklung solcher Prozeduren nun erheblich, da auf dynamische Abfragen mittels EXECUTE in PL/pgSQL verzichtet werden kann.

Kategorien: PostgreSQL
Tags: PostgreSQL


Beitrag teilen: