Jeder Softwareentwickler, der schon einmal mit PostgreSQL gearbeitet hat, sollte die Befehle Explain bzw. Explain Analyze kennen.
Explain zeigt den Queryplan, den Postgres zum Ausführen einer Query benutzt. So kann man direkt sehen, wie viel Rückgabezeilen erwartet werden, welche "Kosten" die Query
verursacht und vor allem, welche Indizes verwendet werden.
Zum Test erstellen wir einen Index:
CREATE INDEX users_gerade_user_id ON users USING btree (user_id) WHERE (user_id % 2) = 0;
Nun schreiben wir eine Query die diesen Index benutzt:
EXPLAIN ANALYZE user_id FROM users WHERE user_id % 2 = 0
Der Queryplan sieht nun so aus:
"Bitmap Heap Scan on users (cost=8191.10..23029.67 rows=6215 width=4) (actual time=336.873..5263.160 rows=621287 loops=1)" " Recheck Cond: ((user_id % 2) = 0)" " -> Bitmap Index Scan on users_gerade_user_id (cost=0.00..8189.55 rows=6215 width=0) (actual time=278.139..278.139 rows=621380 loops=1)" "Total runtime: 5366.097 ms"
Hier ist gut zu sehen, dass der entsprechende Index auch benutzt wird:
-> Bitmap Index Scan on users_gerade_user_id (cost=0.00..8189.55 rows=6215 width=0) (actual time=278.139..278.139 rows=621380 loops=1)
Allein über die Ausgabe und Interpretation von Explain Analyze kann man ganze Bücher schreiben, aber das ist auch gar nicht das, worauf ich hinaus will.
Viele Leute nutzen Libraries wie ADODB, welche mit Prepared Statements arbeiten.
Schauen wir uns doch einmal den Queryplan an, wenn man diese Query als prepared Statement ausführt:
PREPARE test(integer) AS SELECT user_id FROM users WHERE user_id % 2 = $1; EXPLAIN ANALYZE EXECUTE test(0);
"Seq Scan on users (cost=0.00..101341.73 rows=6217 width=4) (actual time=0.013..2074.244 rows=621290 loops=1)" " Filter: ((user_id % 2) = $1)" "Total runtime: 2202.066 ms"
Wie wir sehen, wird hier der vorhin angelegt Index nicht benutzt. Warum?
Die Erklärung dazu liefert uns die Postgres Dokumentation, dort steht naemlich folgendes:
When the PREPARE statement is executed, the specified statement is parsed, rewritten, and planned. When an EXECUTE command is subsequently issued, the prepared statement need only be executed. Thus, the parsing, rewriting, and planning stages are only performed once, instead of every time the statement is executed.
Das steht's, sämtliche "Vorarbeiten" wie das Parsen, Umschreiben und Planen der Query passieren in dem Moment, wo das Statement per "PREPARE" angelegt wird.
Beim "EXECUTE" wird es lediglich ausgeführt.
D.h. der Queryplaner kennt zum Zeitpunkt der Planung den übergebenen Parameter gar nicht. Er weiß nicht, ob dort user_id % 2 = 0 oder vll. user_id % 2 = 1 steht, kann also folglich den Index gar nicht benutzen!
Erst ab Postgres 9.2 ändert sich dieses Verhalten, die Doku sagt dazu folgendes:
When the PREPARE statement is executed, the specified statement is parsed, analyzed, and rewritten. When an EXECUTE command is subsequently issued, the prepared statement is planned and executed. This division of labor avoids repetitive parse analysis work, while allowing the execution plan to depend on the specific parameter values supplied.
Seit Postgres 9.2 wird also beim "PREPARE" Die Query geparsed, analysiert und ggf. umgeschrieben. Geplant wird sie aber erst beim Execute!
Vielleicht ging es ja dem einen oder anderen auch schon so, dass er auf der Konsole eine Query vermeintlich optimiert hat, sie dann aber im Produktivsystem trotzdem noch sehr langsam war, oder Indizes einfach nicht benutzt hat.
Falls ja, hat er nun hier die Erklärung :-D