| Red Hat Database: Administrator and User's Guide | ||
|---|---|---|
| Prev | Chapter 9. Overview of PostgreSQL Architecture and Internals | Next |
The task of the planner/optimizer is to create an optimal execution plan. It first combines all possible ways of scanning and joining the relations that appear in a query. All the created paths lead to the same result; it is the task of the optimizer to estimate the cost of executing each path and find out which one is the cheapest.
The planner/optimizer decides which plans should be generated based upon the types of indexes defined on the relations appearing in a query. There is always the possibility of performing a sequential scan on a relation, so a plan using only sequential scans is always created.
After all feasible plans have been found for scanning single relations, plans for joining relations are created. The planner/optimizer considers only joins between every two relations for which there exists a corresponding join clause (that is, for which a restriction like where rel1.attr1=rel2.attr2 exists) in the where qualification. All possible plans are generated for every join pair considered by the planner/optimizer. The three possible join strategies are:
nested iteration join: The right relation is scanned once for every tuple found in the left relation. This strategy is easy to implement but can be very time consuming.
merge sort join: Each relation is sorted on the join attributes before the join starts. Then the two relations are merged together taking into account that both relations are ordered on the join attributes. This kind of join is more attractive because every relation has to be scanned only once.
hash join: the right relation is first hashed on its join attributes. Next the left relation is scanned and the appropriate values of every tuple found are used as hash keys to locate the tuples in the right relation.