Mastering PostgreSQL 11 - Second Edition

Speedup set operations

Set operations allow the results of multiple queries to be combined into a single result set. Set operators 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 ...