Database Reference
In-Depth Information
-> Seq Scan on warehouse_tbl (cost=0.00..1.04 rows=4
width=245)
Planning time: 0.158 ms
(5 rows)
Merge joins
A merge join or a sort-merge join works on the principle that before the join starts,
each relation is irst sorted on the join attributes. Scanning is performed in parallel
and matching rows are thus combined. Implementation can become complex with
duplicate values, so if the left one has duplicate values, then the right table can be
rescanned more than once.
Merge joins are observed when joining on the basis of
equality and not on range or inequality.
Hash joins
Unlike a merge join, a hash join doesn't sort its input. Rather, it irst creates a hash
table from each row of the right table using its join attributes as
hash keys
and then
scans the left table to ind the corresponding matching rows.
It will be more evident from the query plan of the hash join on the same tables
we used earlier for the nested loop join. So, let's take a look at the following hash
join example:
warehouse_db=# EXPLAIN SELECT * FROM record.warehouse_tbl,
record.history WHERE warehouse_tbl.warehouse_id =
history.warehouse_id;
QUERY PLAN
-----------------------------------------------------------------
Hash Join (cost=1.09..3309581.09 rows=100000000 width=291)
Hash Cond: (history.warehouse_id = warehouse_tbl.warehouse_id)
-> Seq Scan on history (cost=0.00..1934580.00 rows=100000000
width=46)
-> Hash (cost=1.04..1.04 rows=4 width=245)
-> Seq Scan on warehouse_tbl (cost=0.00..1.04 rows=4
width=245)
Planning time: 0.265 ms
(6 rows)
You can create an intermediate hash table from a small table.
