Planner

For a=7 and b=7, actual output is 100 rows. Independence predicts one row, while collected dependency statistics predict 100. For a=7 and b=8, the dependency still predicts 100 while actual output is zero.

Query planners bet on cost, not certainty

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. ...

The selective source plan repeats a 100-row inner bitmap path three times. The broad plan builds a hash from 500 customers and probes with 50000 purchases.

Joins pick a strategy from the sizes both sides bring

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. ...

Unknown status in a generic plan cannot prove open-only membership. A custom plan knowing open, or a generic query with literal open, can consider the partial index. Eligibility does not guarantee selection.

A partial index stores only the rows that matter

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. ...

Three loops each supply two rows through the inner Limit, producing six joined rows. Row output is per loop; a LIMIT can also stop a child before full output is consumed. Article observations are illustrated, not rerun.

EXPLAIN estimates need execution context

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 estimates the planner relies on

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. ...