PostgreSQL Join Optimization: Nested Loop, Hash, and Merge

PostgreSQL has three join algorithms. The planner picks between them for every join in every query, driven by several things at once: the estimated sizes of the two inputs, whether they arrive already sorted on the join key, the type of join (inner vs left/semi/anti), which operators are mergejoinable or hashjoinable , whether a hash table will fit in work_mem , and the cost parameters that weigh I/O against CPU. Get the decision right and a three-way join across millions of rows runs in tens of