Documentationpg_stat_insights Documentation

Slow Query Analysis

Rank Slow Queries

Start with average execution time to locate persistent bottlenecks. Filter by call count to ignore one-off statements.

Top 10 slowest queries

SELECT queryid,
       LEFT(query, 120) AS query_preview,
       calls,
       mean_exec_time AS avg_ms,
       total_exec_time AS total_ms,
       stddev_exec_time,
       rows
  FROM pg_stat_insights
 WHERE calls > 10
 ORDER BY mean_exec_time DESC
 LIMIT 10;

Measure Variability

High standard deviation indicates intermittent slowness due to locking, bloated tables, or cache misses. Prioritise queries with high coefficient of variation.

Execution time variance

SELECT queryid,
       stddev_exec_time,
       mean_exec_time,
       max_exec_time,
       min_exec_time,
       (stddev_exec_time / NULLIF(mean_exec_time, 0)) AS coeff_variation
  FROM pg_stat_insights
 WHERE stddev_exec_time > 0
 ORDER BY coeff_variation DESC
 LIMIT 15;

Drill into Execution Plans

Use pg_stat_insights_plan to fetch the captured plan summary, then re-run the statement with EXPLAIN (ANALYZE, BUFFERS, WAL) for current statistics.

Plan investigation

-- Replace :queryid with target ID
SELECT *
  FROM pg_stat_insights_plan
 WHERE queryid = :queryid;

-- Capture execution plan
dO $$
DECLARE
  sql TEXT;
BEGIN
  SELECT query INTO sql
    FROM pg_stat_insights
   WHERE queryid = :queryid;
  EXECUTE format('EXPLAIN (ANALYZE, BUFFERS, WAL) %s', sql);
END;
$$;

Remediation Checklist

Index & schema fixes

Create missing indexes surfaced by EXPLAIN or pg_stat_user_indexes.
VACUUM and ANALYZE bloated tables; stale statistics often cause bad plans.
Consider partitioning large tables to minimise scanned data.

Query optimisation

Rewrite OR chains into UNION ALL or use partial indexes.
Push predicates closer to data sources to avoid wide joins.
Reduce client round-trips by batching repeated lookups.

Automate Regression Detection

Store baselines

CREATE TABLE IF NOT EXISTS slow_query_baselines AS
SELECT queryid,
       mean_exec_time,
       total_exec_time,
       calls,
       now() AS captured_at
  FROM pg_stat_insights
 WHERE mean_exec_time > 100;

Alert on regressions

#!/usr/bin/env bash
REGRESS=$(psql -t -c "SELECT queryid FROM pg_stat_insights s JOIN slow_query_baselines b USING (queryid) WHERE s.mean_exec_time > b.mean_exec_time * 1.5;")
if [[ -n "$REGRESS" ]]; then
  echo "Slow query regression detected: $REGRESS" | mail -s "pg_stat_insights regression" dba@example.com
fi

PreviousParallel Query Analysis

NextWAL Activity Monitoring