Performance
Indexing strategies for analytical queries
When the right index makes the query 200× faster, and when adding an index makes everything worse.
Loading essay…
When the right index makes the query 200× faster, and when adding an index makes everything worse.
Loading essay…
An index makes a query 200× faster, and the next index makes the same query 4× slower because the planner stopped using the first one. Indexes are not "more is better." This essay is about the patterns that have worked for the analytical queries we ship (dashboards, reports, the "show me the totals for this month") and the patterns that have wasted nights of debugging.
Analytical queries divide into two families that need different indexing.
SELECT SUM(amount), COUNT(*)
FROM invoices
WHERE tenant_id = ? AND status = 'paid' AND due_date >= ?Selective on a small number of columns, returns a small number of
rows or aggregates. Wants a composite index with the columns in
selectivity order, leading with tenant_id.
CREATE INDEX idx_invoices_tenant_status_due
ON invoices (tenant_id, status, due_date);Order matters. tenant_id first because every query has it.
status second because it is more selective than due_date. due_date
last because it is a range filter and range filters end the usable
prefix of the index.
SELECT contact_id, SUM(amount)
FROM invoices
WHERE tenant_id = ? AND created_at >= ?
GROUP BY contact_id
ORDER BY SUM(amount) DESC
LIMIT 20Wide scan over a date range, GROUP BY a column that is not in the WHERE. Composite indexes do not help; the scan has to read every row in the date range to do the aggregation. What helps:
tenant_top_contacts table, refreshed on a schedule).Trying to "index your way" to fast Family-B queries has cost us more days than any other database lesson. Indexes do not help scans.
Five patterns we hit, in order of how much pain they caused.
A junior dev adds an index to fix a slow query. The query gets fast.
Three other queries that used a different index get slow because the
planner now picks the new one (in MySQL: index_merge surprises). Each
new index needs an EXPLAIN on every query against the table.
"Add every column the query references to the index, so it never has to touch the table." This is sometimes correct and frequently a trap: the index gets so wide that updating any of its columns is expensive, and the table now has 12 indexes that all need to be maintained on every write.
We use covering indexes only when:
CREATE INDEX ON invoices(status) where status has 4 distinct
values. The index is useless: any value matches a quarter of the
table, which is a lot. Worse, the planner sometimes uses it anyway
("the index is there, must be useful") and the query gets slower
than the table scan would have been.
The fix: composite indexes that lead with a high-cardinality column
(tenant_id, created_at), with the low-cardinality column second.
An index on a write-heavy table costs proportionally per write. We had a table doing 1,000 inserts per minute that grew to 8 indexes; write throughput dropped 3×. We dropped four of the indexes and replaced them with a denormalised "summary" table that batched updates.
You add an index, the next run of the query is fast. A week later
the same query is slow again because the cached plan was the old
one and the planner has not re-evaluated. We have a deploy step that
runs ANALYZE TABLE on hot tables after schema changes; without it,
some queries take days to "discover" the new index.
Three metrics, in priority:
Every query we care about is tagged with a name in the application
layer (Laravel's DB::statement with a comment, /* invoice_summary */). The slow query log groups by tag. We track p50/p95/p99 weekly.
A jump in p99 is the leading indicator that an index stopped being
used or that data growth crossed a threshold.
SELECT * FROM sys.schema_unused_indexes (MySQL) or
pg_stat_user_indexes (Postgres) tells us which indexes never get
hit. We review monthly and drop unused ones. About 20 % of the
indexes we have ever added have been later dropped.
Inserts per second to a table, vs. number of indexes. If insert rate drops as indexes accumulate, we either drop indexes or move the write to a less-indexed staging table that gets summarised asynchronously.
A real query that started at 14 seconds and ended at 80 ms:
-- The slow original
SELECT i.id, i.number, c.name AS contact_name, SUM(p.amount) AS paid
FROM invoices i
JOIN contacts c ON c.id = i.contact_id
LEFT JOIN payments p ON p.invoice_id = i.id
WHERE i.tenant_id = ?
AND i.status = 'open'
AND i.due_date < CURRENT_DATE
GROUP BY i.id
ORDER BY i.due_date ASC
LIMIT 50;What we changed, in order:
(tenant_id, status, due_date) index on invoices.
Query: 14 s → 4 s. The big win, by reducing the scan to a small
subset of invoices.(invoice_id) covering (amount) index on payments.
Query: 4 s → 1.2 s. The LEFT JOIN was scanning all payments per
tenant; now it lookups by invoice id.JOIN contacts c from the query and lazy-loaded
contact names in the application layer for the 50 rows the page
shows. Query: 1.2 s → 80 ms. The join was forcing a sort on a
different key; pulling it out let the planner pick a clean path.The third step is the one that surprised me. "Just add an index" did not work past a point. Re-shaping the query did.
Three honest options:
Each costs operational complexity. Do not reach for them until you have ruled out indexing and query reshaping.
Most slow queries can be fixed with two things: the right composite index, and a reshape that lets the planner pick a clean path. Most "the index isn't working" complaints are about an index in the wrong column order or a low-cardinality leading column. Most "the database is slow" tickets are about analytical queries on the same database that serves writes.
The next post is on why we measure on real devices. Or browse the performance topic for the rest.