Einen PostgreSQL-Abfrageplan mit EXPLAIN ANALYZE lesen

Ziel

Herausfinden, weshalb eine Abfrage langsam ist, indem gelesen wird, was die Datenbank tatsächlich getan hat, statt zu raten, welcher Index fehlt.

Voraussetzungen

Eine langsame Abfrage mit realistischen Parametern, ausgeführt gegen Daten realistischer Grösse und Verteilung; aktuelle Statistiken (ANALYZE).

Schritte

1. EXPLAIN (ANALYZE, BUFFERS) <query> ausführen; bei einer Abfrage mit Nebeneffekten in eine Transaktion einbetten und zurückrollen.
2. Den Baum von den innersten Knoten nach aussen lesen; jeder Knoten zeigt geschätzte Kosten und Zeilen sowie tatsächliche Zeit und Zeilen, mit Durchläufen (loops).
3. Geschätzte mit tatsächlichen Zeilenzahlen vergleichen. Eine grosse Abweichung (10-fach oder mehr) bedeutet, dass der Planer aufgrund falscher Annahmen entschieden hat: veraltete Statistiken, korrelierte Spalten oder Funktionen, die er nicht schätzen kann.
4. Den Knoten mit der grössten tatsächlichen Zeit finden, die nicht bloss die Summe seiner Kindknoten ist; dort geschieht die eigentliche Arbeit.
5. Scan-Typen betrachten: Ein sequenzieller Scan über eine grosse, auf wenige Zeilen gefilterte Tabelle deutet auf einen fehlenden oder unbrauchbaren Index hin; ein Indexscan mit hoher Durchlaufzahl innerhalb eines Nested Loop deutet auf ein Problem mit der Join-Reihenfolge oder der Schätzung hin.
6. Buffers prüfen: Ein hoher Wert bei shared read zeigt Daten an, die von der Platte kommen; temp-Blöcke zeigen Sortierungen oder Hashes, die auf die Platte auslagern (work_mem).
7. Eine Sache ändern (Index, umgeschriebenes Prädikat, Statistik-Ziel), erneut ausführen, vergleichen.

Erwartetes Ergebnis

Eine konkrete Ursache für die Langsamkeit und eine verifizierte Änderung, statt eines aufs Geratewohl hinzugefügten Index.

Grenzen und Prüfbasis

Pläne unterscheiden sich zwischen Umgebungen mit unterschiedlichen Datengrössen; ein Plan aus einer kleinen Entwicklungsdatenbank beweist wenig. ANALYZE in EXPLAIN führt die Abfrage aus, deshalb bei destruktiven Anweisungen ausserhalb einer zurückgerollten Transaktion vermeiden.