The grouped parallel arm loses to the serial node because the Finalize is priced off estimate_num_groups on an expression key

[jdatcmd]

Summary

Split out of #349, which is otherwise complete. #366 made the grouped vectorized path
parallel-aware and it works — but the planner declines it on exactly the shapes where
it wins most, and the reason is a costing asymmetry rather than anything about the
node.

20M-row TSBS-shaped fixture, PG18 assert, 4 workers:

shape	core	serial node	parallel arm
G1 (1 metric, 12h window)	6,144 ms	4,555 ms	4,587 ms (serial chosen)
G2 (10 metrics, 12h window)	12,410 ms	9,225 ms	7,493 ms (serial chosen)
G3 (full scan, group by host)	892 ms	896 ms	600 ms (parallel chosen)

Forced onto G1 the parallel arm runs in 1,184 ms against the serial node's 4,555 —
a 3.9x win the planner is declining.

Mechanism

The planner's own numbers on G1:

partialScan=15,042   ppath=16,292   gather=16,292   final=44,324   serial=20,042
dNumGroups=200,000   (actual groups ~8,000)

Our partial node under its Gather costs 16,292 against the serial node's 20,042
— it wins on everything it actually does. The core Finalize HashAggregate on top adds
28,031, and that term is priced off dNumGroups.

For a date_trunc(...) grouping key estimate_num_groups cannot estimate distinctness
and returns a count near the input row count: 200,000 estimated against ~8,000 actual
here, and 2,000,000 against 48,000 on a larger fixture. The finalize is overpriced by
that ratio and loses the comparison on its own.

Why it is structural, not a tuning miss

The serial node emits finished values, so it pays no finalize at all. #350
deliberately priced it per input row with no per-output-group term — an earlier version
charged per group and autoanalyze's group estimate flipped the plan choice, so the node
sometimes did not run.

So any two-phase plan pays a group-count-driven finalize and the serial node does not.
When the group estimate is inflated, the serial node wins by construction, whatever
the parallel arm actually costs.

The mechanism is confirmed, not inferred

G3's grouping key is a plain column, where estimate_num_groups is accurate, and there
the parallel arm is chosen and is faster. Accurate estimate → chosen; inflated
estimate → declined. Same code, same box, same session.

Fix directions

1. Charge the serial node for the group hash table it builds, so both arms pay a
   group-count term. Closest to correct, and the reason fix: charge the grouped aggregate path for the folding it does (#349) #350 avoided it (autoanalyze
   flipping the choice) is worth re-testing rather than assumed — it may have been an
   artifact of charging per output group instead of per hash entry built.
2. Cost the finalize off something less brittle than estimate_num_groups on an
   expression key — e.g. clamp it by the partial arm's own row estimate.
3. Leave it, and accept that the parallel arm only fires on plain-column keys.

(1) and (2) both change plan choice on shapes beyond this one, which is why #366 did
not fold either in: each deserves its own measurement.

Not urgent

pgcolumnar.enable_group_vectorization and enable_parallel_vector_agg are both off
by default, so no default install is affected. Post-alpha in my view; noted on #367.

Related

#349 (parent, complete), #366 (the parallel arm), #350 (the serial node's costing).

[jdatcmd]

The diagnosis in this issue is wrong, or at least incomplete. There are two causes and the bigger one is not the group estimate.

I filed this blaming estimate_num_groups. Measured properly, that is the second
cause. The first is that the serial node is priced from a parallel path.

What the planner is actually holding

Dumping input_rel->pathlist at the point ColumnarTryGroupAggPath picks its
cheapest, on a 2M-row fixture:

pathlist[0] nodetag=296 (T_GatherPath)      cost=0.0..15026.0    rows=1999605
pathlist[1] nodetag=297 (T_GatherMergePath) cost=74307.9..103771.4
chosen cheapest = 15026.0

There is no serial scan in the list at all. By the time this hook runs,
generate_useful_gather_paths has put a Gather over the columnar partial path into
pathlist, and add_path has dropped the serial columnar scan it dominates. The
comment on that loop says it takes "the columnar custom or sequential scan"; what it
actually takes is the Gather, whose cost is the scan already divided across four
workers.

So a node that executes single-threaded was priced at 15,026 against a true
serial scan of ~35,109. That is not a modelling subtlety, it is a serial node
charging a parallel price, and it made the serial node unbeatable by any honestly
costed parallel plan. Same family as #362: code reading rel->pathlist after
add_path has reshaped it and getting something other than what the comment says.

Fixing it closes most of the gap but not all of it

Skipping parallel paths, and costing the scan directly when nothing serial survives
(which is the common case, not the rare one):

	before	after
serial node's scan input	15,026	35,109
serial grouped node total	20,025	40,109
parallel arm total	44,324	44,324
margin	2.21x in serial's favour	1.11x

The gap closes from 2.2x to 1.1x. G3 still takes the parallel arm and wins;
G1 and G2 still do not, because the remaining 4,215 is the Finalize, and that
term is the one driven by estimate_num_groups returning 200,000 against 8,000
actual. At the true group count the Finalize is ~1,100 rather than ~28,000 and the
parallel arm wins outright.

So the two causes compound, and neither alone is sufficient:

1. serial node priced from a Gather (this comment, fix measured above);
2. Finalize priced off an inflated group estimate (what this issue originally said).

Things I checked and ruled out

• Not spill pricing. I thought the inflated group count made core price the
  Finalize as a spilling hash aggregate. Raising work_mem from 4 MB to 1 GB does
  not change the plan at all: 2772 / 2784 / 2786 / 2788 ms, serial node in every
  arm. Wrong hypothesis, discarded.
• The partial path itself is costed correctly. partial[0] cost=0.0..15026.0 rows=499902 workers=4 is exactly the serial scan divided by four. The parallel
  side was never the problem.

What I am landing

Cause (1) only, as its own change, because it is independently wrong regardless of
what happens to the group estimate: a serial node must not inherit a parallel path's
cost. Five-major build clean with zero warnings; native_groupagg,
parallel_vector_agg, ungrouped_vector_agg, native_agg, analyze_stats and
column_projection all pass; full matrix running.

It changes no plan selection on G1/G2/G3 by itself, and I would rather say that
plainly than dress an honest-costing fix up as a speedup. It is a precondition for
cause (2) being fixable at all: with the serial node at 15,026 no group-estimate
work could ever have helped, because the finalize would have had to cost less than
nothing.

Cause (2) stays open here.