Subquery Scan and Subplan

These two types of nodes are used for shuffling rows around between nodes in some types of UNION and subselect queries. They have little performance impact and have also become rare in current PostgreSQL versions due to the HashAggregate optimizations.

Subquery conversion and IN lists

A few types of things that you might 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...