A tenant-scoped dashboard query over an 80 million row orders table takes 9 seconds. It filters one tenant id and the last 30 days and returns 40 rows. The only index is on created_at. A proposal is to add a nightly materialised view. Which change should come first?

The deciding property

Ask whether the query is expensive because it must read a lot of rows, or because it must discard a lot of rows to find a few. Precomputation is the right tool for the first; an access path is the right tool for the second.

Here the answer is 40 rows. With an index on created_at alone, the database walks 30 days of the whole table — every tenant — and throws nearly all of it away. On 80 million rows with 400 tenants and a year of history, that is on the order of 2 to 3 million rows examined to return 40. A useful ratio to carry: when rows examined divided by rows returned exceeds roughly 1,000, you have an access-path problem, not a precomputation problem.

A composite index on (tenant_id, created_at) turns that into a range scan over one tenant's slice — hundreds of rows — and the query usually lands in single-digit or low tens of milliseconds. Cost: one index, some write amplification, no new moving parts and no staleness.

Why the other options fail

• The nightly materialised view. It is the heaviest option and it does not meet the requirement: a dashboard that shows today's orders cannot be served from a view refreshed at 02:00. It also adds a refresh job to operate, a staleness contract to publish, and a rebuild path to own — all before anyone has established that reading the rows is the expensive part.
• Cache the rendered response for five minutes. Caching hides the cost from the second viewer, not the first. With 400 tenants each loading the dashboard a few times a day, most requests are the first request for that key, so the hit rate is low and the 9 seconds remain. A cache in front of an unindexed query is a way to not find out that the query is wrong.
• Move it to a read replica. The same plan runs on the same data and takes the same 9 seconds. It protects the primary from the load, which is worth having for other reasons, and it adds replication lag to a dashboard that wanted fresher data, not staler.

When the index is the wrong answer

The index stops being enough when the query genuinely has to read everything it reads: a company-wide aggregate over all 400 tenants for the last quarter, a percentile over tens of millions of rows, a join that fans out before it filters. No index makes a sum of 30 million rows cheap, because the rows must be read.

Then precompute — and the form matters. An incrementally maintained rollup keyed by tenant and day, updated as orders arrive, costs one extra write per order and is always current. A full nightly refresh is the version to avoid unless the consumer genuinely wants yesterday's number, because its refresh cost grows with the table while its freshness gets worse.

The principle

Reach for precomputation when the cheapest correct plan is still too expensive. Until then you are precomputing the result of a bad plan, and you have bought a second copy of the data, a staleness window and a rebuild procedure to avoid writing one CREATE INDEX.