Documentationpg_stat_insights Documentation

Views Reference

At a Glance

All views are created alongside the extension and require only SELECT privileges. Most views aggregate the primary statistics to reduce joins during investigations.

`pg_stat_insights`

Primary view containing all 52 metrics. Use for comprehensive analysis.

Sample query

SELECT queryid,
       calls,
       total_exec_time,
       mean_exec_time,
       shared_blks_hit,
       shared_blks_read
  FROM pg_stat_insights
 ORDER BY total_exec_time DESC
 LIMIT 20;

`pg_stat_insights_plan`

Augments statements with plan hash, plan time, and planning counters (requires track_planning).

Sample query

SELECT queryid,
       planid,
       total_plan_time,
       mean_plan_time,
       plans
  FROM pg_stat_insights_plan
 ORDER BY total_plan_time DESC
 LIMIT 15;

`pg_stat_insights_io`

Block-level IO timing and counts, including temp usage and write amplification.

Sample query

SELECT queryid,
       blk_read_time,
       blk_write_time,
       temp_blks_read,
       temp_blks_written
  FROM pg_stat_insights_io
 ORDER BY blk_read_time DESC
 LIMIT 15;

`pg_stat_insights_cache`

Derived cache hit ratio and buffer churn metrics to quickly spot thrashing statements.

Sample query

SELECT queryid,
       cache_hit_ratio,
       shared_blks_read,
       shared_blks_hit
  FROM pg_stat_insights_cache
 WHERE cache_hit_ratio < 0.9
 ORDER BY shared_blks_read DESC
 LIMIT 15;

`pg_stat_insights_waits`

Aggregates execution time by wait event type for root cause analysis.

Sample query

SELECT wait_event_type,
       wait_event,
       total_exec_time,
       calls
  FROM pg_stat_insights_waits
 ORDER BY total_exec_time DESC
 LIMIT 20;

`pg_stat_insights_relation`

Relation-level statistics including shared/temp blocks and WAL usage by table/index.

Sample query

SELECT relid::regclass AS relation,
       shared_blks_read,
       shared_blks_hit,
       wal_bytes
  FROM pg_stat_insights_relation
 ORDER BY shared_blks_read DESC
 LIMIT 15;

`pg_stat_insights_db`

Database-wide aggregates, summarising metrics at database granularity.

Sample query

SELECT datname,
       sum(total_exec_time) AS total_exec_ms,
       sum(calls) AS calls
  FROM pg_stat_insights_db
 GROUP BY datname
 ORDER BY total_exec_ms DESC;

`pg_stat_insights_user`

Aggregates metrics per role to understand workload by user.

Sample query

SELECT rolname,
       calls,
       total_exec_time
  FROM pg_stat_insights_user
 ORDER BY total_exec_time DESC
 LIMIT 10;

`pg_stat_insights_plan_stats`

Plan hash frequency and last execution timestamps for change detection.

Sample query

SELECT planid,
       plan_runs,
       last_run
  FROM pg_stat_insights_plan_stats
 ORDER BY last_run DESC
 LIMIT 20;

`pg_stat_insights_jit`

JIT compilation metrics capturing LLVM overhead.

Sample query

SELECT queryid,
       jit_functions,
       jit_generation_time,
       jit_emission_time
  FROM pg_stat_insights_jit
 ORDER BY jit_generation_time DESC
 LIMIT 15;

`pg_stat_insights_reset_history`

Audit trail of statistic resets to correlate metric drops with operational events.

Sample query

SELECT reset_by,
       reset_at,
       reason
  FROM pg_stat_insights_reset_history
 ORDER BY reset_at DESC
 LIMIT 20;

PreviousMetrics Reference Guide

NextUsage Examples