Encontrar as consultas que geram I/O de disco, arquivos temporários e WAL (pg_stat_statements)

Comando

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;

O que faz

Mostra as consultas que mais leem blocos fora dos shared buffers, mais gravam em arquivos temporários (sorts/hashes que não cabem no work_mem) e mais geram WAL, junto com sua taxa de acerto de cache.

Quando usar

Para encontrar a consulta responsável quando a latência de leitura de disco sobe, disparam alertas de arquivos temporários (log_temp_files) ou o volume de WAL cresce de repente.

Atenção

shared_blks_read também conta leituras atendidas pelo page cache do SO; o tempo real de disco exige track_io_timing = on (meça antes o overhead com pg_test_timing). Se temp_written estiver alto, a correção costuma ser aumentar o work_mem por role/sessão ou corrigir a consulta/índice; aumentar o work_mem globalmente aumenta o risco de memória.