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

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.

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.

   -- 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 toward 1.1 for SSDs can also nudge it toward index usage.

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.