Mastering PostgreSQL 12 - Third Edition

Speedup set operations

Set operations combine the results of more than one query into a single result set. They include UNION, INTERSECT, and EXCEPT. PostgreSQL implements all of them and offers many important optimizations to speed them up.

The planner is able to push restrictions down into the set operation, opening the door for fancy indexing and speedups in general. Let's take a look at the following query, which shows us how this works:

test=# EXPLAIN SELECT * FROM (   SELECT aid AS xid    FROM a    UNION ALL    SELECT bid FROM b) AS y 
WHERE xid = 3; 
                           QUERY PLAN ----------------------------------------------------------------  Append (cost=0.29..12.89 rows=2 width=4)    -> Index Only Scan using idx_a on a          (cost=0.29..8.30 rows=1 width=4)  Index ...