= Database query optimization
<Database query optimization> chooses a <database query plan> for a <database query> by comparing ways to obtain the required result. The choice includes the order of <relational joins>, use of a <database index>, and the algorithms implementing those <relational joins>. A cost model estimates work from quantities such as input sizes and storage accesses. The selected plan minimizes estimated cost among considered alternatives; this need not be the fastest possible plan in reality.<Planner and Optimizer in the PostgreSQL documentation>[Section 51.5, opening paragraphs and Generating Possible Plans]{ref}
Consider a <relational join> of one million orders with customers, followed by a condition selecting ten customers. Applying the customer condition first can greatly shrink a join input. If a suitable <database index> locates those customers' orders, the engine may avoid scanning all orders. If almost all customers qualify, a sequential scan can instead be cheaper than many scattered index accesses. These are alternative ways of computing the same answer, not different meanings of the query.
Join order also matters with three or more inputs: an early join that creates a huge intermediate result can dominate the total work. Exhaustive search itself becomes expensive as the number of inputs grows. PostgreSQL therefore switches to a heuristic search above a configurable threshold. Inaccurate size estimates can favor a poor plan even when the search algorithm correctly minimizes its cost model.<Planner and Optimizer in the PostgreSQL documentation>[Section 51.5.1, scan alternatives, join sequences and heuristic search]{ref}
Back to article page
