The Escalation Ladder: Rewrite First, Index Second, Everything Else After
There is an order to fixing a slow query, and each rung costs more than the one above it: rewrite the query or fetch less; add or fix an index; refresh or extend the statistics; precompute with a materialized view or a denormalized column; cache the answer; send reads to a replica; partition the table; archive old rows; buy a bigger machine. The rungs above are cheap, reversible and local; the rungs below add moving parts that can be wrong, stale or out of sync. The discipline is to be able to say why the rung you chose was necessary, which means having a plan reading that rules out the ones above it.
Questions this Concept answers
- Why are the higher rungs of the ladder preferred to the lower ones?
This Concept is waiting for its first lesson!
There is an order to fixing a slow query, and each rung costs more than the one above it: rewrite the query or fetch less; add or fix an index; refresh or extend the statistics; precompute with a materialized view or a denormalized column; cache the answer; send reads to a replica; partition the table; archive old rows; buy a bigger machine. The rungs above are cheap, reversible and local; the rungs below add moving parts that can be wrong, stale or out of sync. The discipline is to be able to say why the rung you chose was necessary, which means having a plan reading that rules out the ones above it.
Are you a teacher? Sign in to start contributing.
Sign In