pg_store_plans

Overview

Version

Build

You can build the RPM / DEB packages for pg_store_plans using pig build:

pig build pkg pg_store_plans         # build RPM / DEB packages

Install

You can install pg_store_plans directly. First, make sure the PGDG and PIGSTY repositories are added and enabled:

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

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

• Install
• pig
• dnf
• apt

pig install pg_store_plans;          # Install for current active PG version

pig ext install -y pg_store_plans -v 18  # PG 18
pig ext install -y pg_store_plans -v 17  # PG 17
pig ext install -y pg_store_plans -v 16  # PG 16
pig ext install -y pg_store_plans -v 15  # PG 15
pig ext install -y pg_store_plans -v 14  # PG 14

dnf install -y pg_store_plans_18       # PG 18
dnf install -y pg_store_plans_17       # PG 17
dnf install -y pg_store_plans_16       # PG 16
dnf install -y pg_store_plans_15       # PG 15
dnf install -y pg_store_plans_14       # PG 14

apt install -y postgresql-18-pg-store-plan   # PG 18
apt install -y postgresql-17-pg-store-plan   # PG 17
apt install -y postgresql-16-pg-store-plan   # PG 16
apt install -y postgresql-15-pg-store-plan   # PG 15
apt install -y postgresql-14-pg-store-plan   # PG 14

Preload:

shared_preload_libraries = 'pg_store_plans';

Create Extension:

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.