Grouped aggregates get none of the vectorized aggregate work: measure first, then decide

[jdatcmd]

Successor to #289, which is closed. That issue's diagnosis came from a profile taken while column projection was silently broken (#338), and its headline claim has since been measured and contradicted: on the 100M bench, serial-to-serial, pgColumnar now runs its q6 shape in 4,357 ms against TimescaleDB's 5,597 ms. This issue covers what measurement says actually remains.

The gap, verified in code

Grouped aggregates receive none of the vectorized aggregate work that landed under #289.

ColumnarCreateUpperPaths (src/columnar_vector.c:816-829) branches on the query shape. When groupClause != NIL it either adds the grouped path or nothing, and then returns -- so #337 (serial batch fold), #343 (parallel partial agg) and #346 (int partials) are unreachable for any grouped query.

What a grouped aggregate actually gets today:

	state
column projection (#339)	on by default, applies (it is scan-level)
serial batch fold (#337)	never reached
parallel partial agg (#343/#346)	never reached
grouped vectorized node (#321)	behind pgcolumnar.enable_group_vectorization, default off
that node's parallelism	none -- parallel_aware = false, parallel_workers = 0 (src/columnar_vector.c:938-940), and columnar_groupagg_exec_methods declares no DSM callbacks

So a default install answers a grouped aggregate with a projected scan feeding an ordinary row-wise Agg.

This is not a niche shape. Three of the four TSBS queries in #289's own table -- q4 (group by host), q5 (10 aggregates, grouped), q8 (top 20 by max) -- are grouped.

What is deliberately NOT claimed here

Nothing about the size of this. No grouped shape has been measured on current main. The published q4/q5 figures in docs/benchmarks.md show pgColumnar losing to heap, but those predate #339 (see #348), so they say nothing about today.

That is the specific mistake #289 made -- a plan built on a profile rather than on a measurement of the thing being planned -- and it is worth not repeating in its successor.

First step: measure, then decide

Before any implementation, on the 100M bench, current main, fixtures asserted, buffers alongside times, all arms in one session:

1. q4, q5 and q8 with projection on vs off. Establishes what the one lever that already applies is worth on grouped shapes.
2. The same with enable_group_vectorization on vs off. Establishes whether the existing Grouped vectorized aggregate (#289) #321 node earns its keep, and whether its default-off is the right default.
3. The same against heap and TimescaleDB serial on the same box, so the comparison is like-for-like. Note that TimescaleDB's parallel path currently faults on the bench (could not read blocks 0..0, see docs/benchmarks.md cross-engine table was measured before column projection landed and understates pgColumnar by ~50x on q6 #348), so cross-engine parallel numbers are not available until that is repaired.

Only after that should anyone choose between: making the grouped node parallel-aware, extending the batch fold to grouped shapes, or turning enable_group_vectorization on by default.

Second item, independent of the above

The batch fold pushes no scan keys, so it forfeits zone-map group pruning. ColumnarBeginRead is called with nkeys = 0 from the fold path, and with no predicates columnar_build_predicates returns immediately, so no group skipping and no vector skipping occurs.

On a randomly filled fixture this costs nothing -- no group's min/max rules out usage_user > 90.0, so nothing would have been pruned. On clustered or time-ranged data, which is the TSBS-shaped workload, enabling the fold may trade pruning for folding, and the sign of that trade is not obvious.

Detectable without any timing: under the fold, EXPLAIN reports Columnar Chunk Groups Removed by Filter: 0 regardless of how selective the predicate is. Worth confirming on a clustered fixture before it bites someone.

Third item

Three of the four levers default off (enable_ungrouped_vector_agg, enable_parallel_vector_agg, enable_group_vectorization). A default install gets projection and nothing else. Whether that is intended is a separate decision, but it should be a decision rather than an accident, and the defaults should be stated wherever performance numbers are published.

[ChronicallyJD]

Item 2 confirmed — the fold forfeits zone-map group pruning (current main)

Reproduced on a clustered fixture, off the bench so it doesn't touch your measurement session. x ascending, stripe_row_limit => 10000 → 200 groups with tight, non-overlapping zone maps; selective predicate only the last two groups satisfy:

SELECT count(*), sum(y) FROM c WHERE x > 1980000

	chunk groups read	removed by filter	buffers
enable_ungrouped_vector_agg off (core Agg pushes the key)	2 / 200	198	722
on (batch fold, reader opened with 0 keys)	200 / 200	0	1256

Both return 20000 | 990000 — correct either way; the cost is all in what got read. The Removed by Filter: 0 line is exactly the machine-independent tell you called out.

The buffer delta here (1.7×) understates it because the fixture is two narrow columns; the groups-read delta (100×) is the honest signal, and on a wide or more selective clustered table the pruning loss would swamp the fold's per-value CPU win. So the sign of the trade is data-dependent, as you said, and this is a concrete argument for the default-off it already has.

The clean fix is to push the scan keys in the fold so it reclaims group (and vector) pruning instead of forfeiting it — the fold code already flags this as "a later refinement (it needs present-index skipping)". That's self-contained, needs no bench, and doesn't touch the grouped work this issue is really about. I'm happy to take it as a small follow-up PR if you'd like it separated from the grouped decision.

[jdatcmd]

Measurement phase done. The first item is not what this issue assumed.

Bench box, current main, 100M-row TSBS-cpu (4,000 hosts, ~2.9 days, 21 columns), PG18. Fixtures asserted at 100,000,000 rows in all three tables before any timing. Buffers alongside times. Parallel arms use 4 workers.

Shapes, defined explicitly rather than claimed to be byte-exact TSBS (the bench CSV only carries q1-q5, so I cannot verify equivalence with the published table):

• G1 -- SELECT date_trunc('hour',time), hostname, avg(usage_user) ... WHERE time IN [12h) GROUP BY 1,2 (~17M rows, 48,000 groups)
• G2 -- as G1 with all 10 usage_* metrics
• G3 -- SELECT hostname, avg(usage_user) FROM cpu GROUP BY 1 over the full 100M, no time filter (4,000 groups)

arm	G1	G2	G3
pgc, projection OFF, groupvec OFF	8,825 ms	12,077 ms	18,661 ms
pgc, projection ON, groupvec OFF	7,139 ms	10,731 ms	5,953 ms
pgc, projection ON, groupvec ON	3,604 ms	8,474 ms	11,478 ms
heap, parallel	14,596 ms	17,951 ms	9,867 ms
TimescaleDB, serial	4,760 ms	8,473 ms	4,519 ms

Grouped result verified identical between columnar and heap on G1 (48,000 groups both).

Finding 1: the grouped vectorized node throws away parallelism, and is not costed for it

pgcolumnar.enable_group_vectorization=on is a 1.98x win on G1, a 1.27x win on G2, and a 1.92x loss on G3. Confirmed on G3 by three alternating runs, which is about as stable as this gets:

groupvec=off   6134.3 ms   5972.0 ms   5985.0 ms
groupvec=on   11472.8 ms  11615.7 ms  11445.3 ms

Buffers are effectively identical (38,912 vs 39,426), so this is CPU, not I/O. The plans say why:

groupvec=off:  Finalize GroupAggregate -> Gather Merge -> Partial HashAggregate
               Workers Planned: 4
groupvec=on:   Custom Scan (ColumnarScan)          <- one node, serial
               Columnar Vectorized Group Keys: 1

The grouped node is serial by construction -- parallel_aware = false, parallel_workers = 0 (src/columnar_vector.c:938-940), and columnar_groupagg_exec_methods declares no DSM callbacks. So when its path wins, it replaces a four-worker parallel plan with a single-threaded one. On G1 and G2 the vectorization is worth more than the parallelism it gives up; on G3, over a full 100M scan with only 4,000 groups, it is not.

This reframes the issue. The first thing to do is not "extend the batch fold to grouped shapes" -- it is that the existing grouped path discards parallelism and its cost model does not account for what it displaces. Making it parallel-aware plausibly fixes G3 outright and improves G1/G2 further, and it is the same machinery #343 already built for the ungrouped case.

Until that is done, enable_group_vectorization cannot be turned on by default: it would be a ~2x regression for anyone whose grouped queries look like G3.

Finding 2: projection is worth much less on grouped shapes

1.24x on G1, 1.13x on G2, 3.13x on G3 -- against 6.9x on the ungrouped q6 shape. Expected in hindsight, since a grouped query also reads its grouping keys, but worth stating so nobody generalises the q6 number. G2 is the clearest case: with 10 of 21 columns referenced there is little left to skip.

Finding 3: the published "loses to heap" result does not reproduce

docs/benchmarks.md publishes pgColumnar losing to heap on the q4 and q5 shapes. Measured here, pgColumnar wins all three grouped shapes against heap-parallel: G1 3,604 vs 14,596; G2 8,474 vs 17,951; G3 5,953 vs 9,867. Consistent with those published numbers coming from the broken-projection build. Strengthens #348.

Against TimescaleDB (serial, since its parallel path faults on this box)

G1: pgColumnar 3,604 vs 4,760, 1.32x ahead. G2: 8,474 vs 8,473, dead even. G3: 5,953 vs 4,519, 1.32x behind.

So on grouped shapes it is roughly parity, not the "~4x behind" that #289 claimed for the ungrouped case, and G3 is the one shape where TimescaleDB is still ahead -- which is also the shape where our own node throws away parallelism.

Proposed next step

Make the grouped vectorized path parallel-aware, reusing the partial/Finalize machinery from #343, and cost it against the parallel plan it displaces. Re-measure G1-G3 before considering any change to the default.

I have not started on this. Recording the measurement first so the work is planned against numbers rather than a profile -- which is the specific correction this issue exists to make.

[ChronicallyJD]

Taking Finding 1 — making the grouped vectorized path parallel-aware — unless you'd rather keep it. It's the #343/#346 machinery extended to the grouped node, which is squarely the aggregate-vectorization line I've been carrying, so I'd like to run with it.

Plan, and the one real difference from #343 I'll design around first:

• Reuse verbatim what feat: parallel-aware ungrouped vectorized batch fold (#289 phase 5/6) #343 built: the shared gap-23 stripe counter for per-worker group claiming, the DSM Estimate/Initialize/Worker callbacks, and a core Gather + Finalize Aggregate over a parallel-aware partial ColumnarAgg.
• The difference: feat: parallel-aware ungrouped vectorized batch fold (#289 phase 5/6) #343's ungrouped node emits one partial tuple per worker; the grouped node emits one partial tuple per group per worker, and the Finalize is an AGG_HASHED / AGG_SORTED AGGSPLIT_FINAL_DESERIAL keyed on the grouping columns rather than AGG_PLAIN. Each worker builds its own partial hash table over the groups in the row-groups it claims; the core Finalize re-aggregates across workers by key. The per-aggregate transition state is identical to feat: parallel-aware ungrouped vectorized batch fold (#289 phase 5/6) #343 (int8 / float8 / the _float8 and _int8[2] arrays via construct_array), so combine/overflow parity carries over unchanged.
• Cost it against the parallel plan it displaces — Finding 1's core point. The current node prices itself with parallel_workers = 0, so it can win by looking cheap while executing single-threaded; the parallel arm must be priced so it doesn't beat a genuinely-parallel plan it would then run slower than (the same trap I hit and fixed for the ungrouped serial node in feat: parallel-aware ungrouped vectorized batch fold (#289 phase 5/6) #343).

I'll design the partial-hash-table + Finalize plan shape first (the grouping-key resolution through setrefs is the fiddly part), build and correctness-gate on assert 18/19 + all-five-majors + pg18_san on my container, and leave the G1–G3 re-measurement to your bench since you're the one holding it stable. Shout if you want this yourself.

[jdatcmd]

Why the G3 regression happens, and why the cheap fix is the wrong one

ColumnarTryGroupAggPath prices itself at (src/columnar_vector.c:1390-1397):

Cost cost = cheapest->total_cost + cpu_tuple_cost;

where cheapest is the cheapest non-index path in input_rel->pathlist. The comment states the intent plainly:

Price just above the scan the reader already does. [...] a negligible fixed bump keeps this reliably below the ordinary Agg-over-scan plan whenever the feature is enabled. This is an opt-in accelerator: when it is on it should be the plan, not a coin-flip against a HashAggregate whose cost is close.

So the path wins by construction. And the comparison is only ever against serial paths -- parallel alternatives live in input_rel->partial_pathlist, which this never consults. That is precisely the G3 regression: the node displaces a four-worker plan without the cost model ever seeing that plan exists.

The obvious fix does not work

The tempting change is to price it honestly and let core's parallel plan win when it is better. That fixes G3 and loses G1 and G2.

The node is serial, so an honest cost is roughly serial-scan + fold. Core's parallel agg costs roughly serial/4. Honest costing therefore makes this node lose nearly always -- including on G1, where it is measurably 1.98x faster than the four-worker plan it would be losing to. The cost model has no way to express "serial but with a per-row constant several times smaller".

The commit comment records that this was already learned once, from the other direction:

An earlier version charged per output group, which let autoanalyze flip the choice on large inputs -- so the node sometimes did not run at all.

So: cost it honestly and it loses when it should win; cost it as now and it wins when it should lose. Both are symptoms of the same thing -- the node is serial, and the planner is being asked to pretend that does not matter.

This confirms parallel-awareness as the fix, not a nicety

Once the grouped path is parallel-aware it no longer has to be mispriced to win. It would be both vectorized and parallel, so an honest cost would select it on merit, and the G3 case disappears because nothing is being displaced. It also makes the default-on question answerable, which it currently is not.

Scope, by analogy with #343 which already did this for the ungrouped case: per-worker partial grouped folding emitting (group key, transition state) tuples, combined by a core Finalize, with the workers claiming distinct row groups through the shared counter the parallel scan already uses.

Starting on that. Recording the costing analysis first because it rules out the cheaper option, and someone would otherwise reasonably try it.

[ChronicallyJD]

All yours — our comments crossed (mine 12:51, yours 12:52) and you're clearly already on it, so I'm withdrawing my claim above and stepping back; go ahead so we don't both build it.

We're fully aligned on the shape and the why: your costing analysis nails the trap I was gesturing at — honest costing makes the serial node lose G1/G2 where it's genuinely faster, current costing makes it win G3 where it shouldn't, and parallel-awareness dissolves both because the node then wins on merit with nothing displaced. That's the same reason #343 had the ungrouped serial node step aside once the parallel arm existed, rather than trying to out-cost it.

Where I'm useful without colliding:

• I'll review the grouped parallel PR when it lands — the grouping-key resolution through setrefs for a custom partial node is the part I'd look at hardest.
• The item-2 scan-key fold fix (reclaim zone-map pruning) is still on the table and doesn't touch the grouped path — say the word if you want it split off to me.

[jdatcmd]

Correction to my costing analysis above

The comment where I argued that honest costing cannot work was wrong, and I have now measured it. Fixed in #350.

What I argued: the node is serial, so an honest cost is roughly serial-scan while core's parallel agg is roughly serial/4, therefore honest pricing must lose the windowed shapes where the node is genuinely 1.98x faster.

What that missed: core's plan also pays per-row aggregation, and on the windowed shapes that is dominated by a Sort. I compared our scan cost against their divided scan cost and ignored the rest of their plan.

The planner's own estimates, which I should have read before reasoning:

shape	core plan cost	our node cost	margin
G1 (1 metric, 12h)	1,642,294	518,249	3.17x
G3 (full scan, 4k groups)	463,190	337,545	1.37x

A 3.17x margin against a 1.37x margin is ample room to separate the two with a single per-row charge. Charging cpu_operator_cost * scan_rows * naggs flips G3 while keeping G1 and G2, with the valid constant range being 0.0013 to 0.0107 against a default of 0.0025.

Verified end to end: G3 now chooses the parallel plan and runs in 5,380 ms rather than 11,478; G1 and G2 keep the vectorized path at 3,175 ms and 6,842 ms.

So the item I listed as "the fix" for this issue -- making the grouped node parallel-aware -- is not what was blocking. It remains the larger win, and this issue stays open for it, but the regression that made enable_group_vectorization undefaultable is closed by a five-line cost change rather than a #343-scale feature.

Recording this because the wrong reasoning is still sitting above and someone would otherwise plan around it.