Replica and Standby Query Plans #

A report is moved to a read replica to take load off the primary, and gets slower. The replica has the same hardware, the same data, the same indexes and — being a physical copy — byte-identical statistics. The plan is the same too. Only the execution differs, and the reasons are all in the environment rather than the query.

This page covers what actually differs between a primary and a streaming standby from a query’s point of view: cache state, configuration, visibility and conflicts. It builds on the settings precedence in session parameter drift and the diagnostic techniques in identifying plan bottlenecks.

What Is and Is Not Shared #

A physical streaming replica applies the primary’s WAL, so it is a block-for-block copy. That makes some things necessarily identical and leaves others free to differ.

Identical by construction: table and index contents, pg_statistic rows (statistics are stored in a table and replicated like any other), table and index sizes, the visibility map, and therefore every input the planner reads from the catalogs. A given query, given the same settings, gets the same plan on both.

Free to differ:

So a plan difference between primary and replica always comes from settings, and a timing difference usually comes from cache.

Shared, and not shared A table. Data and indexes are identical because the replica is a block level copy. Statistics are identical because they are stored in a replicated table. Shared buffer contents differ because each server caches its own workload. Configuration differs because it is per server. Visibility differs because of replication lag. Write capability is absent on the standby. same? why data and indexes identical block-level copy statistics identical stored in a table buffer cache differs own workload configuration differs per-server settings visibility differs replication lag writes, ANALYZE absent read-only plan differences mean settings; timing differences usually mean cache

Annotated Evidence #

Same plan, different execution:

-- on the primary
EXPLAIN (ANALYZE, BUFFERS) SELECT count(*) FROM orders WHERE placed_at >= '2026-01-01';
Finalize Aggregate  (actual time=412.204..412.206 rows=1 loops=1)
  ->  Gather  Workers Planned: 3  Workers Launched: 3
        ->  Parallel Seq Scan on orders  (actual rows=1025502 loops=4)
              Buffers: shared hit=225226 read=0
-- on the replica: identical plan shape
Finalize Aggregate  (actual time=8402.118..8402.120 rows=1 loops=1)
  ->  Gather  Workers Planned: 3  Workers Launched: 0
        ->  Parallel Seq Scan on orders  (actual rows=4102008 loops=1)
              Buffers: shared hit=1204 read=224022
-- two differences: no workers launched, and the data was not cached

The environment that explains it:

-- run on each server
SELECT name, setting FROM pg_settings
WHERE name IN ('max_parallel_workers_per_gather', 'max_parallel_workers', 'work_mem',
               'effective_cache_size', 'random_page_cost', 'shared_buffers');
SELECT pg_is_in_recovery();
SELECT now() - pg_last_xact_replay_timestamp() AS replication_lag;
 max_parallel_workers  | 0        ← on the replica: parallelism disabled
 shared_buffers        | 4GB      ← primary has 32GB
 replication_lag       | 00:00:02
Three comparisons that localise it Three items are annotated. An identical plan shape with different timings points at cache or workers rather than the planner. Workers launched being zero on the replica points at parallelism settings. Buffers moving from hit to read points at a cold cache rather than a plan problem. same plan shape, 20× the time not a planner difference Workers Launched: 3 → 0 parallelism settings differ Buffers: hit 225k → read 224k cold cache, not a plan problem compare pg_settings on both servers before anything else

Internals: Why the Planner Behaves the Same #

It is worth being precise about why plans do not differ by themselves. The planner’s inputs are the catalogs — pg_class for sizes, pg_statistic for distributions, pg_index for available indexes — and the GUCs. On a physical replica the catalogs are byte-identical to the primary’s by definition of physical replication, so the only free variable is configuration.

This has a practical consequence that surprises people: you cannot fix a replica’s plan by running ANALYZE there, because a standby is read-only and ANALYZE writes. If the statistics are wrong on the replica, they are wrong on the primary too, and that is where the fix goes.

It also means EXPLAIN without ANALYZE gives identical output on both servers whenever the settings match — which makes it a clean test. Identical EXPLAIN output plus divergent EXPLAIN ANALYZE timings localises the problem to execution: cache, workers, I/O contention or conflicts. Different EXPLAIN output localises it to configuration, and the precedence rules in role and database level setting overrides will say which layer supplied the difference.

Logical replication is a different case. A logical replica is a separate database that receives row changes, so it has its own statistics, its own indexes and potentially a different schema — plan differences there are expected rather than anomalous.

Where is the difference coming from? Three cases. If EXPLAIN output differs between the two servers, the cause is configuration and pg_settings identifies which. If the plan matches but buffers move from hit to read, the cause is cache warmth. If the query is cancelled rather than slow, the cause is a replay conflict. what differs between the servers? the plan itself compare pg_settings configuration buffers hit vs read cache warmth not the plan query cancelled replay conflict see conflict stats EXPLAIN without ANALYZE is the cleanest comparison

Routing read traffic deliberately #

Most replica problems start as a routing decision made for the wrong reason. “Send reads to the replica” is not a strategy; which reads, and what they tolerate, is.

Reads that tolerate staleness — reports, exports, analytics, search indexes, anything driving a dashboard — are the natural fit. They are also usually the large queries, which is convenient: moving them removes the most load from the primary and they benefit most from a replica tuned with a large work_mem and few concurrent sessions.

Reads that must see their own writes are the ones that break. A request that inserts a row and then reads it back through a replica may not find it, and the failure is intermittent because it depends on lag at that instant. This is the read-your-writes problem, and the only robust answers are to route those reads to the primary or to wait for a known LSN to be replayed.

Reads inside a transaction that also writes cannot go to a replica at all, since the transaction belongs to one server.

A useful rule is to route by workload role rather than by statement: a reporting role whose connections point at the replica, a web role whose connections point at the primary, and an explicit decision whenever something moves between them. That also gives each role its own settings, which is what makes the tuning differences above expressible.

Measuring lag correctly #

Lag is reported several ways and they answer different questions. pg_last_xact_replay_timestamp() compared with now() gives time lag, which is what staleness-sensitive reads care about — but on an idle primary it grows without anything being wrong, because no new transactions have arrived to replay.

The byte-based measures from pg_stat_replication on the primary — the differences between sent_lsn, write_lsn, flush_lsn and replay_lsn — say how much WAL is outstanding at each stage, which is what capacity planning cares about. A replica that is flushing promptly but replaying slowly is I/O bound on replay, and that is precisely the state in which queries and replay compete.

The two together diagnose the common failure: replay lag rising while queries slow down means the replica cannot both serve and keep up, and the answer is fewer queries, more I/O, or a second replica — not a query change.

Memory, Parallelism and Cache Considerations #

Shared buffers. A replica sized smaller than its primary caches less, and a read-only reporting replica has a different access pattern anyway. Plans priced with effective_cache_size describing the primary’s memory will be optimistic on a smaller replica; setting each server’s effective_cache_size to its own reality is the fix.

Parallel workers. max_parallel_workers_per_gather and max_parallel_workers are per server, and replicas often have them lowered or zeroed to bound resource use. A plan with Workers Planned: 3 and Workers Launched: 0 runs the whole scan in one process — the same plan, several times the duration.

work_mem. A reporting replica usually wants a larger value than the primary, since it runs few concurrent sessions doing large sorts and aggregations. That is a legitimate reason for the two servers to differ deliberately.

I/O contention with replay. The startup process applying WAL competes for I/O with queries. On a replica that is barely keeping up, queries and replay slow each other, and the symptom is both rising lag and rising query times.

Hot standby feedback. Enabling it prevents query cancellations by holding back cleanup on the primary, at the cost of bloat there. It is a trade between the two servers, not a free improvement.

Step-by-Step Diagnostic Workflow #

  1. Confirm which server you are on: SELECT pg_is_in_recovery();

  2. Compare EXPLAIN output — without ANALYZE — on both. Identical output means the planner agrees and the difference is in execution.

  3. Diff the settings that affect planning and execution: the parallel worker limits, work_mem, random_page_cost, effective_cache_size, shared_buffers.

  4. Compare Buffers. A large read count on the replica against a large hit count on the primary is cache warmth, not a plan problem.

  5. Check Workers Launched against Workers Planned. A gap means the worker pool was exhausted or disabled.

  6. Check replication lag and conflicts with pg_stat_database_conflicts and pg_last_xact_replay_timestamp().

  7. Fix statistics on the primary, since a standby cannot analyse. Plan-level problems seen on a replica are primary-level problems.

Indexes that exist only where they are not needed #

A physical replica cannot have its own indexes: CREATE INDEX is a write, and every index on the replica is an index on the primary. That constraint shapes reporting workloads more than anything else on this page.

The consequence is that an index built purely to serve a nightly report still costs the primary a write amplification on every insert and update to that table, all day, for a benefit that materialises once. Whether that trade is worth taking is a real decision, and it is often decided by default rather than deliberately.

Three ways out are worth knowing. A covering index that serves both an operational query and the report costs one index rather than two, and the design questions are in covering index design. A partial index restricted to the rows the report actually touches — a status, a date range — is far cheaper to maintain than a full one. And for genuinely separate analytical needs, a logical replica is a different database that can carry its own indexes and its own schema, at the cost of being a separate thing to operate.

The fourth option, which is frequently the right one, is to accept a sequential scan on the replica. A report that reads most of a table gains nothing from an index anyway, and a parallel sequential scan on a machine doing nothing else is a perfectly good plan.

Failover changes the answer #

Everything above assumes the replica stays a replica. When one is promoted, the settings that made it a good reporting server become the settings of the primary — a large work_mem sized for four concurrent reports, parallelism limits set for a quiet machine, and a shared_buffers sized for a smaller working set.

That is worth checking before it happens rather than during an incident. The practical form is a short list of settings that must be reconciled at promotion, applied by whatever performs the failover, so that a promoted standby does not run production traffic with reporting-shaped memory limits.

The plan-level consequence is direct: after a promotion, plans are chosen with the new server’s settings, which means a fleet-wide plan change at the worst possible moment. Capturing the baselines described in plan regression detection with the SETTINGS option makes that comparison possible afterwards, and reconciling the settings beforehand makes it unnecessary.

Common Pitfalls #

Running ANALYZE on the replica. It is read-only and the command fails. Diagnostic signal: an error on a standby. Fix: analyse the primary.

Assuming different plans mean different statistics. They cannot be different on a physical replica. Diagnostic signal: divergent plans. Fix: compare settings.

Blaming the planner for cold cache. The plan is the same. Diagnostic signal: identical shape, buffers read rather than hit. Fix: compare Buffers, warm the cache or accept it.

Copying the primary’s configuration verbatim. A reporting replica wants different values. Diagnostic signal: a reporting workload with the primary’s small work_mem. Fix: tune each server for its role.

Ignoring Workers Launched. A serial execution of a parallel plan looks like a mystery. Diagnostic signal: planned workers not launched. Fix: check the parallel settings on that server.

Treating cancellations as failures of the query. They are replay conflicts. Diagnostic signal: canceling statement due to conflict with recovery. Fix: see the conflict guide.

Routing read-your-writes traffic to a replica. A row just written may not be there yet. Diagnostic signal: intermittent missing records immediately after a write, worse under load. Fix: route those reads to the primary, or wait for the LSN.

Reading time lag on an idle primary. It grows because nothing is being replayed, not because anything is behind. Diagnostic signal: lag rising overnight while byte lag is zero. Fix: use the LSN differences for capacity questions.

Frequently Asked Questions #

Do read replicas have the same query plans as the primary? #

For physical streaming replication, yes, provided the settings match: statistics, sizes and indexes are byte-identical because the replica is a block-level copy. Plan differences mean configuration differences, not statistics differences.

Why is the same query slower on a replica? #

Usually a cold buffer cache, fewer parallel workers, or I/O contention with WAL replay. Compare the Buffers lines and the Workers Launched counts before suspecting the plan.

Can I run ANALYZE on a standby? #

No. A standby is read-only and ANALYZE writes statistics. If statistics are wrong, fix them on the primary and the change replicates.

Why do replica queries get cancelled? #

WAL replay can conflict with running queries — for example when it removes rows a query still needs. The standby cancels the query after max_standby_streaming_delay. Enabling hot_standby_feedback avoids most of these at the cost of bloat on the primary.

Up: Runtime Environment