In this lesson, you will learn how to select the most efficient set operator to minimize expensive sort overhead during result set combination.
When you use set operations like UNION, INTERSECT, or EXCEPT in PostgreSQL, the database must ensure the resulting rows are distinct. By default, PostgreSQL performs a UNIQUE operation, which almost always triggers an explicit Sort node in the query plan. If you are dealing with millions of rows, that sort operation consumes significant memory and CPU.
Prefer UNION ALL over UNION
The UNION operator forces a deduplication step after the sets are combined, requiring a sort or a hash table. However, if your business logic allows for duplicate rows, or if you can guarantee that the inputs are already disjoint (mutually exclusive), you should always use UNION ALL. Because UNION ALL performs a simple concatenation of the results without checking for duplicates, it completely skips the sort step.
Leveraging Index Ordering
When you cannot avoid UNION or INTERSECT (because you must have unique results), you can optimize performance by ensuring the input sets are already sorted. PostgreSQL’s planner can execute a MergeAppend node if the underlying queries providing the sets are already ordered by the columns involved in the set operation.
Consider this comparison:
-- Potentially slow: forces a global sort to find unique rows
SELECT val FROM table_a UNION SELECT val FROM table_b;
-- Efficient: bypasses sorting if you know uniqueness isn't required
SELECT val FROM table_a UNION ALL SELECT val FROM table_b;
If you must perform an intersection, try to rewrite the query as a JOIN using EXISTS or an inner join. Joins are often better optimized by the planner’s cost model than the overhead of a standard INTERSECT operation, especially when indexes exist on the join keys, allowing the database to use Merge Join or Hash Join instead of a heavy-duty sort-based set intersection.
Key Takeaway: Always use UNION ALL unless you explicitly require deduplication, and favor joins over INTERSECT/EXCEPT to leverage existing indexes and avoid redundant sort operations.