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:

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

Sources: official docs, repo, 1.10 release notes

pg_store_plans tracks execution plan statistics for SQL statements, similar to how pg_stat_statements tracks statement statistics. The upstream 1.10 release note says this version adds PostgreSQL 18 support; the documented SQL surface is otherwise the same as current docs.

Required server settings

shared_preload_libraries = 'pg_store_plans'
compute_query_id = 'on'

pg_store_plans requires shared memory, so adding or removing it needs a server restart. The docs say it is silently disabled if compute_query_id is no.

Inspect stored plans

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

SELECT * FROM pg_store_plans_info;

The docs describe queryid as the join key for pg_stat_statements, and pg_store_plans_info as a one-row view that exposes module-level stats such as dealloc and stats_reset.

Helper functions

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

pg_store_plans_*plan() is useful when pg_store_plans.plan_format = 'raw'.

Key GUCs

• pg_store_plans.max
• pg_store_plans.track
• pg_store_plans.max_plan_length
• pg_store_plans.plan_storage
• 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.save

The docs describe plan_storage as file or shmem, and plan_format as text, json, xml, yaml, or raw.

Caveats

• Non-superusers cannot see plan, queryid, or planid for statements executed by other users.
• pg_store_plans and pg_stat_statements maintain entries independently, so low-frequency rows may not always have a matching peer.