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. 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.

-- 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.