Planner Search Space, Paths, Join Orders, Cost Parameters, and Generic vs Custom Plans

Learning outcomes

ServiceHub has a performance incident that looks contradictory. The same prepared query is extremely fast for a small tenant but slow for the largest tenant. An engineer opens EXPLAIN, sees a plan with “cost 42”, and reports that it should take roughly 42 milliseconds. Another engineer forces index scans globally. Both responses confuse the planner's model with execution reality.

PostgreSQL planning is a search problem. The planner considers legal ways—called paths—to obtain rows from base relations, combine relations, order data, aggregate it, and satisfy other query requirements. Each path receives cardinality and cost estimates. The planner chooses a complete plan with the lowest estimated cost according to its model, not according to a promise about elapsed time.

1. Build a skewed ServiceHub planning lab

The lab intentionally creates one very large tenant and many small tenants. Parameter sensitivity is much easier to understand when the data distribution actually gives different parameter values different optimal access strategies.

DROP TABLE IF EXISTS app.ch11_work_orders CASCADE;DROP TABLE IF EXISTS app.ch11_tenants CASCADE;CREATE TABLE app.ch11_tenants (    tenant_id integer PRIMARY KEY,    tenant_name text NOT NULL,    service_region text NOT NULL);INSERT INTO app.ch11_tenantsSELECT g,       format('Tenant %s', g),       CASE g % 4 WHEN 0 THEN 'north' WHEN 1 THEN 'south' WHEN 2 THEN 'east' ELSE 'west' ENDFROM generate_series(1, 40) AS g;CREATE TABLE app.ch11_work_orders (    work_order_id bigint PRIMARY KEY,    tenant_id integer NOT NULL REFERENCES app.ch11_tenants,    status text NOT NULL,    created_at timestamptz NOT NULL,    amount numeric(12,2) NOT NULL,    technician_id integer NOT NULL);INSERT INTO app.ch11_work_ordersSELECT g,       CASE WHEN g <= 180000 THEN 1 ELSE 2 + ((g - 180001) % 39) END,       CASE g % 5 WHEN 0 THEN 'queued' WHEN 1 THEN 'assigned' WHEN 2 THEN 'done' WHEN 3 THEN 'cancelled' ELSE 'waiting' END,       timestamptz '2026-01-01 00:00+00' + g * interval '15 seconds',       ((g % 50000) + 100)::numeric / 100,       1 + (g % 500)FROM generate_series(1, 240000) AS g;CREATE INDEX ch11_work_orders_tenant_created_idxON app.ch11_work_orders (tenant_id, created_at DESC);ANALYZE app.ch11_tenants;ANALYZE app.ch11_work_orders;

Tenant 1 owns most rows. A small tenant owns only a fraction of that volume. The same SQL text with different bind values can therefore justify different plans.

SELECT tenant_id, count(*) AS rows_per_tenantFROM app.ch11_work_ordersGROUP BY tenant_idORDER BY rows_per_tenant DESC, tenant_idLIMIT 8;

2. From relation to path to plan

A relation in planner terminology can be a base table, a join relation, or another logical row source the planner reasons about. For a base table, candidate paths may include a sequential scan, an index scan, a bitmap path, or a parallel-aware path. A join relation might have nested-loop, hash-join, or merge-join paths. A parameterized path depends on values supplied by rows from another relation; this is especially important inside nested loops.

EXPLAIN (COSTS, SETTINGS)SELECT t.tenant_name, w.work_order_id, w.created_atFROM app.ch11_tenants AS tJOIN app.ch11_work_orders AS w  ON w.tenant_id = t.tenant_idWHERE t.service_region = 'east'  AND w.created_at >= timestamptz '2026-02-01 00:00+00'ORDER BY t.tenant_id, w.created_at DESCLIMIT 100;

If a nested loop is chosen, an inner Index Cond such as tenant_id = t.tenant_id demonstrates the executor receiving a parameter from the current outer row. If your server instead chooses a hash or merge join, that does not make the concept false; it means the local cost model preferred another legal path for this dataset and settings.

3. Cost is a dimensionless model, not milliseconds

PostgreSQL's cost values are arbitrary units. Conventionally seq_page_cost is 1.0 and other disk/CPU costs are expressed relative to it. Startup cost estimates work required before a node can emit its first row; total cost estimates work if the node runs to completion. The parent includes child costs. Network transmission and result formatting are not fully represented in those costs.

SELECT name, setting, unit, contextFROM pg_settingsWHERE name IN (  'seq_page_cost','random_page_cost','cpu_tuple_cost','cpu_index_tuple_cost',  'cpu_operator_cost','effective_cache_size','parallel_setup_cost','parallel_tuple_cost',  'join_collapse_limit','from_collapse_limit','geqo_threshold','plan_cache_mode')ORDER BY name;

effective_cache_size is not a cache allocation. It is an estimate the planner uses about cache availability. Likewise, reducing random_page_cost does not make storage faster; it changes the planner's belief about the relative cost of random page access.

4. Join-order search and why query size matters

For inner joins the planner can often reorder relations because relational equivalence gives many legal join trees. The number of possible orders grows rapidly. PostgreSQL uses settings such as join_collapse_limit and eventually the Genetic Query Optimizer (GEQO) threshold to keep planning work bounded. This means planning time is itself a resource.

BEGIN;SET LOCAL join_collapse_limit = 1;EXPLAIN (SETTINGS)SELECT count(*)FROM app.ch11_tenants tJOIN app.ch11_work_orders w ON w.tenant_id = t.tenant_idJOIN (SELECT DISTINCT technician_id FROM app.ch11_work_orders) x  ON x.technician_id = w.technician_idWHERE t.service_region = 'north';ROLLBACK;

Setting join_collapse_limit = 1 can preserve explicit join order for eligible joins, but it is a diagnostic and advanced planning control—not a general recommendation to hand-plan joins. Outer joins and other semantics also constrain reorderings regardless of this setting.

5. Prepared statements: custom versus generic plans

A custom plan is planned using the actual parameter values for one execution. A generic plan does not depend on those values and can be reused. PostgreSQL's default plan_cache_mode = auto initially uses custom plans for parameterized prepared statements, then compares the average estimated cost of the first five custom plans with a generic plan and may reuse the generic plan when replanning no longer appears worthwhile.

DEALLOCATE ALL;PREPARE servicehub_tenant(integer) ASSELECT work_order_id, created_at, amountFROM app.ch11_work_ordersWHERE tenant_id = $1ORDER BY created_at DESCLIMIT 1000;

BEGIN;SET LOCAL plan_cache_mode = force_custom_plan;EXPLAIN (ANALYZE, BUFFERS, SETTINGS, TIMING OFF)EXECUTE servicehub_tenant(40);EXPLAIN (ANALYZE, BUFFERS, SETTINGS, TIMING OFF)EXECUTE servicehub_tenant(1);ROLLBACK;

In a custom plan, EXPLAIN EXECUTE generally shows the supplied literal in predicates. Tenant 40 may favor a narrow index-driven plan. Tenant 1 may still use the index because the ORDER BY ... LIMIT contract can make ordered retrieval attractive; do not prescribe the expected node name in advance.

BEGIN;SET LOCAL plan_cache_mode = force_generic_plan;EXPLAIN (ANALYZE, BUFFERS, SETTINGS, TIMING OFF)EXECUTE servicehub_tenant(40);EXPLAIN (ANALYZE, BUFFERS, SETTINGS, TIMING OFF)EXECUTE servicehub_tenant(1);ROLLBACK;

The generic form usually contains $1 rather than a value-specific literal. The important comparison is not “generic bad, custom good.” It is whether execution cost varies enough by parameter to justify repeated planning.

SELECT name, parameter_types, generic_plans, custom_plansFROM pg_prepared_statementsWHERE name = 'servicehub_tenant';

6. PostgreSQL 18 can show a generic plan without PREPARE

PostgreSQL 18's EXPLAIN (GENERIC_PLAN) accepts parameter placeholders and generates a generic plan without executing it. It cannot be combined with ANALYZE, because actual execution would require actual parameter values.

EXPLAIN (GENERIC_PLAN, SETTINGS)SELECT work_order_id, created_atFROM app.ch11_work_ordersWHERE tenant_id = $1::integerORDER BY created_at DESCLIMIT 100;

7. Production judgment and cleanup

Investigate prepared-plan incidents by recording query shape, parameter distribution, row estimates, generic/custom counters, planning time, execution evidence, and driver pooling behavior. A transaction-pooling layer may change how long prepared state survives. DDL, statistics updates, and relevant environment changes can also trigger re-analysis/replanning. Do not solve parameter sensitivity by forcing one plan mode globally unless repeated production evidence supports that policy.

DEALLOCATE ALL;DROP TABLE IF EXISTS app.ch11_work_orders;DROP TABLE IF EXISTS app.ch11_tenants;

Authoritative references

Planner and executor behavior is version-sensitive and workload-sensitive. Verify the exact PostgreSQL major, statistics state, server settings, indexes, and data distribution before generalizing any plan shown in this lesson.

• PostgreSQL 18 — PREPARE
• PostgreSQL 18 — pg_prepared_statements
• PostgreSQL 18 — EXPLAIN
• PostgreSQL 18 — Query Planning Configuration
• PostgreSQL 18 — Controlling the Planner with Explicit JOIN Clauses
• PostgreSQL 18 — Using EXPLAIN