Subplans, CTEs and the Rest of the Executor Nodes

Everything that is not a scan, a join, a sort or an aggregate — collected in one chapter so you can look it up.

The scans of §18.2 read tables, the joins of §18.4 combine them, the aggregates of §18.3 summarise them — and yet almost no real query is made of only those. Between them sits a third population of nodes: the ones that let a plan refer to another plan, buffer a result so it can be read twice, iterate until a fixed point, concatenate branches, stop early, remove duplicates, or write rows back. This section is that catalogue. Two of its members are genuinely deep — the subplan/parameter machinery and the recursive-CTE loop — and they get most of the room; the rest are small nodes whose whole implementation fits on a page but whose absence would break SQL. Throughout, remember the chapter’s refrain: which of these nodes you see depends on which optimizer planned the query, and several of them behave differently in Cloudberry than in upstream PostgreSQL because a subplan here may have to be dispatched (§13.3) before it can be evaluated.

18.6.1 Three ways to run a subquery

A PlannedStmt carries a main planTree and a list of subplans. The main tree references a subplan not by a child pointer but through a parameter: a slot in EState.es_param_exec_vals. nodeSubplan.c (1582 lines) implements three quite different ways of filling that slot, and people conflate them constantly:

InitPlan — The subquery is uncorrelated — nothing inside it depends on the outer query. It is therefore evaluated once, its result stored into a PARAM_EXEC slot by ExecSetParamPlan (nodeSubplan.c:1133), and the main plan just reads $0 like a constant. EXPLAIN labels it InitPlan N (returns $0).

SubPlan — The subquery is correlated: it has parParam values pushed in from the outer tuple. ExecScanSubPlan (nodeSubplan.c:235) sets those parameters, calls ExecReScan on the subplan (:283), and runs it again for every outer tuple. EXPLAIN labels it SubPlan N, and the giveaway is a loops=N greater than 1 on the nodes underneath it.

hashed SubPlan — An IN/NOT IN (ANY_SUBLINK) whose right-hand side is uncorrelated. buildSubPlanHash (nodeSubplan.c:523) drains the subquery once into a TupleHashTable built with §18.5’s execGrouping.c machinery, and ExecHashSubPlan (:113) turns each test into a hash probe. EXPLAIN writes hashed SubPlan N inside the qual.

An uncorrelated aggregate subquery becomes an InitPlan (VERBOSE Output lines trimmed)

SET optimizer = off;
EXPLAIN (ANALYZE, VERBOSE, TIMING OFF, COSTS OFF)
  SELECT count(*) FROM m186_t WHERE val > (SELECT avg(val) FROM m186_t);

 Finalize Aggregate (actual rows=1 loops=1)
   InitPlan 1 (returns $0)  (slice2)
     ->  Finalize Aggregate (actual rows=1 loops=1)
           ->  Gather Motion 3:1  (slice3; segments: 3) (actual rows=3 loops=1)
                 ->  Partial Aggregate (actual rows=1 loops=1)
                       ->  Seq Scan on public.m186_t m186_t_1 (actual rows=66711 loops=1)
   ->  Gather Motion 3:1  (slice1; segments: 3) (actual rows=3 loops=1)
         ->  Partial Aggregate (actual rows=1 loops=1)
               ->  Seq Scan on public.m186_t (actual rows=33451 loops=1)
                     Filter: ((m186_t.val)::numeric > $0)
                     Rows Removed by Filter: 33260
 Settings: optimizer = 'off'
   (slice0)    Executor memory: 115K bytes.
   (slice1)    Executor memory: 120K bytes avg x 3x(0) workers, 120K bytes max (seg0).
   (slice2)    Executor memory: 59K bytes.
 ...
 Optimizer: Postgres query optimizer
 Execution Time: 31.517 ms

That (slice2) next to InitPlan 1 is the Cloudberry part, and it is not decoration. Upstream PostgreSQL evaluates an InitPlan lazily, the first time the parameter is read: ExecEvalParamExec (execExprInterp.c:2547) notices prm->execPlan != NULL and calls ExecSetParamPlan right there, in the middle of expression evaluation. Cloudberry cannot afford that, because an InitPlan of its own may span several slices and needs a gang and an interconnect. So ExecSetParamPlan was rewritten to dispatch:

src/backend/executor/nodeSubplan.c:1152

	if (Gp_role == GP_ROLE_DISPATCH && planstate != NULL &&
		planstate->plan != NULL && queryDesc)
	{
		int  subsliceIndex = queryDesc->plannedstmt->subplan_sliceIds[subplan->plan_id - 1];
		ExecSlice *subslice = &estate->es_sliceTable->slices[subsliceIndex];

		if (subslice->gangType != GANGTYPE_UNALLOCATED || subslice->children)
			shouldDispatch = true;
	}
	...
	if (shouldDispatch)
	{
		CdbDispatchPlan(queryDesc, estate->es_param_exec_vals, needDtx, true);
		CurrentMotionIPCLayer->SetupInterconnect(queryDesc->estate);   /* for the initplan's root slice */
	}

So an InitPlan in Cloudberry is a small query of its own: its own slice (subplan_sliceIds[], plannodes.h:125), its own gang, its own interconnect setup and teardown, its own line in the memory footer. And because it must complete before the main plan is dispatched, it is run eagerly: ExecutorRun calls preprocess_initplans(queryDesc) (execMain.c:680) — implemented in cdbsubplan.c:61 — before it dispatches the main plan, looping over es_param_exec_vals and firing every InitPlan whose slice is a root slice. Nested InitPlans work because the growing set of already-computed PARAM_EXEC values is re-serialised into each dispatch. This is one of the clearest places where an upstream lazy mechanism had to become an eager one to survive dispatch (§13.3).

A correlated scalar subquery becomes a SubPlan — and loops=5 is the whole story

SET optimizer = off;
EXPLAIN (ANALYZE, VERBOSE, TIMING OFF, COSTS OFF)
  SELECT d.grp, (SELECT count(*) FROM m186_t t WHERE t.grp = d.grp) FROM m186_dim d;

 Gather Motion 3:1  (slice1; segments: 3) (actual rows=10 loops=1)
   Output: d.grp, ((SubPlan 1))
   ->  Seq Scan on public.m186_dim d (actual rows=5 loops=1)
         Output: d.grp, (SubPlan 1)
         SubPlan 1
           ->  Aggregate (actual rows=1 loops=5)
                 ->  Result (actual rows=20000 loops=5)
                       Filter: (t.grp = d.grp)
                       ->  Materialize (actual rows=200000 loops=5)
                             work_mem: 34270kB  Segments: 3  Max: 11424kB (segment 0)  Workfile: (0 spilling)
                             ->  Broadcast Motion 3:3  (slice2; segments: 3) (actual rows=200000 loops=1)
                                   ->  Seq Scan on public.m186_t t (actual rows=66711 loops=1)
 ...
 Optimizer: Postgres query optimizer
 Execution Time: 445.614 ms

Read the loops column downward: the Aggregate and the Result ran 5 times (once per m186_dim row on this segment), but the Broadcast Motion beneath them ran once. That difference is entirely due to the Materialize sitting between them — §18.6.2’s subject. Without it, every outer tuple would re-open a motion, and re-running a motion 5 times is not merely slow, it is the kind of thing that deadlocks an interconnect. Note also that per-node rows are per-loop averages: Result reports 20 000 with loops=5, i.e. 100 000 tuples pulled from the tuplestore in total, all so that a count(*) could be filtered down to one row each time. A correlated subplan is the executor doing exactly what the SQL text asked and nothing more.

An IN-subquery that cannot be pulled up becomes a hashed SubPlan: built once, probed 66 711 times

SET optimizer = off;
EXPLAIN (ANALYZE, VERBOSE, TIMING OFF, COSTS OFF)
  SELECT count(*) FROM m186_t t
   WHERE t.val < 0 OR t.grp IN (SELECT grp FROM m186_dim WHERE label <> 'g3');

 Finalize Aggregate (actual rows=1 loops=1)
   ->  Gather Motion 3:1  (slice1; segments: 3) (actual rows=3 loops=1)
         ->  Partial Aggregate (actual rows=1 loops=1)
               ->  Seq Scan on public.m186_t t (actual rows=60006 loops=1)
                     Filter: ((t.val < 0) OR (hashed SubPlan 1))
                     Rows Removed by Filter: 6647
                     SubPlan 1
                       ->  Broadcast Motion 3:3  (slice2; segments: 3) (actual rows=9 loops=1)
                             ->  Seq Scan on public.m186_dim (actual rows=4 loops=1)
                                   Filter: (m186_dim.label <> 'g3'::text)
 ...
 Optimizer: Postgres query optimizer
 Execution Time: 31.633 ms

The OR is what keeps the subquery from being flattened into a semi-join; the planner’s consolation prize is to hash it. loops=1 on the motion proves the subquery ran once. What buildSubPlanHash builds is actually two hash tables: node->hashtable for rows with no NULLs and node->hashnulls for rows containing one (nodeSubplan.c:561 and :589). That is not tidiness, it is IN’s three-valued logic: a probe that misses hashtable must still ask findPartialMatch (:763) whether some row could have matched had a NULL been a value, in which case the answer is NULL rather than false. NOT IN over a nullable column is slow and surprising for exactly this reason, and here is the code that makes it so.

The same two queries under ORCA: no InitPlan, no SubPlan — the subqueries were decorrelated into joins

SET optimizer = on;
EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF)
  SELECT count(*) FROM m186_t WHERE val > (SELECT avg(val) FROM m186_t);
EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF)
  SELECT d.grp, (SELECT count(*) FROM m186_t t WHERE t.grp = d.grp) FROM m186_dim d;

 Finalize Aggregate (actual rows=1 loops=1)
   ->  Gather Motion 3:1  (slice1; segments: 3) (actual rows=3 loops=1)
         ->  Partial Aggregate (actual rows=1 loops=1)
               ->  Nested Loop (actual rows=33451 loops=1)
                     Join Filter: ((m186_t.val)::numeric > (avg(m186_t_1.val)))
                     ->  Broadcast Motion 1:3  (slice2) (actual rows=1 loops=1)
                           ->  Finalize Aggregate (actual rows=1 loops=1)
                                 ->  Gather Motion 3:1  (slice3; segments: 3) (actual rows=3 loops=1)
                                       ->  Partial Aggregate (actual rows=1 loops=1)
                                             ->  Seq Scan on m186_t m186_t_1 (actual rows=66711 loops=1)
                     ->  Seq Scan on m186_t (actual rows=33356 loops=2)
 Optimizer: GPORCA

 Gather Motion 3:1  (slice1; segments: 3) (actual rows=10 loops=1)
   ->  Hash Left Join (actual rows=5 loops=1)
         Hash Cond: (d.grp = t.grp)
         ->  Seq Scan on m186_dim d (actual rows=5 loops=1)
         ->  Hash (actual rows=5 loops=1)
               ->  Finalize HashAggregate (actual rows=5 loops=1)
                     Group Key: t.grp
                     ->  Redistribute Motion 3:3  (slice2; segments: 3) (actual rows=15 loops=1)
                           ->  Streaming Partial HashAggregate (actual rows=10 loops=1)
                                 ->  Seq Scan on m186_t t (actual rows=66711 loops=1)
 Optimizer: GPORCA
 Execution Time: 29.462 ms

This is §17.3/§17.5 made visible: ORCA’s preferred move is to decorrelate — the per-tuple SubPlan becomes a one-pass Hash Left Join over a pre-aggregated inner side, 445 ms down to 29 ms on this machine (a ratio on one assert-enabled build, not a benchmark). It is not that ORCA cannot emit subplans: the OR-guarded IN above still comes out as hashed SubPlan 1 under ORCA too, wrapped in its own Materialize. The lesson is the one this chapter keeps repeating — before you tune a SubPlan, check the Optimizer: footer, because the other optimizer may not produce that node at all.

18.6.2 Materialize: buffering a subtree

nodeMaterial.c (548 lines) is the executor’s answer to “I am going to need to read this more than once.” It pulls tuples from its child, stashes each one in a tuplestore on the way past, and returns it; on a rescan it replays the tuplestore instead of re-executing the child. The planner inserts it on a nested loop’s inner side (§18.4), under a correlated subplan as we just saw, and wherever mark/restore is needed for a merge join. It is also the node that makes ExecMaterialMarkPos (:385) possible.

Squeeze the operator memory and the tuplestore spills — on all three segments

SET optimizer = off;
SET statement_mem = '2MB';
EXPLAIN (ANALYZE, VERBOSE, TIMING OFF, COSTS OFF)
  SELECT d.grp, (SELECT count(*) FROM m186_t t WHERE t.grp = d.grp) FROM m186_dim d;

         SubPlan 1
           ->  Aggregate (actual rows=1 loops=5)
                 ->  Result (actual rows=20000 loops=5)
                       Filter: (t.grp = d.grp)
                       ->  Materialize (actual rows=200000 loops=5)
                             work_mem: 4344kB  Segments: 3  Max: 1448kB (segment 0)  Workfile: (3 spilling)
                             Work_mem wanted: 9582K bytes avg, 9582K bytes max (seg0) to lessen workfile I/O affecting 3 workers.
                             ->  Broadcast Motion 3:3  (slice2; segments: 3) (actual rows=200000 loops=1)
 ...
 * (slice1)    Executor memory: 1103K bytes avg x 3x(0) workers, 1103K bytes max (seg0).  Work_mem: 1448K bytes max, 9582K bytes wanted.
 Memory used:  2048kB
 Memory wanted:  10181kB
 Execution Time: 690.646 ms

Two things to know about reading this. First, Cloudberry does not print upstream’s Storage: Memory / Maximum Storage: NkB lines for Material — they were removed; what you get instead is the per-segment work_mem: … Workfile: (N spilling) line, plus the starred slice in the footer (§18.1 owns those semantics). Second, the budget here is not work_mem directly: ExecMaterial creates its tuplestore with PlanStateOperatorMemKB(&node->ss.ps) (nodeMaterial.c:73), the planner’s per-operator slice of statement_mem, which is why lowering work_mem alone did not make it spill and lowering statement_mem did.

src/backend/executor/nodeMaterial.c:101

		/*
		 * MPP: If requested, fetch all rows from subplan and put them
		 * in the tuplestore.  This decouples a middle slice's receiving
		 * and sending Motion operators to neutralize a deadlock hazard.
		 */
		if (((Material *) node->ss.ps.plan)->cdb_strict)
		{
			for (;;)
			{
				TupleTableSlot *outerslot = ExecProcNode(outerPlanState(node));
				if (TupIsNull(outerslot))
					break;
				tuplestore_puttupleslot(tuplestorestate, outerslot);
			}
			node->eof_underlying = true;

That flag is Cloudberry’s own. A Material with cdb_strict set does not lazily buffer — it drains its entire child up front, before returning even one tuple. The planner sets it at createplan.c:5571 and :5848 for the case where a slice both receives from one Motion and sends to another, and could otherwise sit half-read while a peer waits (§18.4 describes the sibling mechanism, inner prefetch; Chapter 19 owns why an unread Motion blocks). Materialize is thus not only an optimisation in Cloudberry: sometimes it is a correctness device.

18.6.3 Recursive CTEs: iteration built from two tuplestores

WITH RECURSIVE is the only genuine loop in SQL, and nodeRecursiveunion.c (371 lines) implements it with two tuplestores and a swap. The non-recursive term is evaluated once into the working table. Then the recursive term runs, reading the working table through a WorkTableScan (nodeWorktablescan.c, 229 lines) and writing whatever it produces into the intermediate table. When the recursive term returns EOF, the intermediate table becomes the new working table, a fresh empty intermediate table is created, and the recursive term is rescanned. When an iteration produces nothing, the loop ends.

src/backend/executor/nodeRecursiveunion.c:126

			/* done with old working table ... */
			tuplestore_end(node->working_table);

			/* intermediate table becomes working table */
			node->working_table = node->intermediate_table;
			for (int k = 1; k < node->refcount; k++)
				tuplestore_alloc_read_pointer(node->working_table, EXEC_FLAG_REWIND);

			/* create new empty intermediate table */
			node->intermediate_table = tuplestore_begin_heap(false, false, work_mem);
			node->intermediate_empty = true;

			/* reset the recursive term */
			innerPlan->chgParam = bms_add_member(innerPlan->chgParam, plan->wtParam);

Notice how the iteration is triggered: not by a loop counter but by poking wtParam into the recursive term’s chgParam, which makes §18.1’s ExecReScan machinery re-initialise the whole subtree on the next pull. The dedup check is the other half. For UNION ALL, numCols is 0 and every tuple is kept; for UNION, a TupleHashTable is built at init (:40) and each candidate goes through LookupTupleHashEntry first, so a cyclic graph terminates instead of spinning forever. Both the seed loop and the recursive loop do this check, which is why UNION costs you a hash table for the entire transitive closure.

WITH RECURSIVE forces ORCA to hand the query back — and the recursion lands entirely on the coordinator

SET optimizer = on;  SET optimizer_trace_fallback = on;
EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF) WITH RECURSIVE d(node,depth) AS (
    SELECT 1, 0
  UNION ALL
    SELECT e.dst, d.depth+1 FROM m186_edge e JOIN d ON e.src = d.node WHERE d.depth < 6)
SELECT count(*), max(depth) FROM d;

INFO:  GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL:  Falling back to Postgres-based planner because GPORCA does not support
         the following feature: WITH RECURSIVE

 Aggregate (actual rows=1 loops=1)
   ->  Recursive Union (actual rows=1093 loops=1)
         ->  Result (actual rows=1 loops=1)
         ->  Hash Join (actual rows=156 loops=7)
               Hash Cond: (d.node = e.src)
               ->  WorkTable Scan on d (actual rows=52 loops=7)
                     Filter: (depth < 6)
                     Rows Removed by Filter: 104
               ->  Hash (actual rows=40000 loops=1)
                     Buckets: 524288  Batches: 1  Memory Usage: 5659kB
                     ->  Gather Motion 3:1  (slice1; segments: 3) (actual rows=40000 loops=1)
                           ->  Seq Scan on m186_edge e (actual rows=13504 loops=1)
   (slice0)    Executor memory: 5777K bytes.  Work_mem: 5659K bytes max.
   (slice1)    Executor memory: 112K bytes avg x 3x(0) workers, 112K bytes max (seg0).
 Optimizer: Postgres query optimizer
 Execution Time: 19.874 ms

This is the §17.1 fallback confirmed live, and it has a consequence worth stating plainly: RecursiveUnion and WorkTableScan are planner-path-only nodes. No amount of ORCA tuning will change how a recursive query executes, because ORCA never planned it. The plan itself is worth decoding node by node, because the counters reconstruct the traversal exactly. The seed is node 1; every node in this tree has 3 children; the depth < 6 guard cuts the recursion off after six productive iterations.

iteration

working table — WorkTable Scan reads

intermediate table — RecursiveUnion writes

what happens

seed

—

1

non‑recursive term: SELECT 1, 0

1

1

3

2

3

9

3

9

27

4

27

81

5

81

243

6

243

729

7

729

0

all 729 rows have depth 6 and fail the filter → intermediate table empty → loop ends

total

1093 rows read, 364 pass the filter

1092 rows from the recursive term

⟦1 + 1092 = 1093 = Recursive Union actual rows⟧

Every number in the plan falls out of that table. WorkTable Scan reported rows=52 loops=7, and 52 × 7 = 364 — exactly the rows that pass depth < 6 (1+3+9+27+81+243). Its Rows Removed by Filter: 104 × 7 = 728, the 729 depth-6 rows modulo the per-loop rounding. Hash Join reported rows=156 loops=7, and 156 × 7 = 1092, the recursive term’s total output; add the seed and you get the Recursive Union’s 1093. This is the clearest demonstration in the chapter that EXPLAIN’s rows is a per-loop average, not a total (§18.1).

Now the MPP question the plan above answers only half of. Hash shows loops=1: the 40 000-row hash table over m186_edge was built once and survived all seven rescans, so the Gather Motion ran once — the executor did not re-pull the table through the interconnect on every iteration. But where does the loop itself run? Here, on the coordinator (slice0), because the seed SELECT 1 has General locus. That is not a fixed rule; Cloudberry decides it in the planner, by giving the worktable the locus of the non-recursive term:

src/backend/optimizer/util/pathnode.c:3589

	switch (ctelocus.locustype)
	{
		case CdbLocusType_Entry:
		case CdbLocusType_General:
		case CdbLocusType_OuterQuery:
			break;                                    /* loop stays where the seed is */
		case CdbLocusType_SingleQE:
			CdbPathLocus_MakeSingleQE(&ctelocus, numsegments);
			break;
		case CdbLocusType_SegmentGeneral:
			elog(ERROR, "worktable scan path can never have segmentgeneral locus.");
		default:
			CdbPathLocus_MakeStrewn(&ctelocus, numsegments, 0);   /* one loop per segment */
	}

Give the seed a distributed locus and the same recursion runs once per segment

SET optimizer = off;
EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF) WITH RECURSIVE d(node,depth) AS (
    SELECT src, 0 FROM m186_edge WHERE src = 1
  UNION ALL
    SELECT e.dst, d.depth+1 FROM m186_edge e JOIN d ON e.src = d.node WHERE d.depth < 6)
SELECT count(*) FROM d;

 Finalize Aggregate (actual rows=1 loops=1)
   ->  Gather Motion 3:1  (slice1; segments: 3) (actual rows=3 loops=1)
         ->  Partial Aggregate (actual rows=1 loops=1)
               ->  Recursive Union (actual rows=3279 loops=1)
                     ->  Seq Scan on m186_edge (actual rows=3 loops=1)
                           Filter: (src = 1)
                     ->  Hash Join (actual rows=468 loops=7)
                           Hash Cond: (d.node = e.src)
                           ->  WorkTable Scan on d (actual rows=156 loops=7)
                                 Filter: (depth < 6)
                           ->  Hash (actual rows=40000 loops=1)
                                 ->  Broadcast Motion 3:3  (slice2; segments: 3) (actual rows=40000 loops=1)
                                       ->  Seq Scan on m186_edge e (actual rows=13504 loops=1)
   (slice1)    Executor memory: 5783K bytes avg x 3x(0) workers, 5847K bytes max (seg1).  Work_mem: 5659K bytes max.
 Optimizer: Postgres query optimizer
 Execution Time: 45.214 ms

The Recursive Union moved from slice0 to slice1: three independent loops, one per segment, each with its own pair of tuplestores. Correctness is bought by the Broadcast Motion 3:3 — every segment gets a full copy of the edge table, so a traversal that starts on segment 1 can finish there without talking to anyone. Two consequences follow. It is parallel only to the extent that the seeds are spread out (here all three seed rows hashed to segment 1, so 3279 = 3 × 1093 rows were produced by one segment while the other two found an empty working table and stopped after one iteration). And the broadcast is per segment, so the inner side’s memory is multiplied by the segment count, not divided by it — the footer’s 5659K is per segment. Also worth knowing: upstream assumes a working table has exactly one reader, but Cloudberry can plan several WorkTableScans against one RecursiveUnion, so each gets a private tuplestore read pointer (execnodes.h:2263 explains why, nodeWorktablescan.c:58 and :145 do it) — that is what the refcount loop in the swap code was allocating.

18.6.4 Append and MergeAppend: many children, one stream

nodeAppend.c (1277 lines) is deceptively large for a node whose job is “return all of child 1, then all of child 2, …”. Most of it is choice: choose_next_subplan_locally (:565) for the ordinary case, choose_next_subplan_for_leader/_for_worker (:635, :722) when the Append is parallel-aware, and the async machinery (ExecAppendAsyncBegin, :92) for concurrent foreign scans. Append appears for UNION ALL, for an inheritance or partition hierarchy, and under SetOp. Its two interesting behaviours are that children are started lazily — a child never asked for shows (never executed) — and that it participates in partition pruning.

Plan-time pruning leaves two children; a generic plan prunes at run time instead and says so

SET optimizer = off;
-- constant predicate: the planner removes 2024 outright
EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF) SELECT count(*) FROM m186_part WHERE dt >= '2025-06-01';

-- a generic plan cannot know $1, so pruning is deferred to the executor
SET plan_cache_mode = force_generic_plan;
PREPARE q186(date) AS SELECT count(*) FROM m186_part WHERE dt >= $1;
EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF) EXECUTE q186('2026-01-01');

               ->  Append (actual rows=14563 loops=1)
                     ->  Seq Scan on m186_part_2025 m186_part_1 (actual rows=6463 loops=1)
                           Filter: (dt >= '2025-06-01'::date)
                           Rows Removed by Filter: 4481
                     ->  Seq Scan on m186_part_2026 m186_part_2 (actual rows=8100 loops=1)
                           Filter: (dt >= '2025-06-01'::date)
 Optimizer: Postgres query optimizer

               ->  Append (actual rows=8100 loops=1)
                     Subplans Removed: 2
                     ->  Seq Scan on m186_part_2026 m186_part_1 (actual rows=8100 loops=1)
                           Filter: (dt >= $1)
 Optimizer: Postgres query optimizer
 Execution Time: 5.625 ms

Subplans Removed: 2 is the executor’s own pruning at work. ExecInitAppend calls ExecInitPartitionPruning (execPartition.c:1872) with the plan’s PartitionPruneInfo; if the pruning steps depend only on external parameters they are evaluated once at init and the surviving children recorded in as_valid_subplans (nodeAppend.c:174), while steps depending on exec parameters — a nested loop’s outer value, an InitPlan’s $0 — are re-evaluated by ExecFindMatchingSubPlans (execPartition.c:2395) on every rescan.

MergeAppend keeps the children’s sort order by merging them through a binary heap

SET optimizer = off;
SET enable_seqscan = off;  SET enable_sort = off;
EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF) SELECT val, dt FROM m186_part ORDER BY val LIMIT 4;

 Limit (actual rows=4 loops=1)
   ->  Gather Motion 3:1  (slice1; segments: 3) (actual rows=4 loops=1)
         Merge Key: m186_part.val
         ->  Limit (actual rows=4 loops=1)
               ->  Merge Append (actual rows=4 loops=1)
                     Sort Key: m186_part.val
                     ->  Index Scan using m186_part_2024_val_idx on m186_part_2024 m186_part_1 (actual rows=4 loops=1)
                     ->  Index Scan using m186_part_2025_val_idx on m186_part_2025 m186_part_2 (actual rows=1 loops=1)
                     ->  Index Scan using m186_part_2026_val_idx on m186_part_2026 m186_part_3 (actual rows=1 loops=1)
 Optimizer: Postgres query optimizer
 Execution Time: 2.912 ms

The row counts are the mechanism: nodeMergeAppend.c primes a binaryheap with one tuple from every child (:243), then repeatedly takes the heap’s minimum and refills that one slot (:258–:263). Hence 4 rows from the winning child and exactly 1 from each of the others — the unavoidable cost of an order-preserving merge. Two cross-refs, not re-explanations. This partition-pruning route through Append is upstream PostgreSQL’s mechanism, and Cloudberry has a second, independent one — the Dynamic* scan family driven by a PartitionSelector, which ORCA emits instead (§18.2 for the scans, §18.7 for the selector); the same goal, two implementations, and which one you get is decided by the Optimizer: line. And enable_parallel_append is on here, but intra-segment parallelism is §18.2’s subject.

18.6.5 The small, essential nodes

Limit — nodeLimit.c (586) — A little state machine (LIMIT_INITIAL, LIMIT_INWINDOW, LIMIT_SUBPLANEOF, LIMIT_WINDOWEND, … execnodes.h:3334) that skips OFFSET rows, passes count rows, then stops. It also tells its child how few rows are wanted: ExecSetTupleBound(compute_tuples_needed(node), outerPlanState(node)) at :448.

Unique — nodeUnique.c (195) — Removes adjacent duplicates only, so it demands sorted input; it keeps the last emitted tuple and compares with ExecQualAndReset (:93). The hash-based alternative for DISTINCT is nodeAgg.c’s AGG_HASHED (§18.3).

SetOp — nodeSetOp.c (653) — INTERSECT/EXCEPT (and their ALL forms) by counting per-input occurrences, in SETOP_SORTED mode over a sorted Append, or SETOP_HASHED mode filling a hash table first (setop_fill_hash_table, :72).

Result — nodeResult.c (359) — Either a single computed row with no child (the VALUES-of-one case, an INSERT’s source), or a gate: a one-time resconstantqual checked once (rs_checkqual, :91) that, if false, makes the whole subtree return nothing.

FunctionScan / ValuesScan / TableFuncScan — generate_series(…) in FROM, a literal VALUES list, and XMLTABLE/JSON_TABLE respectively. All three appear as leaf scans with no relation behind them.

CteScan / NamedTuplestoreScan — In upstream, reading a materialised CTE and reading an AFTER-trigger transition table. In Cloudberry, as we will see, neither node is reachable.

LockRows — nodeLockRows.c (404) — SELECT … FOR UPDATE: re-fetch each row from its subplan and lock it, with EvalPlanQual re-checking if it moved. §11.1/§11.2 own the lock semantics.

Limit passes OFFSET+count rows up from each segment, and hands its bound down to the Sort

SET optimizer = off;
EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF)
  SELECT id, val FROM m186_t ORDER BY val DESC, id OFFSET 5 LIMIT 3;

 Limit (actual rows=3 loops=1)
   ->  Gather Motion 3:1  (slice1; segments: 3) (actual rows=8 loops=1)
         Merge Key: val, id
         ->  Limit (actual rows=8 loops=1)
               ->  Sort (actual rows=8 loops=1)
                     Sort Key: val DESC, id
                     Sort Method:  top-N heapsort  Memory: 75kB
                     ->  Seq Scan on m186_t (actual rows=66711 loops=1)
   (slice1)    Executor memory: 134K bytes avg x 3x(0) workers, 134K bytes max (seg0).  Work_mem: 1K bytes max.
 Optimizer: Postgres query optimizer
 Execution Time: 22.376 ms

Three mechanisms in eight lines. The plan has two Limit nodes, because a limit is not distributive: the per-segment one must pass OFFSET + LIMIT = 8 rows (it cannot know which are the global first five to discard), and only the coordinator’s Limit applies the offset and returns 3. Sort Method: top-N heapsort proves that ExecSetTupleBound reached the sort, which then kept a bounded heap of 8 instead of sorting 66 711 rows (§18.3 owns bounded sorts). And when ExecLimit runs dry it calls ExecSquelchNode(node, false) (nodeLimit.c:366) — Cloudberry’s “nobody will read you again” signal, so the segments still streaming into that motion stop instead of blocking their peers (§18.1).

Unique needs sorted input; SetOp has a sorted and a hashed shape; Result can gate a whole plan away

SET optimizer = off;  SET enable_hashagg = off;
EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF)
  SELECT grp FROM m186_dim UNION SELECT val FROM m186_t WHERE val < 3;
EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF)
  SELECT grp FROM m186_t INTERSECT SELECT grp FROM m186_dim;
RESET enable_hashagg;
EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF) SELECT count(*) FROM m186_t WHERE 1 = 0;

 Gather Motion 3:1  (slice1; segments: 3) (actual rows=10 loops=1)
   ->  Unique (actual rows=5 loops=1)
         Group Key: m186_dim.grp
         ->  Sort (actual rows=402 loops=1)
               Sort Method:  quicksort  Memory: 75kB
               ->  Redistribute Motion 3:3  (slice2; segments: 3) (actual rows=402 loops=1)
                     ->  Append (actual rows=213 loops=1)
                           ->  Seq Scan on m186_dim (actual rows=5 loops=1)
                           ->  Seq Scan on m186_t (actual rows=208 loops=1)

 Gather Motion 3:1  (slice1; segments: 3) (actual rows=10 loops=1)
   ->  SetOp Intersect (actual rows=5 loops=1)
         ->  Sort (actual rows=100005 loops=1)
               Sort Method:  quicksort  Memory: 15521kB
               ->  Append (actual rows=100005 loops=1)
                     ->  Subquery Scan on "*SELECT* 2" (actual rows=5 loops=1)
                     ->  Redistribute Motion 3:3  (slice2; segments: 3) (actual rows=100000 loops=1)

 Aggregate (actual rows=1 loops=1)
   ->  Result (actual rows=0 loops=1)
         One-Time Filter: false
   (slice0)    Executor memory: 12K bytes.
 Execution Time: 0.052 ms

Two notes on the first two plans. Unique and SetOp both need their input hashed to one segment per group first — hence the Redistribute Motion beneath them; a set operation is a distributed dedup, and the Motion is doing the real work. With enable_hashagg back on, the same INTERSECT becomes HashSetOp Intersect and the 15 MB sort disappears. The third plan is the cheapest in this whole chapter: Result with One-Time Filter: false never opened the table, no gang was needed at all, and slice0’s footer reads 12K bytes.

Two nodes in that catalogue you will never see on this system, and it is worth knowing why. CteScan is dead code in Cloudberry: create_ctescan_plan (createplan.c:4939) builds a SubqueryScan over either the inlined subplan or a ShareInputScan, and make_ctescan itself is marked pg_attribute_unused() at :7235 — a WITH … AS MATERIALIZED referenced twice comes out as Shared Scan (share slice:id 1:0) instead (§18.7). NamedTuplestoreScan is unreachable for a different reason: transition tables need statement-level triggers, and CREATE TRIGGER … FOR EACH STATEMENT is rejected at parse time — “Triggers for statements are not yet supported” (gram.y:9440), behind a GUC that exists only so pg_restore from older major versions can proceed.

Cloudberry gives FunctionScan a third kind of InitPlan: one whose result is a whole tuplestore

SET optimizer = off;
CREATE FUNCTION m186_gen() RETURNS SETOF int LANGUAGE sql
  EXECUTE ON INITPLAN AS $$ SELECT i FROM generate_series(1,3) i $$;
EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF)
  SELECT count(*) FROM m186_t t, m186_gen() g WHERE t.id = g;

 Finalize Aggregate (actual rows=1 loops=1)
   InitPlan 1 (returns $0)  (slice3)
     ->  Function Scan on m186_gen g_1 (actual rows=3 loops=1)
   ->  Gather Motion 3:1  (slice1; segments: 3) (actual rows=3 loops=1)
         ->  Partial Aggregate (actual rows=1 loops=1)
               ->  Hash Join (actual rows=2 loops=1)
                     Hash Cond: (t.id = g.g)
                     ->  Seq Scan on m186_t t (actual rows=66653 loops=1)
                     ->  Hash (actual rows=2 loops=1)
                           ->  Redistribute Motion 1:3  (slice2) (actual rows=2 loops=1)
                                 ->  Function Scan on m186_gen g (actual rows=3 loops=1)
   (slice3)    Executor memory: 197K bytes.  Work_mem: 17K bytes max.
 Optimizer: Postgres query optimizer

EXECUTE ON INITPLAN is a Cloudberry function attribute with no upstream equivalent, and it stretches the InitPlan idea in an interesting direction. The planner clones the FunctionScan into an InitPlan and sets resultInTupleStore on the original (createplan.c:9263); at run time the InitPlan copy actually calls the function and writes its rows into a shared tuplestore file, while the Function Scan in the main plan reads that file instead of invoking anything (nodeFunctionscan.c:101, via tuplestore_open_shared). The parameter $0 is a dummy. Replacing the SQL body with a PL/pgSQL one that raises a notice shows the body running exactly once even though three segments consume its output — which is the point: a volatile or expensive set-returning function evaluated once on the coordinator, then shared.

LockRows exists, but only where there can be no Motion — here, a utility-mode session on the coordinator

-- normal session, GDD off: no LockRows node at all
SET optimizer = off;
EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF) SELECT id FROM m186_dml WHERE id = 42 FOR UPDATE;

-- PGOPTIONS='-c gp_role=utility' psql -p 7100 -d postgres
SET optimizer = off;
EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF) SELECT id FROM m186_dml WHERE id = 42 FOR UPDATE;

 Gather Motion 1:1  (slice1; segments: 1) (actual rows=1 loops=1)
   ->  Index Scan using m186_dml_pkey on m186_dml (actual rows=1 loops=1)
         Index Cond: (id = 42)
 Optimizer: Postgres query optimizer

 LockRows (actual rows=0 loops=1)
   ->  Index Scan using m186_dml_pkey on m186_dml (actual rows=0 loops=1)
         Index Cond: (id = 42)
   (slice0)    Executor memory: 115K bytes.
 Optimizer: Postgres query optimizer

src/backend/parser/analyze.c:4284

bool
checkCanOptSelectLockingClause(SelectStmt *stmt)
{
	if (!IS_QUERY_DISPATCHER())                     return false;
	if (!gp_enable_global_deadlock_detector && Gp_role != GP_ROLE_UTILITY)
		return false;
	if (optimizer)                                  return false;  /* ORCA cannot emit LockRows */
	if (stmt->op != SETOP_NONE)                     return false;
	if (list_length(stmt->fromClause) != 1)         return false;
	if (!IsA(linitial(stmt->fromClause), RangeVar)) return false;
	...
	return true;
}

“It is not easy to lock tuples in Apache Cloudberry, since tuples may be fetched through motion nodes,” says the comment in planner.c:2293 — a row that arrived over an interconnect no longer has a lockable identity in the process that is examining it. So a LockRows path is only created when canOptSelectLockingClause is true, which needs the global deadlock detector on (it is off here), a single plain table, the PostgreSQL planner, and no subqueries; otherwise Cloudberry upgrades the row lock to a table-level lock, exactly as §11.1/§11.2 describe. The utility-mode session above satisfies the condition trivially — no distribution, no motion — and the node appears. Note the last clause in that check: even with GDD enabled, if (optimizer) return false means ORCA plans never contain a LockRows node at all.

18.6.6 ModifyTable: one node, four statements

nodeModifyTable.c is the largest file in the executor — 5482 lines — and almost all of it serves a single node type. One ModifyTable drives INSERT, UPDATE, DELETE and (since PG15) MERGE, distinguished only by its operation field. Its shape is inverted relative to every other node in this chapter: it is at the top of the plan, it pulls tuples from its subplan rather than being pulled from, and its normal return value is nothing at all.

The parts worth naming. ExecInitModifyTable (:4574) builds one ResultRelInfo per target relation and registers them in EState.es_result_relations; with a partitioned target, ExecPrepareTupleRouting (:3897) picks the leaf per row and mt_lastResultOid caches the last one so a sorted stream does not re-look-up. Row identity comes from junk columns the planner added to the subplan’s target list — a ctid for heap, a whole-row attribute for AO/AOCO and PAX where a TID is not enough (:4170). Writing goes through the table AM (table_tuple_insert/_update/_delete), which is why the same node writes heap, AO, AOCO and PAX (§5, §6). RETURNING is a projection built at init and evaluated by ExecProcessReturning (:348) — and it is what turns a DML statement back into something that produces tuples.

An UPDATE needs no Gather Motion; RETURNING adds one; MERGE and ON CONFLICT both send the query to the PostgreSQL planner

SET optimizer = off;
EXPLAIN (ANALYZE, VERBOSE, TIMING OFF, COSTS OFF) UPDATE m186_dml SET val = val + 1 WHERE grp = 3;
EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF) DELETE FROM m186_dml WHERE id > 49990 RETURNING id, val;

SET optimizer = on;  SET optimizer_trace_fallback = on;
EXPLAIN (COSTS OFF) INSERT INTO m186_dml VALUES (1,1,1)
  ON CONFLICT (id) DO UPDATE SET val = excluded.val;

 Update on public.m186_dml (actual rows=0 loops=1)
   ->  Seq Scan on public.m186_dml (actual rows=2385 loops=1)
         Output: (val + 1), ctid, gp_segment_id
         Filter: (m186_dml.grp = 3)
   (slice0)    Executor memory: 129K bytes avg x 3x(0) workers, 129K bytes max (seg0).

 Gather Motion 3:1  (slice1; segments: 3) (actual rows=10 loops=1)
   ->  Delete on m186_dml (actual rows=4 loops=1)
         ->  Index Scan using m186_dml_pkey on m186_dml (actual rows=4 loops=1)
               Index Cond: (id > 49990)

INFO:  GPORCA failed to produce a plan, falling back to Postgres-based planner
DETAIL:  Falling back to Postgres-based planner because GPORCA does not support
         the following feature: ON CONFLICT clause
 Insert on m186_dml
   Conflict Resolution: UPDATE
   Conflict Arbiter Indexes: m186_dml_pkey
   ->  Result
 Optimizer: Postgres query optimizer

The plain UPDATE has no motion whatsoever: the Update node sits in slice0 on the segments, each QE updating only its own rows, and the coordinator hears only a row count. Add RETURNING and a Gather Motion appears on top, because now the statement has results to bring home. Note the junk list — Cloudberry needs gp_segment_id alongside ctid, because a row identity is only unique within a segment. And both ON CONFLICT and MERGE trigger the §17.1 fallback: they are ORCA-unsupported constructs, so however you set optimizer, the plan you get for them is the PostgreSQL planner’s.

One honest caveat about the MERGE instrumentation. Running EXPLAIN ANALYZE on a MERGE that demonstrably updated 3 rows and inserted 5 (verified by querying the table inside the same transaction) printed Tuples: skipped=5 and nothing else. The line is computed as total − mt_merge_inserted − mt_merge_updated − mt_merge_deleted (explain.c:5176) from the local ModifyTableState, and under a dispatched plan those counters are not gathered back from the QEs — so treat the Tuples: breakdown as unreliable here and trust the statement tag or a SELECT. The MERGE plan also contains a Split Merge under an Explicit Redistribute Motion, and an UPDATE of a distribution key becomes a Split Update — a delete plus an insert, because the row must physically move to another segment. Those two nodes are §18.7’s subject, not this section’s.

18.6.7 What this catalogue leaves out

Two deliberate gaps. The Cloudberry-specific operators — Split Update and Split Merge, Assert, Shared Scan and Sequence, RuntimeFilter, PartitionSelector, TupleSplit — are §18.7’s, and each of them exists because an MPP executor has a problem PostgreSQL does not. And Motion, the node that has appeared in nearly every plan in this section without ever being explained, belongs to Chapter 19: nodeMotion.c is only 1405 lines, but everything interesting about it lives in the interconnect underneath. Every Broadcast Motion that fed a recursive term, every Redistribute Motion that made a Unique correct, every Gather Motion that carried a RETURNING row home — those are the next chapter’s subject, and with them the plan tree finally stops being a single process’s private business.