SetOp

A SetOp node computes INTERSECT/EXCEPT (and their ALL forms) by counting matching rows from the two branches. It comes in a sorted variant (SetOp) and a hashed variant (HashSetOp).

For INTERSECT / EXCEPT queries. (UNION is handled differently — by Append plus a deduplicating step.)

Normal for set operations. As with aggregation, the hashed variant can spill if the working set is large.

INTERSECT and EXCEPT internals

SetOp implements the INTERSECT and EXCEPT operators (and their ALL variants) by counting how many of each row appear in each branch and emitting the right number based on the operator. It comes in two strategies:

• SetOp — sorted strategy. Both branches arrive sorted on the comparison columns and SetOp walks them in lockstep.
• HashSetOp — hash strategy. SetOp builds a hash table keyed on the comparison columns and counts occurrences.

PostgreSQL picks whichever it estimates is cheaper, similarly to DISTINCT and GROUP BY.

UNION is not a SetOp

UNION (without ALL) is implemented as Append + a deduplication step (a Sort + Unique or HashAggregate), not as SetOp. So you'll see SetOp only for INTERSECT/EXCEPT.

Tuning notes

The cost is dominated by what feeds the two branches. A sorted SetOp sometimes spends most of its time in the Sort nodes; a HashSetOp's memory budget is the usual work_mem × hash_mem_multiplier story — if the hash spills to disk, raising work_mem for the query restores it.

If you're using INTERSECT/EXCEPT for uniqueness rather than for set semantics (e.g. as a clever way to filter), an EXISTS / NOT EXISTS subquery is often clearer and lets the planner pick a more conventional join shape.

• Append
• Aggregate
• Unique