Найти запросы, создающие дисковый I/O, временные файлы и WAL (pg_stat_statements)

Команда

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;

Что делает

Показывает запросы, которые читают больше всего блоков вне shared buffers, больше всего пишут во временные файлы (сортировки/хэши, не помещающиеся в work_mem) и генерируют больше всего WAL, вместе с их долей попаданий в кэш.

Когда использовать

Чтобы найти ответственный запрос, когда растёт задержка чтения с диска, срабатывают алерты о временных файлах (log_temp_files) или внезапно растёт объём WAL.

Внимание

shared_blks_read учитывает и чтения, обслуженные из страничного кэша ОС; реальное время диска требует track_io_timing = on (сначала измерьте его накладные расходы через pg_test_timing). Если temp_written высокий, решение обычно — увеличить work_mem для роли/сессии или исправить запрос/индекс; глобальное увеличение work_mem повышает риск нехватки памяти.