Making slow queries fast

11 min read · last reviewed 2026-10-17

Open in the interactive app: practice, quiz, mock interview →

In one minute

SQL says what you want; the planner decides how, by enumerating candidate plans and picking the one with the lowest estimated cost.
Estimates come from statistics (row counts, distinct values, common values, histograms). Bad statistics produce bad plans, and many "the database got slow" incidents are a plan change, not a hardware problem.
Your levers: indexes that match the access pattern, predicates the planner can reason about, and fresh statistics.
Read the actual plan (EXPLAIN ANALYZE) first. A large gap between estimated and actual rows points straight at the problem.

How it works

From text to plan

The database parses the statement, rewrites it (expanding views, flattening simple subqueries), and hands a logical tree to the optimizer. The optimizer picks an access path per table (sequential, index, index-only, or bitmap scan), a join order, and a join algorithm (nested loop, hash, or merge). Join orders grow factorially, so planners use dynamic programming for small joins, an idea that goes back to System R (Selinger et al., 1979), and heuristics past a threshold (PostgreSQL switches to genetic search at 12 tables). A quieter limit bites first: Postgres only reorders explicit JOINs while the list stays within join_collapse_limit, 8 by default, so a 10-way join written with JOIN syntax keeps part of the order you wrote (Postgres docs).

Cost model and cardinality

Each plan node gets a cost from estimated page reads and per-row CPU work, with random reads priced above sequential ones. The decisive input is cardinality: how many rows each step yields. ANALYZE samples the table and stores null fraction, distinct count, a most-common-values list, and a histogram of the rest. The planner multiplies selectivities as if predicates were independent, which fails for correlated columns: city = 'Paris' AND country = 'FR' is underestimated by orders of magnitude. Multi-column statistics (CREATE STATISTICS) fix known correlated pairs (Postgres docs).

Why misestimates hurt

A nested loop with an indexed inner side is ideal when the outer side yields 10 rows. If reality is 500,000 rows, the same plan does 500,000 random probes where a hash join would read each side once. Errors compound up the tree, so a 10x miss at the bottom can become 10,000x at the top. The Join Order Benchmark study found that every major engine it tested routinely produced large cardinality errors on multi-join queries, and that these errors mattered far more to plan quality than the cost model did (Leis et al., VLDB 2015).

Indexes and query shapes

A B-tree on (tenant_id, created_at) serves WHERE tenant_id = ? ORDER BY created_at DESC LIMIT 50 with a short range scan and no sort, but does not help a filter on created_at alone. Wrapping a column in a function (lower(email)) hides it from a plain index. A covering index holding every selected column skips the table heap. Deep OFFSET pagination reads and discards every skipped row; keyset pagination (WHERE id > last_seen) does not. ORM N+1 patterns issue one query per parent row; batch them.

Down the stack

Layer 1The contract

The optimizer promises one thing: whatever plan it picks returns the same rows as the SQL you wrote. It promises nothing about speed, about plan stability across ANALYZE runs or version upgrades, or about row order unless you write ORDER BY. EXPLAIN shows estimates; EXPLAIN (ANALYZE, BUFFERS) runs the query and reports actual rows, loops, time per node, and pages hit in cache versus read. From Postgres 18, EXPLAIN ANALYZE includes the buffer counts without asking (Postgres 18 release notes). Even an unchanged table can get a different plan after ANALYZE, because each run draws a new random sample (Postgres docs). Anything you rely on beyond result equivalence, such as "this query uses that index", is an observation, not a guarantee.

Layer 2The mechanism

PostgreSQL keeps per-column statistics in pg_statistic (readable through pg_stats): null_frac, n_distinct, most_common_vals with most_common_freqs, histogram_bounds, and correlation between physical and logical order. ANALYZE samples 300 times default_statistics_target rows, so 30,000 rows at the default of 100, whatever the table size. Costs are in abstract units set by seq_page_cost (1.0), random_page_cost (4.0), cpu_tuple_cost (0.01), cpu_index_tuple_cost (0.005) and cpu_operator_cost (0.0025) (Postgres docs). Join order is searched with dynamic programming over subsets of relations until geqo_threshold (12), then by a genetic algorithm. Prepared statements get custom plans for the first five executions, then switch to a generic plan if its estimated cost is not much worse (Postgres docs), which is exactly where skewed parameters get hurt; plan_cache_mode = force_custom_plan turns the switch off. Postgres has no hint syntax of its own: forcing a join order or index takes the pg_hint_plan extension, which reads hints from a comment in the query (pg_hint_plan).

Layer 3The machine

Data lives in 8 KB pages. A page in shared_buffers costs well under a microsecond to reach; one in the OS page cache costs a syscall and a copy, a few microseconds; one on local NVMe about 100 µs; one on network block storage 0.5 to 2 ms. The default random_page_cost of 4 is not a disk measurement: the docs say a truly random read costs much more than four sequential ones, and 4 assumes most index reads hit cache. Many SSD deployments lower it toward 1.1, which makes index scans look cheaper and changes plans. Sorts get work_mem (4 MB by default) per node, and hash tables get work_mem times hash_mem_multiplier (2.0 by default, so 8 MB) (Postgres docs); past that they spill batches to temporary files, turning a 50 ms join into seconds of disk I/O. Index-only scans skip the heap only for pages marked all-visible in the visibility map, which vacuum sets, so a table that vacuum cannot keep up with quietly loses index-only scans.

Why it's built this way

Random I/O costs much more than sequential I/O, so the planner needs a cost model that tells scans from index probes.
Counting rows exactly would cost as much as running the query, so the planner estimates from a fixed-size sample.
A sample cannot capture every correlation between columns, so the planner assumes independence and sometimes misses by orders of magnitude.
Searching every join order is factorial, so exhaustive search stops at a threshold and heuristics take over.
Planning itself costs CPU on every call, so plans are cached, and a cached plan can be wrong for the next parameter.

Diagram

flowchart LR
    Q["SQL text"] --> P[Parser]
    P --> R["Rewriter (views, subqueries)"]
    R --> O[Optimizer]
    S["Statistics (ANALYZE)"] --> O
    I["Index metadata"] --> O
    O --> PL["Lowest-cost plan"]
    PL --> E[Executor]
    E --> RES[Rows]
    E -.->|"estimated vs actual rows"| A["EXPLAIN ANALYZE"]

Worked example

An orders table holds 200 million rows (80 GB). The dashboard filters customer_id = ? and the last 30 days, ordered by created_at DESC LIMIT 20. With only an index on created_at, the planner walks the last 30 days of that index (about 6 million entries), fetching heap rows until it finds 20 matches. Active customers return in 3 ms; quiet ones take 4 seconds. "Only some users complain" is the signature.

CREATE INDEX ON orders (customer_id, created_at DESC) INCLUDE (total, status) turns this into an index-only scan of about 20 entries, with P99 under 2 ms for everyone. The cost is roughly 8 GB of index and one more B-tree update per insert, measurable WAL volume at 3,000 inserts per second. Usually worth it for a hot read path, but it is a trade.

In the wild

PlanetScale customer, 2026: the planner drops an index overnight. With no deploy, failover or change in traffic, a Postgres primary went from about 20% to 100% CPU. One query pattern, at the same volume as before, went from milliseconds to the 10-second timeout and took almost half of all database time, because the planner had stopped using its index. The customer blocked that query pattern with a resource budget, the database recovered within about 15 minutes, and the root cause was investigated afterwards (PlanetScale).
GitHub, 2021: one bad query plus retries holds a primary down. On 10 August an edge case in one of GitHub's busiest applications generated a poorly performing query against a MySQL primary. Application retry and queueing logic kept the load up, so the primary could not recover on its own and writes to that cluster failed for 1 hour 17 minutes. GitHub fixed the query and changed the retry logic (GitHub).

Trade-offs

Option	Strengths	Weaknesses	Choose when
Composite index	Large, predictable speedup	Slower writes, more storage	A hot query has a stable shape
Rewrite the query	No write cost, often the biggest win	Code change; ORM may resist	Plan shows avoidable sorts or scans
Better statistics	Fixes many queries at once	Useless if no good plan exists	Estimates and actuals diverge
Plan hints	Immediate, deterministic	Freezes a plan that ages badly	Emergency mitigation
Cache or precompute	Removes the query from the hot path	Staleness, invalidation	Expensive and read often

Pitfalls in production

Plan flips after growth. A table crosses a threshold and the planner switches to a sequential scan overnight. Alert on latency per query fingerprint, not just database CPU.
Generic plans for skewed values. A prepared statement planned for a rare value is reused for a common one. Force custom plans for skewed columns.
Index sprawl. One index per incident, none removed, and inserts cost triple. Audit index usage counters and drop unused ones.
Testing on small data. Fast on 10,000 staging rows, pathological in production. Test plans against production-sized data.
Analytics on the primary. A one-hour report holds a snapshot that blocks vacuum and bloats hot tables. Route it to a replica.

Leadership lens

Make slow-query logs and per-fingerprint latency part of the platform, so regressions show up as a dashboard diff rather than a ticket.
In design review, ask for a feature's top five queries and the index serving each. If nobody can name them, the schema is not ready.
Treat index builds on large tables as risky deploys: build concurrently and off-peak.
Give shared databases an owner for query health, or every team optimizes locally while the whole degrades.

Check yourself

Why can a query become 1,000 times slower with no code or schema change?
Show answer
Data growth or skew shifts the statistics, the estimate crosses a threshold, and the planner picks an access path or join that is far worse for the real row counts.
An index on (created_at, customer_id) exists, but a filter on customer_id alone still scans. Why?
Show answer
The B-tree is ordered by its leading column, so one customer's entries are scattered across the whole index.
What does a large estimated-versus-actual gap in EXPLAIN ANALYZE tell you?
Show answer
Statistics are wrong for that predicate (stale, skewed, or correlated), so every plan choice above that node is suspect.

Sources and further study

Primary

Selinger et al., Access Path Selection in a Relational Database Management System (SIGMOD 1979): the System R optimizer, which introduced cost-based access paths and dynamic programming over join orders.
Leis et al., How Good Are Query Optimizers, Really? (VLDB 2015): the Join Order Benchmark, measuring how wrong real cardinality estimates are and what that costs.

Systems docs

PostgreSQL: Query Planning settings: cost constants, GEQO, collapse limits and plan_cache_mode.
PostgreSQL: ANALYZE: sampling, statistics targets and why plans can change between runs.
pg_hint_plan: the extension teams use to pin a plan in Postgres, and its hint grammar.

Books

Database Internals, Alex Petrov, chapter 1 "Introduction and Overview" (section "DBMS Architecture"): where the parser, optimizer and execution engine sit relative to storage.
Designing Data-Intensive Applications (1st ed.), Martin Kleppmann, chapter 2 "Data Models and Query Languages": why declarative queries leave the access path to the optimizer.

Other curricula

donnemartin/system-design-primer: frames this as "SQL tuning": benchmark, turn on the slow query log, tighten the schema and add indexes; it does not cover plans or statistics.
roadmap.sh: System Design: includes SQL Tuning as its own node, alongside Denormalization and Index Table.

Watch

Database Query Optimizers Part 1 (CMU Database Group): Andy Pavlo on rule-based and cost-based plan search.
Query Optimization Part 2 (CMU Database Group): cardinality estimation and why estimates go wrong.

Free resources

PostgreSQL: Using EXPLAIN: reading plan nodes, costs, and actual row counts.
PostgreSQL: Statistics Used by the Planner: what the planner knows and how extended statistics help.
Use The Index, Luke: a free, vendor-neutral book on indexes and SQL.
MySQL: EXPLAIN Output Format: the same ideas from the MySQL side.

Open in the interactive app: practice, quiz, mock interview →