Die teuersten Statements mit pg_stat_statements finden

Ziel

Die von einem Server ausgeführten Statements nach ihren Gesamtkosten ordnen, sodass Tuning-Aufwand in das fliesst, was die Last dominiert, statt in die Abfrage, die zufällig aufgefallen ist.

Voraussetzungen

Das Modul in shared_preload_libraries (die Dokumentation besagt, dass zum Hinzufügen oder Entfernen ein Neustart nötig ist), compute_query_id auf auto oder on, sowie CREATE EXTENSION pg_stat_statements in der Datenbank, aus der die View gelesen wird. Um den Abfragetext anderer Nutzender zu sehen, ist die Rolle pg_read_all_stats oder Superuser nötig.

Schritte

1. Das Zeitfenster feststellen: SELECT stats_reset FROM pg_stat_statements_info. Zähler sind kumulativ seit diesem Zeitpunkt; ist das Fenster unbekannt oder überspannt es ein Deployment, SELECT pg_stat_statements_reset() ausführen und eine repräsentative Zeitspanne abwarten.
2. Nach Gesamtzeit sortieren: SELECT queryid, calls, total_exec_time, mean_exec_time, rows, query FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 20. Ein schnelles, millionenfach aufgerufenes Statement liegt oft vor dem langsamen Bericht, über den sich alle beschweren.
3. Separat nach calls sortieren. Sehr hohe Anzahlen mit einer Zeile je Aufruf deuten auf zeilenweise Schleifen im Anwendungscode hin, nicht auf die Datenbank.
4. mean_exec_time mit max_exec_time vergleichen. Eine grosse Lücke deutet auf parameterabhängige Pläne oder Lock-Wartezeiten hin, nicht auf ein gleichmässig langsames Statement.
5. Die Block-Spalten lesen: shared_blks_read gegen shared_blks_hit trennt I/O-gebundene Statements von zwischengespeicherten; temp_blks_written zeigt Sortierungen und Hashes, die auf Platte auslagern.
6. Den Text des Spitzenstatements nehmen, die Platzhalter $n durch realistische Werte ersetzen und mit EXPLAIN (ANALYZE, BUFFERS) fortfahren.
7. Nach einer Änderung zurücksetzen, dieselbe Zeitspanne abwarten und dieselben Zeilen anhand der queryid vergleichen, nicht anhand des Abfragetexts.

Erwartetes Ergebnis

Eine kurze Liste von Statements, die den grössten Teil der Ausführungszeit ausmachen, jeweils mit angegebenem Grund (Aufrufanzahl, Kosten je Aufruf oder I/O), sowie ein Vorher-Nachher-Vergleich je queryid für jede vorgenommene Änderung.

Grenzen und Prüfbasis

Die Dokumentation besagt, dass die View höchstens pg_stat_statements.max Einträge (Standard 5000) behält und darüber hinaus die am seltensten ausgeführten verwirft, sodass seltene Statements fehlen können; dass queryid weder über Hauptversionen hinweg noch zwischen logisch replizierten Servern stabil ist; und dass nur erfolgreiche Ausführungen die Ausführungszähler aktualisieren. Konstanten werden normalisiert, und die Dokumentation besagt, dass Abfragen, die sich nur in der Anzahl der Elemente einer Konstantenliste unterscheiden, zu einem Eintrag zusammengefasst werden, dargestellt als IN ($1 /*, ... */). Das Vorgehen liefert relative Rangfolgen; es werden keine Zeitwerte behauptet.