Database Reference
In-Depth Information
Hash semi and anti joins
A semi join can be exercised when the optimizer has to make sure that a key value
exists on one side of the join. On the other hand, an anti join looks particularly for
entries where the key value doesn't exist.
Usually, these two types of joins are used when
executing the
EXISTS
and
NOT EXISTS
expressions.
Let's use the
EXISTS
and
NOT EXISTS
expressions in our queries and then
generate the plan accordingly. Generate a query using the
EXISTS
expression
in the following statement:
warehouse_db=# EXPLAIN SELECT * FROM record.warehouse_tbl WHERE
EXISTS (SELECT 1 FROM record.history where
warehouse_tbl.warehouse_id = history.warehouse_id);
QUERY PLAN
------------------------------------------------------------------
Nested Loop Semi Join (cost=0.00..1934581.90 rows=4 width=245)
Join Filter: (warehouse_tbl.warehouse_id = history.warehouse_id)
-> Seq Scan on warehouse_tbl (cost=0.00..1.04 rows=4
width=245)
-> Seq Scan on history (cost=0.00..1934580.00
rows=100000000 width=4)
Planning time: 0.255 ms
(5 rows)
Generate a query using the
NOT EXISTS
expression in the following statement:
warehouse_db=# EXPLAIN SELECT * FROM record.warehouse_tbl WHERE
NOT EXISTS (SELECT 1 FROM record.history where
warehouse_tbl.warehouse_id = history.warehouse_id);
QUERY PLAN
------------------------------------------------------------------
Nested Loop Anti Join (cost=0.00..1934581.90 rows=1 width=245)
Join Filter: (warehouse_tbl.warehouse_id = history.warehouse_id)
-> Seq Scan on warehouse_tbl (cost=0.00..1.04 rows=4
width=245)
-> Seq Scan on history (cost=0.00..1934580.00
rows=100000000 width=4)
Planning time: 0.233 ms
(5 rows)
