Usage Examples
Common Query Patterns
Use the snippets below as building blocks for dashboards and ad-hoc investigations. Adjust filters to your workload and retain query IDs for repeat analysis.
Find Slow Queries
Identify queries with high execution time.
SQL
SELECT LEFT(query, 100) AS query_snippet,
calls,
mean_exec_time,
total_exec_time,
stddev_exec_time
FROM pg_stat_insights
ORDER BY mean_exec_time DESC
LIMIT 20;Cache Hit Ratio Analysis
Focus on statements thrashing shared buffers.
SQL
SELECT LEFT(query, 100) AS query_snippet,
(shared_blks_hit::numeric /
NULLIF(shared_blks_hit + shared_blks_read, 0)) AS cache_hit_ratio,
shared_blks_hit,
shared_blks_read
FROM pg_stat_insights
WHERE shared_blks_read > 100
ORDER BY cache_hit_ratio ASC
LIMIT 20;Top Resource Consumers
Aggregate execution time, IO, and WAL generated per query.
SQL
SELECT LEFT(query, 100) AS query_snippet,
calls,
total_exec_time,
mean_exec_time,
(shared_blks_hit + shared_blks_read) AS total_io,
wal_bytes
FROM pg_stat_insights
ORDER BY total_exec_time DESC
LIMIT 20;JIT Compilation Impact
Compare JIT overhead with net execution time.
SQL
SELECT queryid,
mean_exec_time,
(jit_generation_time + jit_inlining_time + jit_optimization_time + jit_emission_time) / calls AS avg_jit_ms
FROM pg_stat_insights
WHERE jit_functions > 0
ORDER BY avg_jit_ms DESC
LIMIT 15;Reset Statistics Safely
Reset counters after collecting baselines to avoid losing trend data unexpectedly.
Reset command
SELECT pg_stat_insights_reset();Next Steps
Continue refining observability by tuning configuration parameters, wiring dashboards, and consulting the troubleshooting playbook when metrics look off.
Configuration Reference
Adjust retention, planning metrics, and sampling to match production workloads.
Monitoring & Alerts
Export metrics to Prometheus/Grafana and build alert rules for regressions.
Troubleshooting Playbook
Resolve preload errors, missing metrics, and overhead concerns with step-by-step fixes.