Abfragen finden, die Platten-I/O, Temp-Dateien und WAL verursachen (pg_stat_statements)

Befehl

SELECT queryid,
       calls,
       shared_blks_hit,
       shared_blks_read,
       round(100.0 * shared_blks_hit / NULLIF(shared_blks_hit + shared_blks_read, 0), 1) AS hit_pct,
       pg_size_pretty(temp_blks_written * current_setting('block_size')::bigint) AS temp_written,
       pg_size_pretty(wal_bytes) AS wal,
       left(regexp_replace(query, '\s+', ' ', 'g'), 120) AS query
FROM pg_stat_statements
ORDER BY shared_blks_read + temp_blks_read + temp_blks_written DESC
LIMIT 20;

Was es tut

Zeigt die Abfragen, die die meisten Blöcke außerhalb der Shared Buffers lesen, am meisten in Temp-Dateien schreiben (Sortierungen/Hashes, die nicht in work_mem passen) und das meiste WAL erzeugen, zusammen mit ihrer Cache-Trefferquote.

Wann einsetzen

Um die verantwortliche Abfrage zu finden, wenn die Leselatenz der Platte steigt, Temp-File-Alarme ausgelöst werden (log_temp_files) oder das WAL-Volumen plötzlich wächst.

Vorsicht

shared_blks_read zählt auch Lesevorgänge, die aus dem Page Cache des OS bedient werden; echte Plattenzeit erfordert track_io_timing = on (messe vorher den Overhead mit pg_test_timing). Ist temp_written hoch, besteht die Lösung meist darin, work_mem je Rolle/Session zu erhöhen oder Abfrage/Index zu korrigieren; work_mem global zu erhöhen steigert das Speicherrisiko.