Database Engineering

Query Execution & Optimization

Parser, planner, executor; scans and joins; reading EXPLAIN ANALYZE; finding the actual bottleneck.

See how this works internally:Buffer Management →Query Engine →Performance Internals →
How a Query Executes: Planner and Executor
▶ interactive

The planner enumerates ways to run a query, prices each with statistics and a cost model, and hands the cheapest to an executor that pulls rows through a tree of scan, join, sort and aggregate nodes.

Reading EXPLAIN ANALYZE
▶ interactive

Read a plan from the innermost node outwards, compare estimated rows to actual rows at every node, and look for three tells — a huge Rows Removed by Filter, a high loops count, and an estimate that is off by an order of magnitude.

Query Optimization: Finding the Actual Bottleneck
▶ interactive

Slow queries have a short list of causes — missing index, wrong index, wrapped column, N+1, fan-out, deep OFFSET, SELECT *, bad statistics — and the plan tells you which one before you change anything.