One of the hash improvements in PostgreSQL 8.4 is introducing Hash Semi and anti-joins. A semi join is used when the optimizer needs to confirm that a key value exists on one side of the join, but doesn't particularly care what that value is beyond that. The opposite, an anti join, looks specifically for entries where the key value doesn't exist. The most common way to see these two types of join is when executing EXISTS and NOT EXISTS. The inverse of the query in the previous section is finding all the products that have never been ordered by any customer; it executes most efficiently with an anti-join:
EXPLAIN ANALYZE SELECT prod_id,title FROM products p WHERE NOT EXISTS (SELECT 1 FROM orderlines ol WHERE ol.prod_id=p.prod_id); ...
