A 21-Second Order History Query
An order-history screen runs SELECT * FROM orders WHERE DATE(created_at) = ? AND status = ? AND customer_id = ? on PostgreSQL 16. The table has 41 million rows at 4.2 GB, with 240,000 customers and about 170 orders per customer; roughly 4% of orders are in status SHIPPED, so a typical query matches about 6 rows. Existing indexes are the primary key on id, a single-column index on created_at, and a composite index on (status, customer_id). The screen takes 21 s. EXPLAIN (ANALYZE, BUFFERS) shows a Seq Scan over 41 million rows filtered to 6 rows, with about 512,000 shared buffer hits and reads. Autovacuum last analysed the table after a six-week-old backfill inserted 8 million rows; statistics target is the default. The developer's proposal is to add an index on DATE(created_at).
1. Explain why the existing created_at index is unusable here, and why an index on DATE(created_at) is the wrong fix.
Read the worked answer
The predicate DATE(created_at) = '2026-03-01' wraps the column in a function. The index on created_at is a B-tree over raw timestamps; nothing in it is ordered by the truncated date, so the planner cannot map the equality to a contiguous index range. The predicate is non-sargable, and the only correct options are a rewrite or an expression index.
Why the expression index is still the wrong first move:
- Selectivity per day is poor. 41M rows over roughly two years is about 56,000 rows per day. An index on DATE(created_at) narrows 41M to 56k and then filters 56k down to 6 in the heap - thousands of buffer touches for a query that needs a handful of rows.
- It is a large, single-purpose index: an index entry per row, maintained on every insert, to serve one screen.
- The real selective column is customer_id. Each customer has ~170 orders, and the query wants ~6. Searching by customer first means touching about 170 index entries and 2-3 heap pages, which is three orders of magnitude cheaper than 56,000.
- An expression index also cannot help other queries that filter created_at as a plain range.
If a date-only index were genuinely needed, the better form is a range predicate (created_at >= '2026-03-01' AND created_at < '2026-03-02'), which is sargable against the existing created_at index and requires no new object at all.
2. Design the index you would actually add, justify the column order, and explain why the existing (status, customer_id) index does not save the query.
Read the worked answer
Recommend CREATE INDEX CONCURRENTLY ON orders (customer_id, status, created_at) INCLUDE (whatever the screen displays).
Column-order reasoning, following the standard rule - equality columns first, range last:
- customer_id: equality and by far the most selective single column here, 6 rows out of ~170 for that customer.
- status: equality, 4% selectivity. As the second column it prunes the ~170 entries to about 7 without reading heap tuples.
- created_at: put last so it can also be used as a range if the query changes; with equality on the first two columns the third is scanned as a short in-index range regardless.
With this index the access path is: descend to (customer_id, status), scan a handful of entries, filter by created_at, fetch ~6 heap tuples (or zero heap fetches with a covering INCLUDE and a vacuumed visibility map). Expected time is single-digit milliseconds.
Why (status, customer_id) does not work: the leading column has low cardinality. A scan for status = 'SHIPPED' must walk every SHIPPED order in the table - about 1.64 million entries - checking customer_id on each, and the customer_id part is only useful for filtering inside that walk. Because the leading column is not constrained by an equality on a selective value, the index behaves like a worse full scan. This is the general rule: an index is only as selective as its leading column for that query.
Keep the old index only if other queries filter by status alone; otherwise it is pure write overhead and should be dropped after checking pg_stat_user_indexes for scans.
3. The backfill inserted 8 million rows six weeks ago and nothing has been analysed since. How much of the 21 s does that explain, and how would you verify the fix end to end?
Read the worked answer
Stale statistics plausibly explain a large part of the plan choice. After the backfill the planner believes the table is about 33 million rows when it is 41 million, and it has no fresh histogram or n-distinct data for customer_id, status or created_at. Under a bad selectivity estimate a sequential scan looks cheaper than a customer_id path, so the planner picks it. Even after adding the right index, bad statistics can keep the seq scan: an index is only chosen if the estimated row count justifies it.
Actions, in order:
- Run ANALYZE orders (or VACUUM ANALYZE) and re-check the plan. This is cheap and may change behaviour on its own.
- Raise the statistics target for the correlated columns, for example ALTER TABLE orders ALTER COLUMN customer_id SET STATISTICS 500, then re-analyse. Correlated columns benefit from extended statistics: CREATE STATISTICS ON (customer_id, status) FROM orders.
- Rewrite the predicate as a range anyway so it is sargable regardless of which index exists.
- Add the composite index and confirm with EXPLAIN (ANALYZE, BUFFERS).
Verification checklist for the final plan: an Index Scan or Index Only Scan on the new index, not a Bitmap Heap Scan over thousands of pages; 'Rows Removed by Filter' near zero; heap fetches near zero if the visibility map is current; shared buffers read in the low tens rather than 512,000; and p99 latency measured on the real screen, not just in psql. Also confirm the index build was done CONCURRENTLY so the order pipeline is not blocked, and check write amplification afterwards via pg_stat_user_tables.