Handed it the EXPLAIN ANALYZE for our worst query and walked away with a partial index that took it from 2.3 seconds to 40ms

## Plan walk Total: **2,340 ms**. The time lives in one place: ``` -> Seq Scan on orders (cost=0.00..412,908 rows=1,102 width=64) (actual time=3.1..2,214.8 rows=847 loops=1) Filter: ((status = 'pending') AND (created_at > now() - interval '7 days')) Rows Removed by Filter: 11,204,153 Buffers: shared hit=2,341 read=201,882 ``` - Seq scan over 11.2M rows to keep 847 — 2,215 of the 2,340 ms are here. - `read=201,882` buffers: this is I/O-bound, scanning ~1.6 GB of table for a result that fits in one page. - Estimates are close to actuals (1,102 est vs 847 actual) — statistics are fine; the planner seq-scans because **no usable index exists**, not because it's confused. - The nested loop and sort above it are noise: 12 ms combined. ## Dominant cost, one sentence A full scan of `orders` to find the tiny, hot subset of recent pending rows — a needle-in-haystack query with no needle-shaped index. ## Minimal fix: partial index ```sql CREATE INDEX CONCURRENTLY idx_orders_pending_recent ON orders (created_at) WHERE status = 'pending'; ``` Why partial, not composite `(status, created_at)`: only ~0.4% of rows are ever `pending` (they leave the status quickly), so the partial index stays tiny and hot in cache, and every non-pending write skips it entirely. ## Costing - **Size:** ~45k pending rows at any time → index in the single-digit MB range, vs ~340 MB for a full composite on 11M rows. - **Write amplification:** your 40k order updates/hour touch this index only on transitions into/out of `pending` — roughly 2 index writes per order lifecycle. Negligible. - **Will the planner use it?** Yes — the query's literal `status = 'pending'` matches the index predicate exactly. One caveat: if any caller parameterizes status (`status = $1`), the partial index is invisible to that plan. I checked the query shape you provided: literal, so fine — but grep the codebase for parameterized variants before assuming full coverage. ## Verification ```sql EXPLAIN (ANALYZE, BUFFERS) <the query>; ``` Expect: `Index Scan using idx_orders_pending_recent`, actual time under ~50 ms, `Buffers: shared hit` in the low hundreds, `read` near zero after first run. If you still see a seq scan, run `ANALYZE orders;` once and re-check. ## Query-shape note The `SELECT *` pulls 64 bytes × columns nobody in the calling code uses (checked against the endpoint you named — it renders id, total, and created_at). Trimming the select list won't matter much *after* the index, but it would allow an index-only scan later if you add `total` as an INCLUDE column. Not needed today; noted for when this table doubles again.