SetOp

Description

Implements the selected INTERSECT or EXCEPT operation using a sorted or hashed strategy.

Core node tag

T_SetOp

Structured EXPLAIN Node Type

SetOp

Inputs

Set-operation input plans

Output

Set-operation result tuples

Executor initializer

ExecInitSetOp

Memory mechanism

unclassified

EXPLAIN strategies

Sorted, Hashed

EXPLAIN names and attributes

Structured formats use the Node Type above. Text-format spellings can also include operation, strategy, join type, scan direction or aggregation-stage attributes.

Text names recorded by this source: SetOp, HashSetOp.

Parallel-aware and parallel-safe are different plan properties. A node running inside a parallel worker is not necessarily a parallel-aware node.

Memory and temporary storage

This extraction does not assign a universal memory limit or spill policy to this node. Inspect the same-build implementation and its expressions or provider.

Parallel execution and instrumentation

The source callbacks below can coordinate execution or collect worker instrumentation. Their presence is not a blanket claim that this node supports a shared parallel scan or shared state.

Callbacks in this build: none extracted from this node implementation.

Executor implementation notes

nodeSetOp.c Routines to handle INTERSECT and EXCEPT selection

The input of a SetOp node consists of two relations (outer and inner) with identical column sets. In EXCEPT queries the outer relation is always the left side, while in INTERSECT cases the planner tries to make the outer relation be the smaller of the two inputs.

In SETOP_SORTED mode, each input has been sorted according to all the grouping columns. The SetOp node essentially performs a merge join on the grouping columns, except that it is only interested in counting how many tuples from each input match. Then it is a simple matter to emit the output demanded by the SQL spec for INTERSECT, INTERSECT ALL, EXCEPT, or EXCEPT ALL.

In SETOP_HASHED mode, the inputs are delivered in no particular order. We read the outer relation and build a hash table in memory with one entry for each group of identical tuples, counting the number of tuples in the group. Then we read the inner relation and count the number of tuples matching each outer group. (We can disregard any tuples appearing only in the inner relation, since they cannot result in any output.) After seeing all the input, we scan the hashtable and generate the correct output using those counts.

This node type is not used for UNION or UNION ALL, since those can be implemented more cheaply (there's no need to count the number of matching tuples).

EXPLAIN identity in core source

case T_SetOp:
			sname = "SetOp";
			switch (((SetOp *) plan)->strategy)
			{
				case SETOP_SORTED:
					pname = "SetOp";
					strategy = "Sorted";
					break;
				case SETOP_HASHED:
					pname = "HashSetOp";
					strategy = "Hashed";
					break;
				default:
					pname = "SetOp ???";
					strategy = "???";
					break;
			}
			break;

Related entries

• EXPLAIN
• Using EXPLAIN
• Parallel plans

Documentation and source

• src/backend/commands/explain.c:1580
• src/backend/executor/execProcnode.c:370
• src/backend/executor/nodeSetOp.c
• src/include/nodes/plannodes.h

Compare versions

PostgreSQL 17 → 18: unchanged.

Compares recorded interfaces and attributes. Source fingerprints and build metadata are excluded; an absent sample is not proof of the introduction or removal release.

Related entries

• AppendAppend
• Merge AppendMergeAppend
• Recursive UnionRecursiveUnion
• UniqueUnique