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:
Ich suche nun alle Wörter, die mit 'e', 's' oder 't' beginnen:Code:test=*# \d words; Table "public.words" Column | Type | Modifiers --------+------+----------- t | text | test=*# select count(*) from words; count ------- 98569 (1 row)
Eine halbe Sekunde. Ist mir zu lang ...Code:test=*# select t from words where t ~ '^[est].*' order by t; Time: 503.298 ms test=*#
Okay, ich erinnere mich dunkel, daß Indexe helfen sollen, also schnell ein Index gebaut:
Hat über 1 Sekunde gedauert, aber egal, nun ist doch hoffentlich meine Abfrage vieeeel schneller, oder?Code:test=# create index idx_words on words(t); CREATE INDEX Time: 1272.859 ms
Hinweis: nach der Erstellung eines Indexes ist ANALYSE für diese Tabelle aufzurufen, dies habe ich im Copy&Paste vergessen zu zeigen.
Huch? Nix gewonnen...Code:test=*# select t from words where t ~ '^[est].*' order by t; Time: 504.452 ms test=*#
Ich schaue nun also in's EXPLAIN. Ich nutze gleich EXPLAIN ANALYSE, da dieses die Abfrage auch real durchführt:
Ich sehe folgende Dinge:Code: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
- 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:
Hrm. Immer noch nix gewonnen. Ich suche nach Wörtern, die mit 'e', 's' oder 't' beginnen, ich schreibe meine Abfrage darauf um:Code: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
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:Code: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
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' ScherbaumCode: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
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
Geändert von akretschmer (26.03.2010 um 09:50 Uhr)
--
https://www.xing.com/profile/Andreas_Kretschmer7ads Das hatte dir akretschmer gestern schon gesagt und siehe da sobald du das machst, funktioniert es ;-)
Links: Tobias Bauer - IT- und Funkdienstleistungen - Bilderrahmen-kaufen.de - Der Onlineshop für Bilderrahmen & Fotorahmen - Funkforum
Lesezeichen