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:

• 2.1.1 README
• 2.1.1 changelog
• SQL2.1 upgrade
• Visibility checks

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;

Restart after changing the preload list. Using pg_stat_statements alongside it is recommended so plan IDs can be correlated with query text. Optional zstd compression requires a build with zstd support.

Query Plans

SELECT *
FROM pg_stat_plans;

The view exposes userid, dbid, toplevel, queryid, planid, calls, total_exec_time, plan, and plan_advice. 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.

Version, Memory and Visibility

Release 2.1.1 uses SQL extension version 2.1. After installing new files and reloading the library at restart, update existing SQL objects with ALTER EXTENSION pg_stat_plans UPDATE. The 2.1 upgrade drops and recreates the function and views; dependent user objects can prevent the update.

pg_stat_plans.max_plan_memory limits total plan-text storage. A full memory budget or oversized plan text can leave the text empty while counters remain tracked. pg_stat_plans.plan_advice requires PostgreSQL 19 or later and pg_plan_advice in the preload list.

Plan text and query IDs for other users require superuser access or pg_read_all_stats; otherwise only the caller’s own query details are shown. The reset function is not granted to PUBLIC. Collected plan text can contain sensitive constants; restrict monitoring privileges accordingly.