PostgreSQL 10 High Performance

Subquery conversion and IN lists

A few types of things that you may expect to be executed as subqueries will actually turn into types of joins instead. This happens when using a Subquery to find a list of rows then used for IN:

EXPLAIN ANALYZE SELECT * FROM orders WHERE customerid IN (SELECT customerid FROM customers where state='MD');
QUERY PLAN
----------
Hash Semi Join  (cost=728.34..982.61 rows=249 width=36) (actual time=11.521..55.139 rows=120 loops=1)
       Hash Cond: (orders.customerid = customers.customerid)
       ->  Seq Scan on orders  (cost=0.00..220.00 rows=12000 width=36) (actual time=0.009..20.496 rows=12000 loops=1)
       ->  Hash  (cost=726.00..726.00 rows=187 width=4) (actual time=11.437..11.437 rows=187 loops=1)
     Buckets: 1024 Batches: 1 Memory ...