Life of a query

For DBAs and operators

PostgreSQL 18 source trail: postgres.c shows parse analysis, query rewriting and planning; CMD_UTILITY commands are explicitly excluded from normal rewriting/planning.

The five stages

1. Parser (gram.y, scan.l): pure syntax, no catalog access. Produces a raw parse tree. Errors: ERROR: syntax error at or near "selec"
2. Analyzer (parse analysis): resolves names to OIDs, types and operators, expands *, produces a Query tree. Errors: ERROR: relation "customer" does not existERROR: column "nam" does not exist HINT: Perhaps you meant to reference the column "customers.name".
3. Rewriter: applies rules from pg_rewrite. Every view is a _RETURN rule, so the view reference becomes a subquery. Row-level security policies are also added here.
4. Planner/optimizer: pulls up subqueries, generates paths (scan methods per table, join orders and methods), costs them with pg_statistic data and the cost parameters, and turns the cheapest path into a plan. With geqo_threshold (12) or more FROM items it switches to the genetic optimizer; join_collapse_limit (explicit JOINs) and from_collapse_limit (pulled-up subqueries), both 8, cap how many items are merged into one list whose join order is searched.
5. Executor: a demand-driven "Volcano" iterator model. Each node implements init / next / end; calling next on the root pulls rows through the tree. Blocking nodes (Sort, Hash, HashAggregate) must consume their whole input before returning the first row, which is why they have a high startup cost.

Plan cache and prepared statements

With PREPARE / the extended protocol (most drivers), parse and rewrite happen once. The first five executions get a custom plan built for the actual parameter values. After that the backend also builds a generic plan (parameters as $1) and keeps using it if its estimated cost is lower than the average custom plan cost (which includes an estimate of the planning overhead). plan_cache_mode = auto | force_custom_plan | force_generic_plan overrides that. A generic plan that ignores skewed values is a classic "fast in psql, slow from the app" ticket.

PREPARE q(int) AS SELECT * FROM orders WHERE customer_id = $1;
EXECUTE q(42);   -- x6 or more
SELECT name, generic_plans, custom_plans FROM pg_prepared_statements;
EXPLAIN (GENERIC_PLAN) SELECT * FROM orders WHERE customer_id = $1;  -- PG16+

Reading EXPLAIN ANALYZE (PostgreSQL 18)

Hash Join  (cost=2188.88..40461.23 rows=50070 width=15) (actual time=20.943..247.074 rows=50000.00 loops=1)
  Hash Cond: (orders.customer_id = c.id)
  Buffers: shared read=13367
  ->  Seq Scan on orders  (cost=0.00..37739.00 rows=203173 width=10) ...
        Filter: (amount > '90'::numeric)
        Rows Removed by Filter: 1802000

• cost=startup..total in arbitrary units (one sequential page read = 1.0); rows is the estimate. Compare it with actual rows: a 10× or worse misestimate is the usual root cause of a bad plan.
• actual time and rows are per loop; multiply by loops. Since PostgreSQL 18 rows are printed with two decimals, so averages such as rows=0.50 are no longer rounded to 0.
• PostgreSQL 18 includes Buffers: automatically in EXPLAIN ANALYZE and reports Index Searches for index scans.
• Use EXPLAIN (ANALYZE, BUFFERS, SETTINGS, WAL) for tickets; wrap data-changing statements in BEGIN … ROLLBACK because ANALYZE really executes them.

To catch slow plans in production use log_min_duration_statement and the auto_explain module (auto_explain.log_min_duration, auto_explain.log_analyze).