How the Cost-Based Optimizer Chooses a Plan

The one thing to understand first

For any non-trivial query there are many ways to get the answer — different scan methods, join orders, and join algorithms. The planner/optimizer (src/backend/optimizer/) enumerates candidate paths, estimates the cost of each, and picks the cheapest. It does not try every possibility exhaustively; it prunes aggressively.

The optimizer is a pure function: selectivity in, cost out. It almost always picks the right plan given accurate row counts, so plan debugging is not about outsmarting the planner — it is about finding the one row estimate that is wrong.

Paths and the cheapest-path principle

For each relation the planner builds Path objects — one per viable access method (seq scan, each usable index, bitmap). Paths carry a cost and useful properties such as <a class="sev1-termlink" href="https://thesev1database.com/glossary/pathkeys/" title="Pathkeys">sort order. At each level the planner keeps only the cheapest path for each distinct useful property (e.g. cheapest unordered, cheapest sorted by X). This pruning is what keeps planning tractable.