Encontrar las consultas que generan I/O de disco, archivos temporales y 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;

Qué hace

Muestra las consultas que más bloques leen desde fuera de shared buffers, más escriben en archivos temporales (sorts/hashes que no caben en work_mem) y más WAL generan, junto con su cache hit ratio.

Cuándo usarlo

Para encontrar la consulta responsable cuando sube la latencia de lectura de disco, saltan alertas de archivos temporales (log_temp_files) o el volumen de WAL crece de repente.

Precaución

shared_blks_read también cuenta las lecturas servidas desde la page cache del SO; el tiempo real de disco requiere track_io_timing = on (mida antes su sobrecarga con pg_test_timing). Si temp_written es alto, la solución suele ser subir work_mem por rol/sesión o corregir la consulta/índice; subir work_mem globalmente aumenta el riesgo de memoria.