Skip to content

Indexing strategies for analytical queries

When the right index makes the query 200× faster, and when adding an index makes everything worse.

Published

10 min read

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.

Two query shapes, two index strategies#

Analytical queries divide into two families that need different indexing.

Family A: tenant-scoped, narrow column filter#

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.

Family B: tenant-scoped, broad scan with grouping#

SELECT contact_id, SUM(amount)
FROM invoices
WHERE tenant_id = ? AND created_at >= ?
GROUP BY contact_id
ORDER BY SUM(amount) DESC
LIMIT 20

Wide 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:

  1. Partition by created_at so the scan is bounded.
  2. Materialise the answer if the query is hit often (a small tenant_top_contacts table, refreshed on a schedule).
  3. Read replicas for the scan, so it does not compete with live writes.

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.

The mistakes that cost us#

Five patterns we hit, in order of how much pain they caused.

1. Adding indexes one at a time without checking the planner#

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.

2. The "covering index" anti-pattern#

"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:

  • The query is run >100 times per minute, AND
  • The columns added to cover are read-only or rarely updated.

3. Indexes on low-cardinality columns#

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.

4. The "just add an index" reflex on a table with high write rate#

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.

5. Forgetting that the planner caches plans#

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.

What we measure#

Three metrics, in priority:

p99 latency per named query#

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.

Index utilisation#

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. Some of the indexes we add are later dropped.

Write amplification#

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 worked example#

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:

  1. Added (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.
  2. Added (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.
  3. Removed the 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.

What to do when no index works#

Three honest options:

  1. Materialised view. Compute the answer asynchronously, store it, serve from the store. Refresh on a schedule that matches the freshness requirement.
  2. Read replica. Move the analytical query off the primary so it does not compete with writes.
  3. Different storage. Some queries are fundamentally analytical. Move the data into a column-oriented store (ClickHouse, DuckDB, even Postgres with TimescaleDB) where wide scans are cheap.

Each costs operational complexity. Do not reach for them until you have ruled out indexing and query reshaping.

The boring conclusion#

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.

Read this article in Arabic · العربية

Share this article

Ali Haitham Yosef

Ali Haitham Yosef, a software developer and company owner in Damascus. Working since 2015, with more than 600 projects delivered.

About Ali

Does your project look like what you read?

Write what you need in your own words, and we decide together whether what I offer fits your company.

Start a conversation