Finding the Missing Index with EXPLAIN ANALYZE

Staging had 50K orders. Production had 40M. Same query, different planet.

The Query

SELECT * FROM orders
WHERE tenant_id = $1 AND status = 'pending'
ORDER BY created_at DESC
LIMIT 50;

EXPLAIN (ANALYZE, BUFFERS)

Seq Scan on orders  (cost=0..892341 rows=120000 width=...)
  Filter: ((tenant_id = 1) AND (status = 'pending'))
  Rows Removed by Filter: 39880000
  Buffers: shared hit=450000 read=120000
Execution Time: 4231.442 ms

Fix

CREATE INDEX CONCURRENTLY idx_orders_tenant_status_created
ON orders (tenant_id, status, created_at DESC);

Execution time: 4.2s → 12ms.

Always test query plans against production-scale stats, not empty staging DBs.