The Query Planner

The Database Internals board behind most “why is this query slow” questions.

Before you start. The last lesson showed that Postgres, the open-source database this series opens up, never deletes a row on update - it writes a fresh copy and leaves the old one alone for anyone still reading it, until vacuum clears it away later. This lesson is about a different machine entirely: the one that decides, for any query you type, which of the table’s rows to even look at, and in what order.

The idea in 30 seconds

SQL, the language you query a database in, describes the result you want; a separate part of the database, the planner, decides how to actually fetch it, and there can be thousands of legal ways to do that for one query.
The planner never reads your table to decide how to read it. It reads a small summary of each column (its common values, how many distinct values), rebuilt from a random sample of rows by a background job called ANALYZE, and bets on a plan from that alone.
Every planner decision comes down to one repeated question, selectivity: what fraction of rows will match?
Postgres estimates a query with two conditions by multiplying the fraction of rows each one matches, which assumes the columns are unrelated. When they aren’t, like a city and its country, the estimate can come out wrong by a large multiple.
EXPLAIN ANALYZE runs the query and prints, at every step, the row count the planner guessed beside the row count it actually got, and a big gap between the two is the single most useful thing to look at when a query is slow.

Read the story · Skip to the numbers