When Postgres quietly picks the slow plan
Postgres never tells you it ignored your index, started from the wrong table, or scanned a whole table to check one foreign key. Each fix here was small. The hard part was finding it and proving it was safe.
- catalogue search p90
- 281 → 29 mspartial index fix
- one search query
- 1.8 s → 2 msEXPLAIN ANALYZE
- deleting a large store
- ~24 h → 11 min~14 s to ~1 s per batch
- result parity
- 64 / 64query and mode cases identical
beforeLimit (actual time=1802 ms) -> Nested Loop -> Hash Join rows=6.8M 1.4 s -> Seq Scan whole catalogue -> HashAggregate offers UNION -> Index Scan items rows=1
afterLimit (actual time=2 ms) -> Nested Loop -> Index Scan sku_id = ANY($1) rows=20 0.9 ms -> HashAggregate offers UNION -> Index Scan items rows=1
the partial index
A refactor replaced database views with base-table joins. In an A/B benchmark five of six routes stayed within 2%, but catalogue search regressed 54% at p90.
The index it relied on was partial, on the active and not-deleted flags, and the rewritten join no longer applied those predicates. The planner couldn't prove the query stayed inside the index, so it skipped it without a word. A non-partial index took p90 from 281 ms to 29 ms. The same benchmark surfaced 12 route failures that already existed, which I flagged before sign-off.
the backwards plan
WhatsApp product search took about 1.7 s per message, and everyone suspected Elasticsearch. I profiled every search mode in-process and split the time into search engine, database and app. Elasticsearch was 0.3 s. One SQL query was 1.4 s.
Its only selective filter was an IN over a UNION of subqueries, which can't drive a join, so Postgres built a store's entire catalogue (6.8M intermediate rows) before keeping one. Passing the search engine's SKU ids as a redundant key filter gave the planner a primary-key path. I checked 546k rows first to prove the extra filter can never drop a result. EXPLAIN ANALYZE went from 1.8 s to 2 ms, and results diffed identical across 64 cases.
the cleanup
Deleting a large store (about 30k items) ran at roughly 14 s per batch of 5 items, which projected to a full day. Each batch runs in its own short transaction, so an interrupted run rolls back cleanly and a rerun picks up what's left.
A sweep of the catalog found 140 of 187 foreign-key columns pointing at the deleted tables had no index, so every deleted parent row seq-scanned child tables of up to 2.9M rows just to check the constraint. About 30 indexes, built concurrently on the live database, got a batch to ~2 s. Per-statement timing then pinned most of what was left on one delete whose foreign keys all looked indexed. They weren't, not usefully: the only index was partial, and the foreign-key check's query carries no matching predicate, so Postgres scanned a 445 MB table for every row. A plain index fixed it. A batch of 50 now takes ~1.1 s, and a whole store about 11 minutes.
the lesson
An index the planner can't prove applies is decoration, and that includes the foreign-key checks Postgres runs on your behalf. A filter that can't drive a join is a full scan with extra steps. Measure where the time goes before guessing. The full write-up is The index Postgres quietly stopped using.