pg_stat_plans

Overview

Version

Install

You can install pg_stat_plans directly. First, make sure the PGDG repository is added and enabled:

pig repo add pgdg -u          # Add PGDG repo and update cache

Install the extension using pig or apt/yum/dnf:

• Install
• pig
• dnf
• apt

pig install pg_stat_plans;          # Install for current active PG version

pig ext install -y pg_stat_plans -v 18  # PG 18
pig ext install -y pg_stat_plans -v 17  # PG 17
pig ext install -y pg_stat_plans -v 16  # PG 16

dnf install -y pg_stat_plans_18       # PG 18
dnf install -y pg_stat_plans_17       # PG 17
dnf install -y pg_stat_plans_16       # PG 16

apt install -y postgresql-18-pg-stat-plans   # PG 18
apt install -y postgresql-17-pg-stat-plans   # PG 17
apt install -y postgresql-16-pg-stat-plans   # PG 16

Preload:

shared_preload_libraries = '$libdir/pg_stat_plans';

Create Extension:

CREATE EXTENSION pg_stat_plans;

Usage

Sources: README, v2.1.0 release, SQL objects

pg_stat_plans tracks aggregate statistics for PostgreSQL plan shapes. It hashes planned query trees into plan IDs, stores example EXPLAIN text in shared memory, and helps identify when the same query ID is executed with different plans.

Enable

pg_stat_plans requires PostgreSQL 16 or newer and must be loaded at server start:

shared_preload_libraries = 'pg_stat_plans'
pg_stat_plans.compress = 'zstd'

CREATE EXTENSION pg_stat_plans;

Using pg_stat_statements alongside it is recommended so plan IDs can be correlated with query text.

Query Plans

SELECT *
FROM pg_stat_plans;

The view exposes userid, dbid, toplevel, queryid, planid, calls, total_exec_time, and plan. To omit stored plan text for lighter queries:

SELECT *
FROM pg_stat_plans(false);

Group by queryid to see multiple plan shapes chosen for one normalized query:

SELECT queryid, planid, calls, total_exec_time / NULLIF(calls, 0) AS avg_exec_time
FROM pg_stat_plans(false)
ORDER BY queryid, avg_exec_time DESC;

Running Queries

On PostgreSQL 18 and newer, pg_stat_plans_activity can show plan IDs and example plans for currently running queries:

SELECT *
FROM pg_stat_plans_activity;

Reset And Configure

SELECT pg_stat_plans_reset();

Important settings include pg_stat_plans.max, pg_stat_plans.max_size, pg_stat_plans.max_plan_memory, pg_stat_plans.track, pg_stat_plans.compress, and pg_stat_plans.plan_advice.

Notes

Statistics use PostgreSQL’s cumulative statistics system, so counters are flushed at transaction end and may be delayed. Plan IDs describe plan shape and can change when partitions, casts, or expression details change.