How a SQL query executes

You write SELECT … JOIN … WHERE … and rows come back. In between, the database parses the text, estimates how many rows each step will produce, picks among many equivalent plans, and runs them against the stored pages. A query that took 5 ms last month can take 5 seconds today. Usually the plan changed. The estimates drifted, an index stopped being useful, or an ORM started sending many small queries.

This topic maps that pipeline, so you can see why a plan changed and what to do about it.

The big idea

The planner picks a plan from estimates, not from the real data. Three things decide the plan's speed.

• Access paths: how each table's rows are found, by scanning or by an index.
• Join algorithms: how two inputs are matched up.
• Statistics: the estimates of how many rows each step will produce.

You control the plan through indexes, statistics and the shape of the query. The answer to a slow query is almost never "make the database faster". It is usually "give the planner a better choice".

Concepts in this section

Read them in this order.

Row stores and column stores

Where the data is laid out affects which queries are cheap.

A query like SELECT sum(amount) FROM events WHERE day = … reads only two columns in a column store. A row store has to read the whole row for every event.

Use a row store for high-volume lookups and writes. Use a column store, or a copy of the data in a warehouse, for big scans and reports. Running heavy GROUP BY queries on the primary database takes buffer memory and write bandwidth away from user traffic. See transactions and isolation for what the primary must guarantee.

Common mistakes

• Adding indexes before checking the plan. Compare estimate with actual first.
• Letting the ORM choose the queries. Count the queries per request, and check for N+1 patterns.
• Running reports on the primary. Move them to a replica or a warehouse.
• Wrapping indexed columns in functions. Use a range or an expression index.

Key takeaways

• SQL becomes a plan, a tree of steps chosen by estimated cost. The executor runs it one row at a time.
• Access paths decide how rows are found. An index helps only when the condition is selective.
• Join algorithms depend on sizes, sort order and indexes. The planner chooses from row estimates.
• Bad estimates cause bad plans. Refresh statistics, and create extended statistics for correlated columns.
• Read the plan bottom up, and fix the step with the most actual time or reads.
• Row stores serve point lookups and writes. Column stores serve big scans.

Further learning

A short walkthrough of reading a real Postgres plan tree, with its costs and buffer hits:

Use The Index, Luke covers index design with a worked example for each pattern.

CMU 15-445 Database SystemsThis is Carnegie Mellon UniversityCMU 15-445/645