My hypothesis: you're doing a full table scan because there's no index covering both user_id and status together. Postgres can't use an index scan for the filtered sort. Let me check the query plan.
Confirmed: full sequential scan, 2M rows filtered to 20. The fix is a composite index on (user_id, status, created_at DESC). Column order matters — equality filters first, sort column last.
Here's the migration:
-- Migration: add composite index for orders query
-- Estimated time: ~30s on 2M rows (online, no table lock)
CREATE INDEX CONCURRENTLY idx_orders_user_status_created
ON orders (user_id, status, created_at DESC);
-- After deploying, verify the plan changed:
-- EXPLAIN SELECT * FROM orders
-- WHERE user_id = $1 AND status = 'pending'
-- ORDER BY created_at DESC LIMIT 20;
-- Expect: Index Scan using idx_orders_user_status_createdAI can make mistakes. Verify important info.