The SQL is identical. Did PostgreSQL really use the same plan?

The optimizer is choosing before it knows the full work

The database estimates row counts, compares access paths and join methods, and chooses a plan. In an ideal world, the estimate would reflect this merchant and this date range. In real systems, statistics are samples and workload distributions are uneven.

An illustrative query might look like this:

PREPARE recent_orders(bigint, timestamptz) AS
SELECT id, created_at, total_cents
FROM orders
WHERE merchant_id = $1 AND created_at >= $2;

For a small merchant, an index on (merchant_id, created_at) might find a few rows and fetch their values efficiently. For the largest merchant, the same index may identify an enormous fraction of the table's relevant pages. A different scan strategy, or a plan suited to a partition containing that merchant, can have much lower total cost. The same SQL does not imply the same optimal access path.

The interesting failure is not the large merchant having more data. It is the application sometimes losing the chance to choose a plan with that merchant's values in mind.

Prepared statements have a decision of their own

PostgreSQL can use a custom plan, generated with the current parameter values, or a generic plan, reused without specializing to those values. The generic choice avoids repeated planning work. For a fairly uniform workload, that is often good. For heavily skewed merchants, one reusable plan may be a poor fit for a minority of executions.

The rules are more specific than “prepared statements are always generic.” In PostgreSQL 18's default plan_cache_mode=auto, the first five executions of a parameterized prepared statement use custom plans. PostgreSQL then compares a generic plan's estimated cost with the average estimated custom-plan cost to decide whether reuse is attractive. This is a heuristic, not a guarantee that it chooses the fastest real execution for every merchant. See the PREPARE documentation.

A production application's use of server-side preparation also depends on its driver settings, pooling behavior, and connection lifetime. I would first establish that the slow query actually reached the server in the prepared form being investigated. Otherwise we could spend a day blaming a plan cache the application never used.

Reproduce the plan difference, don't guess it

I would compare representative merchant IDs while keeping the same date window, database statistics, and amount of data returned. For a real prepared statement, EXPLAIN EXECUTE exposes the plan chosen for that execution:

EXPLAIN (ANALYZE, BUFFERS)
EXECUTE recent_orders(12345, '2026-10-01');

Use a safe environment if the result is expensive. Check whether the plan is generic or custom, and compare predicted rows with actual rows. A node estimated at 300 rows but emitting 3 million has a cardinality problem that can poison subsequent decisions. A node estimated accurately but still taking a long time may instead be doing necessarily expensive work. The EXPLAIN guide distinguishes estimated plan cost from observed execution timing.

For diagnosis in a controlled session, compare plan_cache_mode=force_custom_plan and force_generic_plan. If the large merchant's plan changes and its execution improves with a custom plan, we have evidence for parameter-sensitive selection. Those settings are useful experiments, not permission to force one plan policy globally before understanding planning overhead and the ordinary merchant mix.

Then inspect the data distribution. PostgreSQL's pg_stats tracks approximate distinct counts, common values, and histograms. A tenant key with a few enormous values may require a larger statistics target to represent its skew accurately. Where multiple predicates are correlated, extended statistics such as multivariate most-common-value lists may help estimation. They do not automatically solve all joins or parameter-sensitive behavior. The statistics documentation describes both the capabilities and limits.

Three fixes that solve different problems

First, if the planner is making a bad decision because it has poor information, improve appropriate statistics and run ANALYZE, then remeasure. This helps bad statistics, not a fundamentally unsuitable access path.

Second, if a generic plan is inappropriate for a small class of skewed parameters, selectively use custom planning for that query or construct a separate supported query path. A two-millisecond planning cost may be justified if it saves seconds of execution for enterprise merchants. It may be wasteful across millions of cheap small-merchant calls. The tradeoff is planning CPU versus execution cost, measured at fleet scale.

Third, if the large merchant genuinely dominates writes, storage and reporting, isolate the workload or adjust its physical organization. That could mean a dedicated reporting projection, partitioning policy, or a targeted export path. Changing plan_cache_mode does not make a scan of four million rows cheap, and splitting tenants is an architectural decision with its own cost.

A good Staff answer ends with an experiment and an ownership decision. Which query shapes are affected? What plan was used? What did the estimates miss? Who will monitor regressions after the correction? The underlying engineering skill is learning to distrust a plausible plan without assuming the optimizer is foolish.

The reporting team fixes the plan. Its next query joins order headers with order items. The plan is sensible, but the runtime changes sharply when the input becomes larger. Why does the join suddenly start writing temporary files?