Working with hash and merge join
In this recipe, we will be discussing merge and hash join mechanisms in PostgreSQL.
Getting ready
Merge join is another joining approach to perform the join operation between two datasets. PostgreSQL optimizer will generally choose this joining method for an equi joins or for union operations. To perform this join on two datasets, it is required to sort the two join key columns first and then it will run the join condition. The optimizer prefers this node type, while joining huge tables.
Hash join is another joining approach. In general, this approach is pretty fast if and only if the server has enough memory resources. To perform this join, PostgreSQL does not need any sorted results. Rather, it will take one table data to build a hash index, which it will be comparing with the other table tuples. PostgreSQL optimizer will generally choose this joining method for an equi joins or for union operations. The optimizer prefers this node type, while joining the...
