One query admits different execution paths
SQL usually describes a result without fixing every access step. A database can scan a table sequentially or locate candidates through an index, and joins admit different orders and algorithms.
PostgreSQL compares compact representations called paths before building a complete plan tree. Scans and joins can be combined with sorting, aggregation, filtering, and expression evaluation. Each operation must remain correctly placed within the query’s semantics.
References: [1]
Estimated cost is not elapsed time
Row counts, distributions, and access costs influence the comparison. Sequential access may be preferable for many qualifying rows, while an index can suit a selective lookup.
EXPLAIN cost values use planner-defined units rather than promising milliseconds. Its rows field generally estimates a node’s output, not every inspected row. Interpreting those measures correctly helps locate mismatches between estimates and execution.
Correlated columns can distort estimates. If A determines B in a dataset, multiplying their selectivities as independent conditions can underestimate matching rows. PostgreSQL extended statistics collect dependency information for explicitly selected column groups through ANALYZE. Dependency statistics have limits, including application to simple column–constant equality and constant IN conditions; they do not automatically repair arbitrary range predicates or every join estimate.
Planning has its own cost
Enumerating every join order can itself consume excessive time and memory. PostgreSQL uses heuristic search, including its genetic query optimizer beyond a threshold, to bound that work.
The result is a practical plan favored by the model rather than a universal proof of minimum runtime. Updated statistics and actual execution evidence remain necessary; observing an index scan alone does not establish low total cost.