PostgreSQL Abfragepläne anzeigen mit EXPLAIN

Der Ausführungsplan, den der Planner erstellt hat, kann mit dem Kommando EXPLAIN angezeigt werden. In der Ausgabe von EXPLAIN kann man ablesen, wie auf welche Tabellen zugegriffen wurde, ob und welche Indexe benutzt wurden, welche Join-Verfahren eingesetzt wurden ... sowie die Anzahl der beteiligten Datensätze.

EXPLAIN gibt die Struktur des Ausführungsplans zurück, die zugegebenermaßen am Anfang nicht einfach zu interpretieren ist. Der Ausführungsplan ist eine Abbildung des Abfragebaums, d.h. die Knoten des Abfragebaums werden auf Knoten des Plans abgebildet. Die verschiedenen Zugriffsarten auf die Tabellen (seq scan, index scan oder bitmap index scan) werden als verschiedene Knoten im Ausführungsplan angegeben. Außerdem kann man im Abfrageplan ablesen, welche Operationen, wie etwa Joins, Sortierungen oder Aggregierungen durchgeführt werden.

phpugs=# explain select anf_datum, adr_a_an_i_d, anfrager, chiffre
         from anfrage where adr_i_an_a_d >  '2007-03-15' and enddate is null;
                                 QUERY PLAN
------------------------------------------------------------------------------
 Seq Scan on anfrage  (cost=0.00..1304.66 rows=2 width=24)
   Filter: ((adr_i_an_a_d > '2007-03-15 00:00:00+01'::timestamp with time zone)
             AND (enddate IS NULL))

EXPLAIN ANALYZE führt zusätzlich die Abfrage aus und gibt die tatsächlichen Werte, vor allem aber die tatsächliche Ausführungszeit, zurück. Aber Vorsicht, wenn das zu testende Statement ein INSERT, UPDATE oder DELETE ist. (Die actual-Werte sind der Übersichtlichkeit wegen zukünftig weggelassen.)

phpugs=# explain analyze select anf_datum, adr_a_an_i_d, anfrager, chiffre
         from anfrage where adr_i_an_a_d > '2007-03-15' and enddate is null;
                           QUERY PLAN
------------------------------------------------------------------------------
 Seq Scan on anfrage  (cost=0.00..1304.66 rows=2 width=24)
                      (actual time=5.666..31.197 rows=6 loops=1)
   Filter: ((adr_i_an_a_d > '2007-03-15 00:00:00+01'::timestamp with time zone)
             AND (enddate IS NULL))
 Total runtime: 69.512 ms

• cost (cost=0.00..1304.66 ... ist eine Abschätzung, wie teuer die Abfrage sein wird. Angegeben sind keine Sekunden, sondern eine Maßeinheit für voraussichtliche Plattenzugriffe (1 bedeutet die Zeit für einen sequentiellen Lesevorgang für eine Speicherseite). Die erste Zahl gibt an, wieviel Aufwand voraussichtlich nötig ist, bis der erste Datensatz des Ergebnisses zurückzugeben werden kann, die zweite Zahl gibt den geschätzten Gesamtaufwand an. Übergeordnete Knoten enthalten die Kosten aller ihrer Kindknoten.
• rows gibt die geschätzte Anzahl der Datensätze im Ergebnis an. Das ist nicht die Anzahl der Zeilen, die verarbeitet oder gescannt werden, sondern meistens weniger, weil der Planner die Selektivität von WHERE-Klauseln einberechnet. Diese ist im Knoten Filter angegeben.
• width ist die durchschnittliche Größe eines Datensatzes in Byte.
• Filter werden auf jeden Datensatz, der gescannt wird, angewendet (wenn ein Seq Scan gemacht wird, auf alle).

Auf die Tabelle anfrage wurde ein Seq Scan gemacht. Die Tabelle wurde von Anfang bis Ende durchlaufen und die WHERE-Klausel der Abfrage (= Filter) wurde auf jeden Datensatz angewendet.

Der Grund: in der WHERE-Klausel ist eine Bedingung für das Feld adr_i_an_a_d angegeben, welches nicht indiziert ist. Das Feld enddate ist zwar indiziert, aber der Index kann in einem Vergleich gegen NULL nicht benutzt werden. "Since most indexable operators are strict and hence cannot return TRUE for NULL inputs."

Zum Vergleich:

Dieselbe Abfrage mit der Bedingung anf_datum > '2007-03-15', anf_datum ist indiziert.

phpugs=# explain analyze select anf_datum, adr_a_an_i_d, anfrager, chiffre
        from anfrage where anf_datum > '2007-03-15' and enddate is null;
                                   QUERY PLAN
-----------------------------------------------------------------------------
 Index Scan using i_anf_datum on anfrage  (cost=0.00..49.56 rows=2 width=24)
   Index Cond: (anf_datum > '2007-03-15 00:00:00+01'::timestamp with time zone)
   Filter: (enddate IS NULL)
 Total runtime: 0.353 ms

Index Scan: Anstelle der gesamten Tabelle wird ein Index durchlaufen und die passenden Datensätze aus der Tabelle gelesen. Der Index Scan beginnt erst ab Index Cond anf_datum > '2007-03-15' zu lesen. Anhand der Ausführungszeiten von knapp 70 ms und 0,3 ms kann man deutlich sehen, welche Auswirkungen ein Index auf die Ausführungsgeschwindigkeit haben kann.