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

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.