EXPLAIN GENERIC_PLAN for Parameterized SQL #

pg_stat_statements shows the statement eating your CPU as SELECT … FROM shipments WHERE carrier_id = $1 AND status = $2 ORDER BY created_at DESC LIMIT $3. You want its plan, but it has no values, and picking arbitrary ones gives you a custom plan that may be nothing like what the application actually runs after its prepared statements switch to a generic plan.

PostgreSQL 16 added EXPLAIN (GENERIC_PLAN) for exactly this. The wider set of options is in EXPLAIN options and output formats; the lifecycle that decides when a generic plan is used is in prepared statement plan pinning.

The Planner Condition #

With GENERIC_PLAN, each $n is treated as a parameter of unknown value and inferred type, and the planner builds exactly the plan a cached generic plan would use:

The option cannot be combined with ANALYZE, because execution needs values.

Unknown parameter versus known value The same predicate carrier_id equals parameter one is estimated two ways. The generic plan path uses the column average from n_distinct, giving one estimate for all values. The custom plan path looks the literal value up in the most common values list or histogram, giving a value-specific estimate. The two paths can lead to different access paths. carrier_id = $1 GENERIC_PLAN: value unknown selectivity ≈ 1 / n_distinct one estimate for every carrier custom plan: carrier_id = 7 MCV frequency for 7 estimate specific to this carrier

Annotated EXPLAIN Evidence #

EXPLAIN (GENERIC_PLAN)
SELECT id, tracking_no FROM shipments
WHERE carrier_id = $1 AND status = $2
ORDER BY created_at DESC
LIMIT $3;
Limit  (cost=0.56..412.30 rows=… width=24)
  ->  Index Scan Backward using shipments_created_at_idx on shipments
        (cost=0.56..82314.10 rows=2010 width=24)
        Filter: ((carrier_id = $1) AND (status = $2))     -- $n placeholders: generic plan
-- rows=2010 is the averaged estimate for any carrier and status
-- the planner walks created_at backwards, filtering, expecting to hit LIMIT rows quickly

The custom plan for a small carrier:

EXPLAIN SELECT id, tracking_no FROM shipments
WHERE carrier_id = 912 AND status = 'in_transit'
ORDER BY created_at DESC LIMIT 50;
Limit  (cost=184.20..184.32 rows=50 width=24)
  ->  Sort  (cost=184.20..184.60 rows=160 width=24)
        Sort Key: created_at DESC
        ->  Index Scan using shipments_carrier_status_idx on shipments
              (cost=0.56..179.10 rows=160 width=24)
              Index Cond: ((carrier_id = 912) AND (status = 'in_transit'::text))
-- literal values: a 160-row index lookup, then a tiny sort

For carrier 912 the generic plan walks the whole created_at index backwards looking for 50 matches that are rare and old — the classic ORDER BY … LIMIT trap, discussed in why index scans sometimes underperform. For a large carrier it is excellent. Which one the application experiences depends on whether the generic plan is in use.

Signs of a generic plan Three lines are annotated. The Filter shows dollar placeholders rather than literals. The row estimate of 2010 is an average for all values. The backward index walk on created_at was chosen without knowing the carrier. Filter: ((carrier_id = $1) AND …) placeholders: no value known rows=2010 averaged selectivity Index Scan Backward … created_at_idx bet on dense matches rerun as custom plans for a rare and a common value

Step-by-Step Resolution #

  1. Take the statement text verbatim from pg_stat_statements.query or an auto_explain log line. Keep the $n placeholders.

  2. Run EXPLAIN (GENERIC_PLAN), adding casts where the type cannot be inferred:

    EXPLAIN (GENERIC_PLAN, SETTINGS)
SELECT … WHERE created_at >= $1::timestamptz AND tenant_id = $2::bigint;

  3. Read the plan for placeholder-driven choices — Filter: lines with $n, averaged rows=, and every partition listed.

  4. Compare custom plans for a rare and a common value of each skewed parameter. Use pg_stats.most_common_vals to pick them.

  5. Time the generic plan with real values before deciding anything:

    PREPARE q (int, text, int) AS
  SELECT id, tracking_no FROM shipments
  WHERE carrier_id = $1 AND status = $2 ORDER BY created_at DESC LIMIT $3;
SET plan_cache_mode = force_generic_plan;
EXPLAIN (ANALYZE, BUFFERS) EXECUTE q (912, 'in_transit', 50);
SET plan_cache_mode = force_custom_plan;
EXPLAIN (ANALYZE, BUFFERS) EXECUTE q (912, 'in_transit', 50);

  6. Choose the remedy. If the generic plan is bad for real traffic, either force custom plans with plan_cache_mode for that workload or give the generic plan a good option — here an index on (carrier_id, status, created_at DESC) makes both plans the same fast index scan.

The generic plan wins for one value and loses for another For large carrier 7, the generic plan takes 1.2 milliseconds and the custom plan 1.4. For small carrier 912, the generic plan takes 3,840 milliseconds and the custom plan 0.9, because the backward index walk must scan far to find fifty rare matches. carrier 7 (large) generic 1.2 ms · custom 1.4 ms carrier 912 (small) generic 3,840 ms custom 0.9 ms time the generic plan on the values it serves worst, not on one typical value

Before and After #

-- BEFORE: generic plan, carrier 912
Index Scan Backward using shipments_created_at_idx  Rows Removed by Filter: 18204410   Execution Time: 3840.6 ms

-- AFTER: CREATE INDEX ON shipments (carrier_id, status, created_at DESC)
Index Scan using shipments_carrier_status_created_idx  Index Cond: (carrier_id = $1 AND status = $2)   Execution Time: 0.8 ms

Why the index fixed both plans at once #

The generic plan was not wrong because it lacked values; it was wrong because the only indexes available forced a choice between two strategies whose costs depended heavily on the values. Walking created_at backwards is excellent when matches are dense and terrible when they are sparse. Looking up by carrier and sorting is excellent when matches are few and expensive when they are many. A plan without values has to bet on one.

An index on (carrier_id, status, created_at DESC) removes the bet. Equality on the first two columns positions the scan at exactly the right carrier and status, and the third column already provides the requested order, so the scan returns rows in ORDER BY order and stops after LIMIT rows whatever the carrier’s size. When the best plan for every parameter value is the same plan, the generic plan and every custom plan converge, and the five-execution switch stops mattering. That is the most robust fix for any statement where GENERIC_PLAN output diverges from custom plans: give the planner an access path whose cost does not depend on selectivity.

Using GENERIC_PLAN as a regression check #

Because GENERIC_PLAN needs no data values and no execution, it is cheap enough to run in continuous integration. Store the statement texts your application prepares — pg_stat_statements on a staging database is a convenient source — and after each migration run EXPLAIN (GENERIC_PLAN, FORMAT JSON) for each. Compare node types and index names with the previous run. A generic plan that switches from an index scan to a sequential scan after a migration is worth a look before deployment, even though the custom plans you test by hand may look unchanged. Parsing that output is straightforward with the approach in parsing EXPLAIN JSON output.

Two limits apply. Statements that depend on session state — temporary tables, search_path, row-level security settings — need that state recreated before planning. And the check sees plans, not timings: a plan that is unchanged in shape can still slow down as data grows.

Common Pitfalls #

Planning a normalized statement with made-up literals. Any literal produces a custom plan for that value. Diagnostic signal: a fast plan in your terminal and a slow statement in pg_stat_statements. Fix: plan the $n form with GENERIC_PLAN.

Placeholders the planner cannot type. WHERE created_at > $1 against an expression may fail type inference. Diagnostic signal: “could not determine data type of parameter $1”. Fix: add explicit casts.

Assuming the application uses the generic plan. It depends on plan_cache_mode, the five-execution rule, and whether the driver prepares statements at all. Diagnostic signal: generic and logged plans differ. Fix: check pg_prepared_statements.generic_plans on an application connection or capture with auto_explain.

Replacing $3 in LIMIT with a constant for testing. The generic plan’s cost for LIMIT $3 uses a default assumption, which can differ from LIMIT 50. Diagnostic signal: different plan shapes for the placeholder and literal limits. Fix: test both, and prefer the placeholder form to match production.

Frequently Asked Questions #

Which PostgreSQL version supports EXPLAIN GENERIC_PLAN? PostgreSQL 16 and later. On older versions, PREPARE the statement, set plan_cache_mode = force_generic_plan, and run EXPLAIN EXECUTE with any values.

Why can’t I use GENERIC_PLAN with ANALYZE? ANALYZE executes the statement, which needs values. Prepare the statement, force the generic plan, and run EXPLAIN ANALYZE EXECUTE with representative values instead.

What if the planner cannot infer a parameter’s type? Planning fails. Add an explicit cast at the placeholder, such as $1::bigint.

Up: EXPLAIN Options and Output Formats