pg_store_plans
track plan statistics of all SQL statements executed
Repository
ossc-db/pg_store_plans
https://github.com/ossc-db/pg_store_plans
Source
pg_store_plans-1.9.tar.gz
pg_store_plans-1.9.tar.gz
Overview
| Package | Version | Category | License | Language |
|---|---|---|---|---|
pg_store_plans | 1.9 | STAT | BSD 3-Clause | C |
| ID | Extension | Bin | Lib | Load | Create | Trust | Reloc | Schema |
|---|---|---|---|---|---|---|---|---|
| 6250 | pg_store_plans | No | Yes | Yes | Yes | No | Yes | - |
| Related | pg_show_plans auto_explain pg_stat_statements pg_hint_plan pre_prepare pg_stat_monitor explain_ui plprofiler |
|---|
pg18 breaks, fixed by Vonng
Version
| Type | Repo | Version | PG Ver | Package | Deps |
|---|---|---|---|---|---|
| EXT | MIXED | 1.9 | 1817161514 | pg_store_plans | - |
| RPM | PIGSTY | 1.9 | 1817161514 | pg_store_plans_$v | - |
| DEB | PIGSTY | 1.9 | 1817161514 | postgresql-$v-pg-store-plan | - |
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
pg_store_plans tracks execution plan statistics for all SQL statements, complementing pg_stat_statements with plan-level detail. Joinable via queryid on PostgreSQL 14+.
Viewing Plan Statistics
-- View tracked plans with statistics
SELECT queryid, planid, plan, calls, total_time, rows
FROM pg_store_plans
ORDER BY total_time DESC;
-- Check module status
SELECT * FROM pg_store_plans_info;
Key View Columns
| Column | Type | Description |
|---|---|---|
queryid | bigint | Query ID (joinable with pg_stat_statements) |
planid | bigint | Plan hash code |
plan | text | Representative plan text |
calls | bigint | Execution count |
total_time | double precision | Total execution time (ms) |
rows | bigint | Total rows retrieved/affected |
shared_blks_hit | bigint | Shared buffer hits |
shared_blks_read | bigint | Shared blocks read |
first_call | timestamptz | First execution time |
last_call | timestamptz | Last execution time |
Functions
-- Reset all statistics (superuser only)
SELECT pg_store_plans_reset();
-- Convert plan formats
SELECT pg_store_plans_textplan(plan); -- to text
SELECT pg_store_plans_jsonplan(plan); -- to JSON
SELECT pg_store_plans_xmlplan(plan); -- to XML
SELECT pg_store_plans_yamlplan(plan); -- to YAML
-- Calculate query hash
SELECT pg_store_hash_query('SELECT 1');
Configuration
| Parameter | Default | Description |
|---|---|---|
pg_store_plans.max | 1000 | Maximum tracked plans (server start only) |
pg_store_plans.track | top | top, all, verbose, none |
pg_store_plans.plan_format | text | text, json, xml, yaml, raw |
pg_store_plans.min_duration | 0 | Minimum execution time to track (ms) |
pg_store_plans.log_analyze | off | Include EXPLAIN ANALYZE output |
pg_store_plans.log_buffers | off | Include buffer statistics |
pg_store_plans.log_timing | true | Record actual timings |
pg_store_plans.plan_storage | file | Storage: file or shmem |
pg_store_plans.max_plan_length | 5000 | Max bytes for plan text |
pg_store_plans.save | on | Persist stats across restarts |
Feedback
Was this page helpful?
Thanks for the feedback! Please let us know how we can improve.
Sorry to hear that. Please let us know how we can improve.