pg_store_plans

pg_store_plans : track plan statistics of all SQL statements executed

Overview

Packages

Source

pig build pkg pg_store_plans;		# build rpm/deb

Install

Make sure PGDG and PIGSTY repo available:

pig repo add pgsql -u   # add both repo and update cache

Install this extension with pig:

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

pig install pg_store_plans -v 18;   # install for PG 18
pig install pg_store_plans -v 17;   # install for PG 17
pig install pg_store_plans -v 16;   # install for PG 16
pig install pg_store_plans -v 15;   # install for PG 15
pig install pg_store_plans -v 14;   # install for PG 14

Config this extension to shared_preload_libraries:

shared_preload_libraries = 'pg_store_plans';

Create this extension with:

CREATE EXTENSION pg_store_plans;

Usage

Syntax:

SELECT * FROM pg_store_plans ORDER BY total_time DESC;
SELECT * FROM pg_store_plans_info;

Sources: Project page, Bundled docs

pg_store_plans tracks execution plan statistics for SQL statements, similar in spirit to how pg_stat_statements tracks statements. It records plan text, plan hash, timing, row counts, and buffer statistics, and its docs note that queryid can be used to join with pg_stat_statements.

Configuration

The upstream documentation requires:

shared_preload_libraries = 'pg_store_plans'
compute_query_id = 'on'

pg_store_plans needs shared memory, so adding or removing it requires a server restart. If compute_query_id is set to no, the module is silently disabled.

Viewing Plan Statistics

The statistics are exposed through the pg_store_plans view:

SELECT queryid, planid, plan, calls, total_time, rows
FROM pg_store_plans
ORDER BY total_time DESC;

SELECT * FROM pg_store_plans_info;

Important columns documented upstream include:

• queryid, the core-generated query ID
• planid, a normalized plan hash
• plan, in the format chosen by pg_store_plans.plan_format
• calls, total_time, and rows
• buffer statistics such as shared_blks_hit and shared_blks_read
• timestamps such as first_call and last_call

Helper Functions

SELECT pg_store_plans_reset();
SELECT pg_store_plans_textplan(plan);
SELECT pg_store_plans_jsonplan(plan);
SELECT pg_store_plans_xmlplan(plan);
SELECT pg_store_plans_yamlplan(plan);
SELECT pg_store_hash_query('SELECT 1');

These functions reset statistics, convert stored plans to different output formats, and compute query hashes.

GUCs

The extension documentation describes settings such as:

• pg_store_plans.max
• pg_store_plans.track
• pg_store_plans.plan_format
• pg_store_plans.min_duration
• pg_store_plans.log_analyze
• pg_store_plans.log_buffers
• pg_store_plans.log_timing
• pg_store_plans.plan_storage
• pg_store_plans.max_plan_length
• pg_store_plans.save

Together these control collection scope, plan representation, persistence, and storage behavior.