sdlc.dev

SQL and PostgreSQL path · How a query runs · Topic 1 of 13

How a query runs: parse, plan, execute

Not started

When you send SELECT ... FROM orders WHERE customer_id = 42, you have said nothing about how to find those rows. The server process that owns your connection turns the text into a parse tree, rewrites it (expanding views, for instance), asks the planner for the cheapest way to produce the result, and then runs that plan. The same SQL can run in a millisecond or a minute depending on which plan wins.

Why it matters: Almost every performance question about a database is really a question about the plan it picked, and why.

  1. 1One backend per connection

    Each client connection is served by its own server process, which parses, plans and executes that client's statements.

  2. 2Declarative in, plan out

    SQL describes the result; the planner turns it into a tree of scans, joins and sorts that produces it.

  3. 3Cost is an estimate

    The planner compares candidate plans by estimated cost, using table statistics gathered by ANALYZE. Bad statistics make bad plans.

  4. 4Prepared statements reuse work

    A prepared statement is parsed once; after a few runs PostgreSQL may switch it to one generic plan for all parameter values.

1

How it works

The explanation, with examples you can run.

The four stages

  1. Parse. Check the syntax and build a tree; look up the tables and columns it names.
  2. Rewrite. Apply rules. A query on a view becomes a query on the view's underlying tables here.
  3. Plan. Generate candidate plans (scan each table this way or that, join in this order with this method) and estimate the cost of each. Keep the cheapest.
  4. Execute. Walk the plan tree. Each node pulls rows from its children and hands rows to its parent, so a LIMIT 10 at the top can stop the work early.

Each client connection has its own backend process doing all of this. That is one reason connections are not free, which comes back in the pooling topic.

Cost, statistics and ANALYZE

The planner cannot run every candidate to see which is fastest. It estimates. For each table it knows roughly how many rows and pages there are; for each column, the most common values, a histogram of the rest, and the fraction of nulls. These live in pg_statistic (readable through the pg_stats view) and are refreshed by ANALYZE, which autovacuum also runs in the background.

ANALYZE orders; SELECT attname, n_distinct, most_common_valsFROM pg_statsWHERE tablename = 'orders' AND attname = 'status';

From these numbers it estimates how many rows each step will produce, and from that the cost in abstract units (by default, reading one page sequentially costs 1.0 and a random page 4.0). If the estimate says a condition matches 25% of the table, reading the whole table in order is usually cheaper than millions of random index lookups. If it matches 25 rows, the index wins. When estimates are wrong, for example right after a bulk load, or when two columns are correlated in ways single-column statistics cannot see, the planner can pick a plan that is cheap on paper and slow in practice.

Planning once, running many times

Planning costs time too, so drivers often use prepared statements: parse once, then execute with different parameters.

PREPARE orders_for(bigint) AS  SELECT id, total_cents FROM orders WHERE customer_id = $1; EXECUTE orders_for(42);

By default the first five executions are planned for the actual parameter values (custom plans). After that PostgreSQL also builds a generic plan and uses it if its estimated cost is not worse than the average of the custom ones. That is usually a good trade, but if one parameter value matches half the table and another matches three rows, a single generic plan suits only one of them. plan_cache_mode lets you force either behaviour when you see this.

The next topic is how to see the plan the planner picked, and how close its estimates were.

2

Check yourself

3 questions. Get them all right to finish this topic.

  1. 1.Which stage decides whether a query reads the table with an index scan or a sequential scan?
  2. 2.After a bulk load of a million rows, a query that should use an index is doing a full scan. What is the first thing to try?
  3. 3.With plan_cache_mode at its default (auto), what happens to a prepared statement with parameters?

0 of 3 right so far. Get all three to finish this topic.

Go deeper