ディスク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;

概要

共有バッファ外から最も多くのブロックを読み取るクエリ、一時ファイルに最も多く書き込むクエリ(work_memに収まらないソート/ハッシュ)、最も多くのWALを生成するクエリを、キャッシュヒット率とともに表示する。

使用する場面

ディスク読み取りレイテンシが上昇したとき、一時ファイルのアラート(log_temp_files)が発生したとき、WAL量が急増したときに原因となるクエリを特定するため。

注意

shared_blks_readはOSのページキャッシュから提供された読み取りも数える。実際のディスク時間にはtrack_io_timing = onが必要(先にpg_test_timingでオーバーヘッドを測定する)。temp_writtenが高い場合、通常の対策はロール/セッション単位でのwork_memの引上げ、またはクエリ/インデックスの修正。work_memをグローバルに引き上げるとメモリのリスクが増す。