Read Replica Plan Differences #

A query planned on the primary uses a hash join; on the replica it uses a merge join with an external sort. The data is identical — the replica is a block-level copy — so the planner had the same statistics, the same sizes and the same indexes in front of it. Something else differed, and on a physical replica there is only one candidate: configuration.

The general comparison is in replica and standby query plans. This page is the procedure for reconciling a specific difference.

The Condition #

The planner’s inputs are catalogs and settings. Physical replication makes the catalogs identical, so any plan difference is a settings difference, and the settings that change plan choice are a short list:

work_mem and hash_mem_multiplier — decide whether a hash fits in memory, and therefore whether a hash join or aggregate is affordable at all.
effective_cache_size — how much caching the planner assumes, which changes index scan pricing substantially.
random_page_cost and seq_page_cost — the relative price of random and sequential access.
cpu_tuple_cost, cpu_index_tuple_cost, cpu_operator_cost — rarely changed, occasionally changed on one server and forgotten.
max_parallel_workers_per_gather, max_parallel_workers, min_parallel_table_scan_size — whether a parallel plan is considered at all.
enable_* flags — the drift in enable flags left off in sessions.
jit and its cost thresholds — which change execution rather than shape, but show up in timings.
plan_cache_mode, geqo_threshold, from_collapse_limit, join_collapse_limit.

They can differ for good reasons: a reporting replica genuinely wants a larger work_mem and may want different parallelism limits. The problem is when they differ by accident, through the layered overrides described in role and database level setting overrides, and nobody knows they do.

Annotated Evidence #

Comparing plans without executing anything:

-- run identically on both servers
EXPLAIN (SETTINGS)
SELECT o.customer_id, sum(o.total) FROM orders o JOIN customers c ON c.id = o.customer_id
WHERE o.placed_at >= '2026-01-01' GROUP BY o.customer_id;

-- primary
HashAggregate  (cost=418402.10..418404.10 rows=200 width=40)
  ->  Hash Join  (cost=8402.00..398402.08 rows=4102008 width=12)
        ->  Seq Scan on orders o
        ->  Hash  ->  Seq Scan on customers c
Settings: effective_cache_size = '48GB', work_mem = '256MB'

-- replica
GroupAggregate  (cost=1284102.40..1324102.44 rows=200 width=40)
  ->  Merge Join  (cost=1284102.40..1304102.42 rows=4102008 width=12)
        ->  Sort  Sort Key: o.customer_id
              ->  Seq Scan on orders o
        ->  Sort  Sort Key: c.id
              ->  Seq Scan on customers c
Settings: effective_cache_size = '4GB', work_mem = '4MB'
-- 4MB of work_mem: a hash of this size is priced out, so a sort-merge plan wins

The settings diff, taken directly:

SELECT name, setting, source FROM pg_settings
WHERE name IN ('work_mem','hash_mem_multiplier','effective_cache_size','random_page_cost',
               'max_parallel_workers_per_gather','jit','geqo_threshold')
ORDER BY name;

-- primary                         -- replica
 work_mem            | 262144       work_mem            | 4096
 effective_cache_size | 6291456      effective_cache_size | 524288
 max_parallel_workers_per_gather | 4  max_parallel_workers_per_gather | 0
-- three differences, all of them plan-relevant

Step-by-Step Resolution #

Capture EXPLAIN (SETTINGS) — no ANALYZE needed — for the same statement on both servers. The Settings: line records the non-default values behind each plan.
Diff the plan-relevant settings with the pg_settings query above, run through the same role on each server. Using a different role changes the answer, because role defaults are part of it.
Identify the source of each difference from pg_settings.source: a configuration file, ALTER SYSTEM, a database default, a role default or a connection option.
Decide which differences are intentional. A reporting replica with a larger work_mem is deliberate. A replica with 4 MB where the primary has 256 MB is almost certainly not.
Set effective_cache_size per server to roughly half to three-quarters of that machine’s RAM. It is a planner hint about caching, not an allocation, and copying the primary’s value to a smaller replica makes every index scan look cheaper than it is.
Set the parallelism limits for the replica’s role. A replica running four large reports wants workers; one running thousands of small queries may not.
Re-check the plan after reconciling. Identical settings on a physical replica produce identical plans, so any remaining difference means a setting was missed.

Before and After #

-- BEFORE: replica with 4MB work_mem
Merge Join  ->  Sort (external merge, Disk: 4102208kB)     estimated cost 1,324,102

-- AFTER: ALTER ROLE app_report IN DATABASE appdb SET work_mem = '256MB'
Hash Join   ->  Hash (Batches: 1, Memory Usage: 198204kB)  estimated cost 418,404

Tuning a replica for its own workload #

Reconciling differences does not mean making the two servers identical. A reporting replica and a transactional primary have genuinely different needs, and the point of the exercise is to make each difference deliberate.

A reporting replica typically wants a larger work_mem, because it runs a handful of concurrent sessions doing large sorts and aggregations rather than thousands doing point lookups. The usual work_mem × connections arithmetic is far less constraining with ten connections than with five hundred.

It wants parallelism, for the same reason: a machine with spare cores and few sessions is exactly where parallel plans pay off, and max_parallel_workers_per_gather at the default of 2 is conservative for that shape.

It usually wants jit left on with its default cost thresholds, since long analytical queries are what JIT compilation was designed for, whereas a primary serving short statements frequently does better with it off.

And it wants effective_cache_size set to its own memory, not the primary’s, so index scans are priced for the machine that will run them.

What it must not have is different enable_* flags or different cost parameters chosen to work around a plan somebody disliked. Those produce plan differences that look like tuning and behave like drift.

Common Pitfalls #

Assuming statistics differ. On a physical replica they cannot. Diagnostic signal: time spent comparing pg_stats. Fix: compare settings instead.

Diffing settings as different roles. Role defaults are part of the answer. Diagnostic signal: a diff that shows nothing while plans differ. Fix: same role on both sides.

Copying effective_cache_size to a smaller machine. Index scans are priced optimistically. Diagnostic signal: index scans chosen on the replica that read far more than expected. Fix: set it per machine.

Leaving parallelism off by inheritance. A plan with workers planned and none launched. Diagnostic signal: Workers Launched: 0. Fix: set the limits for the replica’s role deliberately.

Frequently Asked Questions #

Why does a read replica choose a different plan? #

Because a setting differs. On a physical replica the statistics, sizes and indexes are byte-identical, so configuration is the only remaining input — most often work_mem, effective_cache_size or the parallel worker limits.

How do I compare planner settings between two servers? #

Run EXPLAIN (SETTINGS) for the same statement on each, which records the non-default values with the plan, and diff pg_settings for the plan-relevant parameters using the same role on both servers.

Should a replica have the same configuration as its primary? #

Not necessarily. A reporting replica benefits from a larger work_mem, more parallelism and its own effective_cache_size. The differences should be deliberate and documented rather than inherited by accident.

Replica and Standby Query Plans — parent guide
Hot Standby Query Conflicts and Cancellations — sibling: why standby queries are cancelled
Role and Database Level Setting Overrides — tracing each difference to its source

Up: Replica and Standby Query Plans