Query Performance Fundamentals

SQL Querying and Aggregations covers writing a correct query. This module is about a separate question: once that query is submitted, what determines whether it takes ten milliseconds or ten minutes — and how to find out which part is actually slow, rather than guessing.

From SQL text to rows: four stages#

SQL text   ↓Parse       -> check syntax, resolve table/column names   ↓Plan        -> build a logical plan: what needs to happen, in what order   ↓Optimize    -> the query optimizer chooses a physical plan: which               algorithm for each join, which index (if any) to use   ↓Execute     -> the physical plan actually runs against real data

The same SQL query can produce different physical plans depending on table sizes, available indexes, and current statistics — two databases holding identical schemas can execute an identical query completely differently. This is the central idea behind everything else in this module: query performance is a property of the plan, not of the SQL text itself.

The optimizer's job#

A query planner explores multiple possible physical plans for the same logical query — different join orders, different index choices — and picks the one it estimates will be cheapest, using statistics about the data (row counts, value distributions) collected ahead of time. Cost-Based Optimization covers exactly how that estimate is built and why it can be wrong.

Why the same query can be fast today and slow next month#

A plan chosen when a table had a thousand rows can become a poor choice once it has a hundred million — a nested loop join that was fine at small scale becomes disastrous at large scale, and an index that was selective enough to be worth using stops being selective as new, less distinct values accumulate. Query performance work is rarely a one-time fix; it is periodically re-checking whether the assumptions behind a plan still hold.

What the rest of this module covers#

Reading Query Execution Plans covers interpreting what EXPLAIN actually shows. Index Theory and Join Algorithms cover the two biggest levers a plan has available. Cost-Based Optimization covers how the planner chooses between them. Systematic Query Profiling covers a repeatable method for finding the actual bottleneck instead of guessing at one.

Common mistakes#

Tuning the SQL text without checking the plan#

Rewriting a query based on a hunch, without ever looking at what plan it actually produces, is optimizing blind — two syntactically different queries can produce the identical plan, and the "optimized" rewrite can turn out to change nothing measurable at all.

Assuming a plan that was good once stays good#

Data grows, value distributions shift, and a plan chosen against yesterday's statistics is not guaranteed to still be the right choice today. Cost-Based Optimization covers what keeps statistics current and what happens when they are not.

Treating "the query is slow" as one problem#

A slow query can be I/O-bound, CPU-bound, blocked on a lock, or waiting on network round trips to an unrelated service — each needs a different fix, and none of them are found by staring at the SQL text alone. Systematic Query Profiling covers distinguishing between them before attempting a fix.

Further reading#

• Using EXPLAIN — PostgreSQL documentation — a concrete, widely used implementation of the plan this module explains conceptually.
• EXPLAIN — ClickHouse documentation — the same command for the columnar database used as a running example elsewhere on this site.
• Query optimization — the general field this module is about.
• Query plan — what the optimize stage above actually produces.

See also#