Mastering PostgreSQL 9.6

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 us take a look at the following query, which shows 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 ...