Trouver les requêtes à l'origine des E/S disque, fichiers temporaires et WAL (pg_stat_statements)

Commande

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;

Ce que fait la commande

Affiche les requêtes qui lisent le plus de blocs hors des shared buffers, écrivent le plus dans des fichiers temporaires (tris/hachages ne tenant pas dans work_mem) et génèrent le plus de WAL, avec leur taux de succès cache.

Quand l'utiliser

Pour trouver la requête responsable lorsque la latence de lecture disque augmente, que des alertes de fichiers temporaires se déclenchent (log_temp_files) ou que le volume de WAL augmente brusquement.

Attention

shared_blks_read compte aussi les lectures servies par le page cache de l'OS ; le temps disque réel exige track_io_timing = on (mesurez d'abord sa surcharge avec pg_test_timing). Si temp_written est élevé, la correction consiste généralement à relever work_mem par rôle/session ou à corriger la requête/l'index ; relever work_mem globalement augmente le risque mémoire.