# Why does a query use an Index Scan in staging but a Seq Scan in production, and how do I diagnose it with EXPLAIN?

> Run EXPLAIN (ANALYZE, BUFFERS) in production, then ANALYZE the table and check n_dead_tup; tune autovacuum per-table for sessions and add a partial index.

- Asked: 2026-07-09
- Answered: 2026-07-14
- Asked by: Zeynep
- Tags: postgresql, performans
- Source: https://muhammetsafak.com/just-ask/why-does-a-query-use-an-index-scan-in-staging-but/
- Language: en-US
- Author: Muhammet Şafak

---
**Question:** I'm on Aurora PostgreSQL. The same query returns instantly on staging and gets a nice Index Scan in the `EXPLAIN` output; but on the production primary, under real traffic, that same query drops to a Seq Scan and times out.

The query runs against a `sessions` table that takes very frequent INSERT/UPDATE/DELETE. How do I diagnose this difference, read `EXPLAIN` correctly, and set up a durable fix?


Short answer: the same query getting a different plan means the planner's cost estimate differs between environments.

## Short answer

On a heavily-written `sessions` table, the cause is almost always stale statistics and table bloat — not a "missing index." Before you add an index or rewrite anything, internalize this: the plan is an output of the cost model, and the cost model is only as good as the statistics feeding it. Fix the inputs and the plan usually corrects itself. If you still suspect the index after refreshing statistics, I went through how column order affects the plan in [the PostgreSQL composite index answer](/just-ask/choosing-the-right-composite-index-order-in-postgresql/).

## Why

1. **Statistics go stale on a high-churn table.** `sessions` takes constant INSERT/UPDATE/DELETE; when autovacuum/autoanalyze can't keep up, `n_distinct` and the histograms go wrong. If the planner says "10 rows" but 2 million come back, it's being misled — and that's why it picks the Seq Scan.
2. **Table and index bloat flip the plan.** Dead tuples from constant updates bloat the heap and the indexes; the index grows so large relative to live rows that the planner (correctly) decides a Seq Scan is cheaper.
3. **The data distribution may genuinely differ.** Staging has tiny/uniform data; a predicate that's selective on staging may match most rows in production, so a Seq Scan really is cheaper there. That isn't a bug — the plan is right for the data; fix the query/index, not the planner.
4. **PostgreSQL has no native query hints.** So there's no escape hatch that forces the plan; the real fix is repairing statistics/bloat/index.

## What to do

1. **Run `EXPLAIN (ANALYZE, BUFFERS)`, not plain `EXPLAIN`.** Plain `EXPLAIN` only shows estimates; on production, `EXPLAIN (ANALYZE, BUFFERS)` shows estimated vs actual rows side by side.
2. **Refresh the statistics and measure dead tuples.** Run `ANALYZE sessions;`, check `last_autoanalyze` and `n_dead_tup` in `pg_stat_user_tables`, and consider more aggressive per-table autovacuum.

   ```sql
   -- Diagnose: estimate vs actual
   EXPLAIN (ANALYZE, BUFFERS) SELECT ... FROM sessions WHERE ...;

   -- Fresh stats and dead-tuple check
   ANALYZE sessions;
   SELECT n_live_tup, n_dead_tup, last_autoanalyze
   FROM pg_stat_user_tables WHERE relname = 'sessions';

   -- More aggressive autovacuum, scoped to this table
   ALTER TABLE sessions SET (autovacuum_vacuum_scale_factor = 0.02,
                             autovacuum_analyze_scale_factor = 0.01);
   ```

3. **Account for the Aurora specifics.** There's the reader/writer split and the reality that you can't mirror production statistics onto staging exactly. Tune `autovacuum_vacuum_scale_factor`/`analyze_scale_factor` down for `sessions`; if you always filter to active sessions, consider a partial index.
4. **Don't reach for a hint first.** Fix statistics and bloat first; lowering `random_page_cost` (default 4.0) also nudges the planner toward index scans, but treat it as a tuning step after the statistics are fixed, not as the fix.

**Bottom line:** personally I'd run `EXPLAIN (ANALYZE, BUFFERS)` on production first, then `ANALYZE` the table and check `n_dead_tup`; very often the plan flips back once statistics are fresh. If `sessions` is inherently high-churn, I'd tune its autovacuum per-table and add a partial/covering index for the active-session predicate.

## Related Reading

- [When should I switch to a covering index with INCLUDE to get an index-only scan?](https://muhammetsafak.com/just-ask/switch-covering-index-include-get-index-only-scan/) — Just Ask
- [Should I use a partial index on a queue table where I only ever scan 'pending' rows?](https://muhammetsafak.com/just-ask/should-i-use-a-partial-index-on-a-queue-table-where/) — Just Ask
- [Which PgBouncer pooling mode should I pick — Session, Transaction, or Statement?](https://muhammetsafak.com/just-ask/pgbouncer-pooling-modes-and-prepared-statements/) — Just Ask