Query planning

Your SQL says what rows you want, never how to find them

SQL is declarative: you say what result you want, not how to get it. The query planner, or optimizer, lists the possible ways to answer a query and runs the one it expects to be cheapest.

For SELECT * FROM orders WHERE customer_id = 42, the database can read every row, or look up 42 in a B-tree index and fetch only the matches. Both give the same answer. Which is cheaper depends on how many rows match.

Joins multiply the choices. 3 tables can be joined in 3! = 6 orders, and each join can be a nested loop (look up matches row by row), a hash join (build and probe a hash table) or a merge join (walk two sorted lists).

Query planning

PostgreSQL's EXPLAIN shows the plan it picked for your query

Put EXPLAIN before a query in PostgreSQL and it prints the chosen plan as a tree, such as "Index Scan using orders_customer_idx" or "Seq Scan on orders", with the estimated cost and rows for each step.

EXPLAIN ANALYZE also runs the query and shows the actual rows beside the estimates. A big gap usually means the statistics are stale. Running ANALYZE refreshes them and often fixes a query that suddenly got slow.

Query planning

The database guesses how many rows match, then prices each plan

The planner keeps statistics per table: row and page counts (pages are disk blocks, often 8 KB), distinct values, most common values and histograms. From these it estimates selectivity, the fraction of rows a condition keeps.

Each plan is priced as pages read plus rows processed, each with a weight. Random page reads, the kind index lookups make, cost more than reading pages in order. So an index wins when few rows match, and a full scan wins when many do.

For joins, it finds the cheapest plan for each small group of tables and builds bigger groups from those. This is dynamic programming, and it avoids pricing all n! orders. PostgreSQL switches to a genetic search at 12 or more tables.