Erklärt: Explain

[akretschmer]

Der EXPLAIN-Befehl dient dazu, den Abfrageplan eine Abfrage zu sehen.

Was ist ein Abfrageplan?

Immer, wenn es eine Abfrage auszuführen gibt, ermittelt der Planner, wie er diese Abfrage 'günstig' im Sinne von schnell und ohne viel Aufwand durchführen kann. Oft gibt es mehrere Alternativen: z.B., ob vorhandene Indexe genutzt werden sollen oder nicht.

Manch einer wird sich jetzt fragen: wenn ein Index da ist, dann sollte er doch auch genutzt werden!


Nun, PostgreSQL nutzt ein 'Kostensystem': Kosten haben keine Einheit, also weder Euro oder Sekunden. Sie geben an, wie aufwändig der Planner bestimmte Operationen einschätzt. So kostet das Lesen eines Blocks sequentiell von Platte 1, ein zufälliger Zugriff auf einen Block auf Platte kostet 4, da hier (bei mechanischen Festplatten) Positionierungszeiten dazukommen.

Das lesen eines Datensatzes über einen Index ist naturgemäß ein Random-Read, da aus dem Index der Datenblock-Index des gesuchten Tupels ermittelt wird und dann zielstrebig dieser Block gelesen wird. Bei einem sequentiellen Lesen wird die ganze Tabelle komplett Block-für-Block gelesen, hier fallen zwischen den Blöcken keine Positionierungen des Festplattenkopfes an.

Dies hat zur Folge, daß bei kleinen Tabellen ein komplettes Lesen der ganzen Tabelle 'günstiger' ist als den Index zu nutzen. Hier eine Demo dazu:

test=*# create table demo (id serial primary key);
NOTICE:  CREATE TABLE will create implicit sequence "demo_id_seq" for serial column "demo.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "demo_pkey" for table "demo"
CREATE TABLE
Time: 90,353 ms
test=*# insert into demo select * from generate_series(1,10) s;
INSERT 0 10
Time: 37,629 ms
test=*# analyse demo;
ANALYZE
Time: 22,630 ms
test=*# explain select * from demo where id=5;
                     QUERY PLAN
----------------------------------------------------
 Seq Scan on demo  (cost=0.00..1.12 rows=1 width=4)
   Filter: (id = 5)
(2 rows)

Das ist also eine kleine Tabelle mit nur 10 Datensätzen. Es ist zwar ein Index da (aufgrund des PRIMARY KEY), der zur Abfrage durchaus genutzt werden könnte, aber der Planner entscheidet sich für einen Sequentiellen Scan.

Füllen wir die Tabelle mit mehr Datensätzen:

test=*# truncate demo;
TRUNCATE TABLE
Time: 5,372 ms
test=*# insert into demo select * from generate_series(1,1000000) s;
INSERT 0 1000000
Time: 6445,338 ms
test=*# analyse demo;
ANALYZE
Time: 158,440 ms
test=*# explain select * from demo where id=5;
                                QUERY PLAN
---------------------------------------------------------------------------
 Index Only Scan using demo_pkey on demo  (cost=0.00..9.14 rows=1 width=4)
   Index Cond: (id = 5)
(2 rows)

Selbe Tabelle, selber Index, aber 1 Million Datensätze. Nun erfolgt ein Index-Scan, da dieser vom Planner als 'günstiger' erachtet wird!


Beim EXPLAIN-Befehl unter PostgreSQL gibt es noch 2 grundlegende Varianten zu beachten:

wird die Option 'analyse' angegeben, so wird nicht nur der Abfrageplan ermittelt, sondern die Abfrage auch ausgeführt. Bei der Ermittlung des Planes erfolgt auch eine Abschätzung der Anzahl der Ergebniszeilen (rows=...), bei EXPLAIN ANALYSE wird nun auch die 'reale' Größe angezeigt:

test=*# explain analyse select * from demo where id=5;
                                                     QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
 Index Only Scan using demo_pkey on demo  (cost=0.00..9.14 rows=1 width=4) (actual time=0.139..0.140 rows=1 loops=1)
   Index Cond: (id = 5)
   Heap Fetches: 1
 Total runtime: 0.163 ms
(4 rows)

Im Bereich hinter 'actual' finden wir die Zeit für die Operation und hinter rows=... die reale Größe.

Faustregel: die geschätzen und die realen Mengen sollten nicht mehr als eine Zehnerpotenz auseinander liegen!


Neugierig geworden?

Explain und Kostenermittlung unter PostgreSQL sind ein sehr komplexes Thema. Als DBA hat man einigen Einfluß darauf, so kann man z.B. für unterschiedliche Tablespaces (SATA-Platten, SAS-Platten, SSD-Platten) unterschiedliche Kosten für den sequentiellen Zugriff definieren und damit den Planner steuern, ob Index- oder Seq. Scan zu bevorzugen ist. Wichtig ist, erst einmal zu verstehen, wie der Planner arbeitet.

All das hier zu erklären würde zu weit führen, wer Interesse auf mehr Details hat wird bei Google mit dem Stichworten "postgresql explaining explain" jede Menge weiterer Doku dazu finden.

Das zu lesen lohnt sich!

 

[mic.wendt]

Hallo,

Ich bin neu hier im Forum und arbeite seit längerer Zeit mit PostgreSQL, habe mich allerdings nie tiefer in die Materie hineingearbeitet.
Ich verwalte einige PG-DB's und musste mich nun doch mal mit dem Thema Performance von Abfragen auseinandersetzen, daher finde ich diesen Beitrag sehr interessant.

Das wollte ich hier nur einmal an den Schreiber weitergeben, da der sich sicher auch einmal über eine Resonanz für einen informellen Artikel freut.


Gruß

 

[akretschmer]

Hallo,

Ich bin neu hier im Forum und arbeite seit längerer Zeit mit PostgreSQL, habe mich allerdings nie tiefer in die Materie hineingearbeitet.
Ich verwalte einige PG-DB's und musste mich nun doch mal mit dem Thema Performance von Abfragen auseinandersetzen, daher finde ich diesen Beitrag sehr interessant.

Das wollte ich hier nur einmal an den Schreiber weitergeben, da der sich sicher auch einmal über eine Resonanz für einen informellen Artikel freut.


Gruß

Willkommen und Danke schön ;-)

Falls Du da Fragen hast: einfach fragen.


PS.: es gibt im Forum hier auch den 'Gefällt mir' - Button.