pg_stat_plans

pg_stat_plans : Track per-plan call counts, execution times, and example EXPLAIN texts.

Overview

Packages

Source

Install

Make sure PGDG repo available:

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

Install this extension with pig:

pig install pg_stat_plans;		# install via package name, for the active PG version

pig install pg_stat_plans -v 18;   # install for PG 18
pig install pg_stat_plans -v 17;   # install for PG 17
pig install pg_stat_plans -v 16;   # install for PG 16

Config this extension to shared_preload_libraries:

shared_preload_libraries = '$libdir/pg_stat_plans';

Create this extension with:

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.