Planner

A query planner chooses an execution path using estimated work. It does not run every candidate, time them, and keep the winner. PostgreSQL compares possible scans, joins, sorts, and other operations using statistics and a cost model. The chosen plan is therefore a prediction under current assumptions. Understanding a wrong prediction requires identifying which assumption failed, rather than treating a sequential scan or a nested loop as inherently suspicious. ...

A join strategy connects two input streams; it is not a permanent property of the SQL text. Restricting one side to three customers can make indexed repeated lookups attractive, while joining every purchase may favor scanning once and building a hash table. PostgreSQL chooses among legal alternatives using estimated work, available access paths, ordering, and memory expectations. Table size matters, but the size after filtering matters more than a table’s name or reputation. ...

A partial index can keep a frequently queried subset of rows in a smaller structure. An index on job id WHERE status is open is a useful candidate when open jobs are rare and most requests target them. Its availability depends on what the planner can prove about each query, not merely on whether the application happens to pass “open” during one execution. ...

EXPLAIN describes the plan PostgreSQL intends to execute. EXPLAIN ANALYZE executes that plan and adds measurements. Comparing them is useful only after interpreting what each number counts. An apparent mismatch can reflect stale estimates, repeated execution, or a parent that stopped requesting rows. Matching cardinalities strengthen one part of the model; they do not prove the chosen plan is the fastest possible plan. ...

ANALYZE updates the statistical model PostgreSQL uses to estimate query results. It does not rewrite the query, rebuild its indexes, remove dead tuples, or promise a faster plan. Its useful question is specific: does the planner understand the current distribution well enough to compare access paths? A bulk status change can invalidate that understanding even when the table still contains exactly the same number of live rows. ...