An Index That Helps One Query and Starves Another
An order-management database has orders (order_id PK, customer_id, order_date, status, total) with 40 million rows spanning four years, and order_items with 200 million rows. Existing indexes are the primary key, idx_cust on (customer_id, order_date) and idx_date on (order_date). A nightly report runs:
SELECT order_date, COUNT(*), SUM(total) FROM orders WHERE status <> 'CANCELLED' AND order_date BETWEEN '2025-03-24' AND '2025-03-30' GROUP BY order_date;
About 30% of orders are CANCELLED. The planner chooses idx_date, fetches roughly 190,000 rows by rowid and filters status afterwards; the report takes 4.2 seconds against an SLA of 300 ms. A second query, a customer's open orders sorted by date, uses idx_cust correctly. Month-end days carry about 25 times the average daily order volume and a higher cancellation rate, but statistics were last refreshed five weeks ago.
1. Estimate the cardinalities for the nightly report and explain the 4.2 seconds in terms of I/O rather than CPU.
Read the worked answer
The date range covers 7 of about 1,461 days, so selectivity is 0.0048 and the range holds roughly 0.0048 × 40M ≈ 190,000 rows. Applying status <> 'CANCELLED' leaves about 0.70 × 190,000 ≈ 133,000 rows across 7 groups, about 19,000 per day.
The cost is dominated by the access pattern, not the arithmetic. idx_date yields 190,000 index entries - at roughly 20 bytes each, about 3.8 MB, or 470 pages of 8 KB, read in date order. Each of those 190,000 entries triggers a rowid lookup into a 40M-row heap that is not stored in date order, so the table pages are scattered. Measured 4.2 s over 190,000 lookups is about 22 µs per lookup, which is exactly the profile of a warm-but-scattered buffer pool; on cold storage it would be far worse. Filtering 133,000 rows and summing them is microseconds by comparison.
The lesson: the plan does almost the minimum possible index work and the maximum possible random table I/O, and the runtime follows the random I/O count, not the row count.
2. Propose a better index, estimate its cost, and state what you would drop to pay for it.
Read the worked answer
The report needs order_date and status for filtering and total for aggregation. An index on (order_date, status, total) covers the whole query: the range predicate on the leading column gives a contiguous index range of about 190,000 entries, status filters in-index, and total is present, so no heap access at all. At roughly 28–32 bytes per entry that is about 6 MB, around 750 pages, read mostly sequentially - tens of milliseconds, well inside the 300 ms SLA.
An alternative, (status, order_date, total), supports equality-friendly access, but the predicate here is <> 'CANCELLED', an inequality, so the leading column cannot be used as a clean range prefix; the optimizer would have to run a range scan per non-cancelled status value and merge. That is workable but strictly worse than the leading-date index for this report.
Payment: (order_date, status, total) has order_date as its leading column, so idx_date becomes a redundant prefix of it. Drop idx_date to avoid a second write path on every insert. Keep idx_cust - its leading column is customer_id, and the leftmost-prefix rule means nothing else serves the customer query.
Write cost is real: 40M-row table, one more index to maintain per insert and per status update. If the table also receives status updates on cancellation, consider making the index non-unique and monitoring update throughput before rollout.
3. A month-end variant of the query (2025-03-25 to 2025-03-31) joins order_items, and the planner picks a nested-loop index join that runs for minutes. Diagnose the misestimate.
Read the worked answer
The optimizer estimates row counts from column statistics assuming predicates are independent. It computes selectivity(order_date in range) from the histogram, roughly 0.0048, and selectivity(status <> 'CANCELLED') = 0.70, multiplies to about 0.0034 and estimates ~134,000 orders. It then costs a nested loop that probes order_items once per estimated order, which looks cheap.
Two facts break the estimate. First, the range is the last week of the month, and month-end days carry about 25 times average volume, so the true order count is closer to 3 million than 134,000 - a 20× underestimate that the stale histogram (five weeks old) does not capture. Second, order_date and status are correlated: month-end batches are cancelled more often, so the independence product is wrong in the other direction. The two errors do not cancel.
With 3 million outer rows, a nested loop performs 3 million index probes into a 200-million-row child table, and minutes of runtime is the expected outcome. The cost model never considered a hash join because at 134,000 rows a nested loop really is cheaper.
Remedies: refresh statistics and, more importantly, enable/collect extended statistics on the (order_date, status) column pair so the correlation is known; or rewrite the month-end job to materialize the filtered orders first. Verify with EXPLAIN ANALYZE that estimated and actual rows are within a small factor after the refresh - that gap is the real defect, not the join algorithm.