# 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://www.muhammetsafak.com.tr/en/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. 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.

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. 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. **Stale statistics 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. Run `ANALYZE sessions;` and check `last_autoanalyze` in `pg_stat_user_tables`.
3. **Table and index bloat.** 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. Check `n_dead_tup` and consider more aggressive per-table autovacuum.
4. **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.
5. **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.
6. **Don't reach for a hint first.** PostgreSQL has no native query hints; the real fix is repairing statistics/bloat/index. Lowering `random_page_cost` toward 1.1 for SSDs can also nudge it toward index usage.

```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);
```

**Bottom line:** personally I'd run `EXPLAIN (ANALYZE, BUFFERS)` on production first, then `ANALYZE` the table and check `n_dead_tup`; in 9 cases out of 10 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

- [Should I use a partial index on a queue table where I only ever scan 'pending' rows?](https://www.muhammetsafak.com.tr/en/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://www.muhammetsafak.com.tr/en/just-ask/pgbouncer-pooling-modes-and-prepared-statements/) — Just Ask
- [How do I choose the right composite index column order in PostgreSQL?](https://www.muhammetsafak.com.tr/en/just-ask/choosing-the-right-composite-index-order-in-postgresql/) — Just Ask