Given a query, PostgreSQL will first generate all possible query plans. A query can be executed in multiple ways (hence multiple plans).
The final plan selected is the one with the least cost. Each plan has a cost associated with it.
Cost is generally computed based on the following factors:
- Number of disk scans
- Number of index scans
- Number of rows processed
Generating plans for Joins
JOINs are also evaluated by the planner if they exist in the query. There are 3 types of JOINs:
- nested loop join
- merge join
- hash join
The selection of a join depends on “table size”, “join condition” and column value distribution (cardinality).