Find queries driving disk I/O, temp files and WAL (pg_stat_statements)

Command

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;

What it does

Shows the queries that read the most blocks from outside shared buffers, write the most to temp files (sorts/hashes that do not fit in work_mem) and generate the most WAL, together with their cache hit ratio.

When to use it

To find the responsible query when disk read latency rises, temp file alerts fire (log_temp_files) or WAL volume suddenly grows.

Caution

shared_blks_read also counts reads served from the OS page cache; real disk time requires track_io_timing = on (measure its overhead with pg_test_timing first). If temp_written is high, the fix is usually raising work_mem per role/session or fixing the query/index; raising work_mem globally increases memory risk.