I/O Performance Analysis

Enable Timing Instrumentation

Collect detailed block timing by enabling track_io_timing = on and restarting PostgreSQL. Plan this change during a maintenance window because it increases LWLock sampling cost slightly.

track_io_timing = on
pg_stat_insights.track = 'all'
pg_stat_insights.track_planning = true

Identify Queries with High I/O Wait

Rank queries by cumulative I/O time to find candidates for indexing, caching, or query rewrites.

SELECT queryid,
       query,
       calls,
       blk_read_time,
       blk_write_time,
       (blk_read_time + blk_write_time) / calls AS avg_io_ms
  FROM pg_stat_insights_io
 ORDER BY blk_read_time + blk_write_time DESC
 LIMIT 20;

Inspect Hot Relations and Temp Usage

Combine relation statistics with temp block tracking to discover tables or sorts causing spillover to disk.

SELECT relid::regclass AS relation,
       shared_blks_read,
       shared_blks_dirtied,
       temp_blks_read,
       temp_blks_written
  FROM pg_stat_insights_relation
 ORDER BY shared_blks_read DESC
 LIMIT 15;

SELECT queryid,
       temp_blks_read,
       temp_blks_written,
       calls
  FROM pg_stat_insights
 WHERE temp_blks_written > 0
 ORDER BY temp_blks_written DESC
 LIMIT 15;

Correlate Wait Events

Use the waits view to break down latency by wait event type and confirm whether storage or locking limits throughput.

SELECT wait_event_type,
       wait_event,
       SUM(total_exec_time) AS total_time_ms,
       SUM(calls) AS calls
  FROM pg_stat_insights_waits
 GROUP BY wait_event_type, wait_event
 ORDER BY total_time_ms DESC
 LIMIT 15;

Remediation Checklist