pg_store_plans

What pg_store_plans does

pg_store_plans works like pg_stat_statements, but at the plan level. For every query the server executes, it captures the execution plan, normalizes it (stripping out volatile details like cost estimates and row counts), and accumulates statistics per unique plan. The result is a view where each row represents a distinct plan variant, with columns tracking how often it ran, how long it took, and how much I/O it generated.

The key insight is that the same query can have multiple different execution plans. A SELECT * FROM orders WHERE customer_id = $1 might use an index scan for selective values and a sequential scan for common ones. pg_stat_statements aggregates all executions under a single queryid — you see one average, which is rather like averaging the speed of a sports car and a horse-drawn cart and concluding you have a moderately fast vehicle. pg_store_plans separates each plan variant with its own planid, so you can see that the index scan variant averages 2ms while the sequential scan variant averages 800ms.

The two extensions share the same queryid, so you can join them: pg_stat_statements provides the query text and query-level totals, while pg_store_plans breaks those totals down by plan.

When to use pg_store_plans

pg_store_plans is most valuable when query performance is inconsistent or when you suspect plan regressions — the particular ailment where a query that ran perfectly well yesterday has quietly adopted a worse strategy today. Specific scenarios:

• Plan regression detection — a query that was fast suddenly becomes slow because the planner switched from an index scan to a sequential scan after an ANALYZE or a statistics change
• Understanding performance variance — when pg_stat_statements shows high stddev_exec_time, pg_store_plans can reveal whether the variance comes from multiple plan variants rather than external factors
• Historical plan analysis — pg_store_plans records first_call and last_call timestamps for each plan, so you can see when the planner started or stopped using a particular strategy
• Validating index additions — after creating a new index, confirm the planner actually uses it by checking whether a new planid appears with better statistics
• Debugging prepared statements — PostgreSQL may use generic plans for prepared statements after enough executions, sometimes producing worse plans than custom plans. pg_store_plans makes this visible.

Installation and setup

pg_store_plans is a third-party extension maintained by the OSSC-DB group (the same team behind pg_hint_plan). It must be compiled from source and loaded as a shared library at server startup. This means a server restart is required when enabling it for the first time.

-- 1. Add to postgresql.conf (requires restart)
-- pg_store_plans must be loaded alongside pg_stat_statements
shared_preload_libraries = 'pg_stat_statements, pg_store_plans'

-- compute_query_id must be on or auto (PostgreSQL 14+)
compute_query_id = auto

-- 2. Restart PostgreSQL, then create the extension
CREATE EXTENSION pg_store_plans;

-- 3. Verify it's working
SELECT count(*) FROM pg_store_plans;

On PostgreSQL 14+, compute_query_id must be set to on or auto. If it is set to no, pg_store_plans is silently disabled — it will not raise an error, but the view will remain empty.

The latest release (1.9) supports PostgreSQL 17. Check the releases page for version compatibility.

Key columns

The pg_store_plans view provides per-plan statistics. These are the columns you will use most often.

-- Key columns in pg_store_plans
-- Identifiers
queryid            -- joins to pg_stat_statements.queryid
planid             -- hash of the normalized plan
userid             -- user who executed the plan
dbid               -- database OID

-- Plan text
plan               -- the execution plan (format set by pg_store_plans.plan_format)

-- Statistics
calls              -- number of times this plan was executed
total_time         -- cumulative execution time (ms)
rows               -- total rows returned or affected

-- Block I/O (when track_io_timing is enabled)
shared_blks_hit    -- buffer cache hits
shared_blks_read   -- blocks read from disk

-- Timestamps
first_call         -- when this plan was first seen
last_call          -- when this plan was last executed

The queryid column is the bridge to pg_stat_statements. Join on it to get the query text, since pg_store_plans stores plans but not queries.

Configuration

Most defaults are reasonable for getting started. The parameters you are most likely to tune are max (if you have many distinct plans), min_duration (to reduce overhead on high-throughput systems), and plan_format (if you want machine-readable plan output).

-- Key configuration parameters (postgresql.conf)
pg_store_plans.max = 1000              -- max number of plans to track (default: 1000)
pg_store_plans.track = top             -- top, all, none, or verbose (default: top)
pg_store_plans.plan_format = text      -- text, json, xml, yaml, or raw (default: text)
pg_store_plans.min_duration = 0        -- only store plans slower than N ms (default: 0 = all)
pg_store_plans.max_plan_length = 5000  -- truncate plans longer than N bytes (default: 5000)
pg_store_plans.log_analyze = off       -- include ANALYZE output (default: off)
pg_store_plans.log_buffers = off       -- include buffer usage stats (default: off)
pg_store_plans.save = on               -- persist stats across server restarts (default: on)

Practical queries

Top plans by total time

The most direct query — shows which specific execution plans are consuming the most cumulative database time.

-- Top 10 plans by total execution time
SELECT
  queryid,
  planid,
  calls,
  round(total_time::numeric, 2) AS total_ms,
  round((total_time / calls)::numeric, 2) AS mean_ms,
  left(plan, 200) AS plan_preview
FROM pg_store_plans
ORDER BY total_time DESC
LIMIT 10;

Queries with multiple plan variants

Finds queries where the planner chose different strategies across executions. These are the queries worth investigating first — if a query has three plan variants, at least two of them are not the best one.

-- Find queries with multiple plan variants
-- These are queries where the planner chose different strategies
SELECT
  queryid,
  count(*) AS plan_count,
  round(sum(total_time)::numeric, 2) AS total_ms,
  sum(calls) AS total_calls
FROM pg_store_plans
GROUP BY queryid
HAVING count(*) > 1
ORDER BY sum(total_time) DESC
LIMIT 10;

Joining with pg_stat_statements

Since pg_store_plans stores plans but not query text, join with pg_stat_statements to see both together.

-- Join with pg_stat_statements to see query text alongside plans
SELECT
  s.query,
  p.planid,
  p.calls,
  round(p.total_time::numeric, 2) AS plan_total_ms,
  round((p.total_time / p.calls)::numeric, 2) AS plan_mean_ms,
  p.plan
FROM pg_store_plans p
JOIN pg_stat_statements s
  ON p.queryid = s.queryid
WHERE p.calls > 0
ORDER BY p.total_time DESC
LIMIT 5;

Detecting plan regressions

This is the query that earns pg_store_plans its keep. It compares plan variants for the same query to find cases where one plan is significantly slower than another — a sign of plan regression or parameter-sensitive queries.

-- Detect plan regressions: same query, different plan performance
SELECT
  p1.queryid,
  p1.planid AS fast_plan,
  round((p1.total_time / p1.calls)::numeric, 2) AS fast_mean_ms,
  p2.planid AS slow_plan,
  round((p2.total_time / p2.calls)::numeric, 2) AS slow_mean_ms,
  round(((p2.total_time / p2.calls) / nullif(p1.total_time / p1.calls, 0))::numeric, 1) AS slowdown_factor
FROM pg_store_plans p1
JOIN pg_store_plans p2
  ON p1.queryid = p2.queryid
  AND p1.planid != p2.planid
WHERE p1.calls >= 10
  AND p2.calls >= 10
  AND (p2.total_time / p2.calls) > (p1.total_time / p1.calls) * 2
ORDER BY (p2.total_time / p2.calls) - (p1.total_time / p1.calls) DESC
LIMIT 10;

Cloud availability

pg_store_plans requires compilation from source and shared memory allocation at server startup — requirements that managed providers are disinclined to accommodate. As of early 2026, none of the major providers include it. If you are on managed PostgreSQL, plan-level visibility will need to come from elsewhere.

How Gold Lapel relates

I should mention that Gold Lapel performs this same plan-level analysis as a matter of course. As a proxy sitting between your application and PostgreSQL, it captures every execution plan and tracks per-plan statistics automatically — no extension to install, no shared memory to configure, no restart to schedule around your traffic patterns.

The practical difference: Gold Lapel works on every managed PostgreSQL provider, since it observes plans at the proxy level rather than from inside the database. It also acts on what it finds — the plan data feeds automatic optimizations like materialized view creation and index recommendations, rather than waiting for someone to write the queries above and notice the regression.

If you are running self-managed PostgreSQL and want plan-level visibility without a proxy, pg_store_plans is the most direct option available. It does the job well. If you would prefer that visibility to also drive automatic improvements — or if you are on a managed provider where third-party extensions are simply not an option — Gold Lapel attends to that.