PostgreSQL besitzt einen kostenbasierten Planer. Um eine an die Datenbank gestellte Anfrage auszuführen, ermittelt dieser mögliche Strategien, um diese Anfrage auszuführen, und vergleicht die zu erwartenden Kosten dieser Abfragepläne.
Die Kosten sind fiktive Werte, die für verschiedene Operationen wie z.B. den sequentiellem oder zufälligen Lesens eines Blockes von Festplatte, Berechnen von Indexoperationen, Berechnen von Operationen an sich (z.B. mathematische Operationen innerhalb der DB) anfallen.
Bei der Planung von Abfragen werden auch eine Reihe statistischer Daten über die eigentlichen Nutzdaten in den Tabellen benutzt, um die zu erwartende Ergebnismenge abschätzen zu können. Dazu führt die Datenbank interne Statistiken, die u.B. die ungefähre Anzahl von Datensätzen pro Tabelle, die Verteilung von Werten innerhalb einer Spalte (Histogramm), Informationen über den Sortierzustand in einer Tabelle (z.B. wird eine Tabelle, die Logeinträge speichert und eine Spalte mit dem Zeitstempel führt, in dieser Spalte bereits physisch sortiert sein).
Ich möchte aber auf all diese Details nicht weiter eingehen, dazu ist bereits mehr als genug von anderen geschrieben worden, z.B. hier: Image:Explaining EXPLAIN.pdf - PostgreSQL Wiki ,vielmehr möchte ich eine kurze Demo geben, wie man EXPLAIN pratisch einsetzt.
Okay.
Ich habe eine abelle, die Wörter enthält:
test=*# \d words;
Table "public.words"
Column | Type | Modifiers
--------+------+-----------
t | text |
test=*# select count(*) from words;
count
-------
98569
(1 row)
Ich suche nun alle Wörter, die mit ‘e’, ‘s’ oder ‘t’ beginnen:
test=*# select t from words where t ~ '^[est].*' order by t;
Time: 503.298 ms
test=*#
Eine halbe Sekunde. Ist mir zu lang …
Okay, ich erinnere mich dunkel, daß Indexe helfen sollen, also schnell ein Index gebaut:
test=# create index idx_words on words(t);
CREATE INDEX
Time: 1272.859 ms
Hat über 1 Sekunde gedauert, aber egal, nun ist doch hoffentlich meine Abfrage vieeeel schneller, oder?
Hinweis: nach der Erstellung eines Indexes ist ANALYSE für diese Tabelle aufzurufen, dies habe ich im Copy&Paste vergessen zu zeigen.
test=*# select t from words where t ~ '^[est].*' order by t;
Time: 504.452 ms
test=*#
Huch? Nix gewonnen…
Ich schaue nun also in’s EXPLAIN. Ich nutze gleich EXPLAIN ANALYSE, da dieses die Abfrage auch real durchführt:
test=*# explain analyse select t from words where t ~ '^[est].*' order by t;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
Sort (cost=2764.59..2801.92 rows=14935 width=9) (actual time=507.258..532.631 rows=16923 loops=1)
Sort Key: t
Sort Method: external sort Disk: 328kB
-> Seq Scan on words (cost=0.00..1729.11 rows=14935 width=9) (actual time=53.064..163.195 rows=16923 loops=1)
Filter: (t ~ '^[est].*'::text)
Total runtime: 554.164 ms
(6 rows)
Time: 555.131 ms
Ich sehe folgende Dinge:
- es wird ein seq. Scan durchgeführt, mein Index also nicht genutzt
- es wird ON DISK sortiert, was auch lange dauert
- die erwarteten 14935 Rows liegen realtiv nah an den gelieferten 16923 Rows (Werte nach actual)
Zuerst einmal also die Frage, warum der Index nicht genutzt wird. Die Antwort ist trivial: er ist falsch erstellt. Die DB ist in UTF8, ein Zeichen kann durch mehr als ein Byte dargestellt werden. Um in Textspalten, die NICHT rein ASCII sind, suchen zu können, müssen wir den Index mit dem Zusatz “text_pattern_ops” erstellen ( PostgreSQL: Documentation: Manuals: PostgreSQL 8.4: Operator Classes and Operator Families )
Okay:
test=*# create index idx_words on words(t text_pattern_ops);
CREATE INDEX
Time: 441.020 ms
test=*# explain analyse select t from words where t ~ '^[est].*' order by t;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
Sort (cost=2764.59..2801.92 rows=14935 width=9) (actual time=512.989..540.515 rows=16923 loops=1)
Sort Key: t
Sort Method: external sort Disk: 328kB
-> Seq Scan on words (cost=0.00..1729.11 rows=14935 width=9) (actual time=59.861..173.590 rows=16923 loops=1)
Filter: (t ~ '^[est].*'::text)
Total runtime: 562.538 ms
(6 rows)
Time: 563.866 ms
Hrm. Immer noch nix gewonnen. Ich suche nach Wörtern, die mit ‘e’, ‘s’ oder ‘t’ beginnen, ich schreibe meine Abfrage darauf um:
test=*# explain analyse select t from words where t ~ '^e.*' or t ~ '^s.*' or t ~ '^t.*' order by t;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=1708.23..1743.88 rows=14261 width=9) (actual time=412.822..439.545 rows=16923 loops=1)
Sort Key: t
Sort Method: external sort Disk: 328kB
-> Bitmap Heap Scan on words (cost=138.32..724.23 rows=14261 width=9) (actual time=7.494..88.795 rows=16923 loops=1)
Recheck Cond: ((t ~ '^e.*'::text) OR (t ~ '^s.*'::text) OR (t ~ '^t.*'::text))
Filter: ((t ~ '^e.*'::text) OR (t ~ '^s.*'::text) OR (t ~ '^t.*'::text))
-> BitmapOr (cost=138.32..138.32 rows=5081 width=0) (actual time=7.393..7.393 rows=0 loops=1)
-> Bitmap Index Scan on idx_words (cost=0.00..75.18 rows=3092 width=0) (actual time=1.399..1.399 rows=3205 loops=1)
Index Cond: ((t ~>=~ 'e'::text) AND (t ~<~ 'f'::text))
-> Bitmap Index Scan on idx_words (cost=0.00..4.27 rows=1 width=0) (actual time=4.196..4.196 rows=9567 loops=1)
Index Cond: ((t ~>=~ 's'::text) AND (t ~<~ 't'::text))
-> Bitmap Index Scan on idx_words (cost=0.00..48.16 rows=1990 width=0) (actual time=1.792..1.792 rows=4151 loops=1)
Index Cond: ((t ~>=~ 't'::text) AND (t ~<~ 'u'::text))
Total runtime: 461.047 ms
(14 rows)
Time: 462.797 ms
Besser. Es wird nun der Index benutzt. Aber immer noch auf Platte sortiert. Im RAM wäre schneller, RAM … work_mem - Parameter. Ich schaue, wie dieser gesetzt ist, und erhöhe mal:
test=*# show work_mem;
work_mem
----------
1MB
(1 row)
Time: 0.488 ms
test=*# set work_mem='4MB';
SET
Time: 0.339 ms
test=*# explain analyse select t from words where t ~ '^e.*' or t ~ '^s.*' or t ~ '^t.*' order by t;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=1708.23..1743.88 rows=14261 width=9) (actual time=335.000..357.697 rows=16923 loops=1)
Sort Key: t
Sort Method: quicksort Memory: 1565kB
-> Bitmap Heap Scan on words (cost=138.32..724.23 rows=14261 width=9) (actual time=8.067..90.066 rows=16923 loops=1)
Recheck Cond: ((t ~ '^e.*'::text) OR (t ~ '^s.*'::text) OR (t ~ '^t.*'::text))
Filter: ((t ~ '^e.*'::text) OR (t ~ '^s.*'::text) OR (t ~ '^t.*'::text))
-> BitmapOr (cost=138.32..138.32 rows=5081 width=0) (actual time=7.982..7.982 rows=0 loops=1)
-> Bitmap Index Scan on idx_words (cost=0.00..75.18 rows=3092 width=0) (actual time=1.490..1.490 rows=3205 loops=1)
Index Cond: ((t ~>=~ 'e'::text) AND (t ~<~ 'f'::text))
-> Bitmap Index Scan on idx_words (cost=0.00..4.27 rows=1 width=0) (actual time=4.391..4.391 rows=9567 loops=1)
Index Cond: ((t ~>=~ 's'::text) AND (t ~<~ 't'::text))
-> Bitmap Index Scan on idx_words (cost=0.00..48.16 rows=1990 width=0) (actual time=2.093..2.093 rows=4151 loops=1)
Index Cond: ((t ~>=~ 't'::text) AND (t ~<~ 'u'::text))
Total runtime: 381.111 ms
(14 rows)
Time: 382.865 ms
Ich hoffe, damit kurz demonstriert zu haben, wie man mit Hilfe von EXPLAIN (ANALYSE) untersuchen kann, wie Abfragen intern ausgeführt werden. Zusätzlich habe ich gezeigt, daß Indexe nicht automatisch genutzt und von Vorteil sind, sie müssen auch passend angelegt werden und die Abfrage muß auch so gestellt werden, daß sie nutzbar sind. Der Planner ist an sich recht smart, aber es kann durchaus sinnvoll sein, eine Abfrage einfach umzustellen, um diese zu optimieren. Nicht zu vergessen ist die Optimierung der Speicherparameter wie z.B. work_mem. Einen sehr guten Workshop zu diesem Thema gibt es von Andreas ‘ads’ Scherbaum, die Unterlagen sind hier: Writings Andreas ‘ads’ Scherbaum
Der Erfolg im obigen Beispiel war nun insgesamt nicht so überwältigend, aber es gab schon komplexere Abfragen hier im Forum und durch Maßnahmen wie eben konnten die Abfragezeiten um Fakter 10 … 100 verbessert werden - es lohnt sich also.
Andreas