Re-measure #369 on PostgreSQL 19, which changed how partial-path startup costs are considered

[ChronicallyJD]

PostgreSQL 19 contains a planner change that targets the exact mispricing #369 is about:

Adjust the optimizer to consider startup costs of partial paths (Robert Haas, Tomas Vondra)

#369 records that the grouped vectorized aggregate's parallel arm loses to the serial node
because the core Finalize is priced off estimate_num_groups on an expression key, which
measured 25x to 42x wrong. Both settings involved are off by default because of it.

This is a measurement, not a feature. Either 19 changes the arm the planner picks on the
shapes in #369, or it does not, and the answer decides whether that issue needs code at
all or only a version gate.

What to run

The #369 shapes on 19, against 18 as the control, with the two settings on:

• pgcolumnar.enable_group_vectorization
• pgcolumnar.enable_parallel_vector_agg

Interleave the arms rather than sweeping them, and report the plan actually chosen
alongside the timing. A timing difference with the same plan means something else moved.

Why it is worth doing before any work on #369

If 19 fixes the pricing, the fix for older majors is a different and smaller problem than
the one #369 describes, and the issue's framing changes. If it does not, we have ruled out
the obvious explanation and #369 proceeds with one fewer unknown.

Blocked on nothing. 19 beta 2 builds in the matrix already.

Found while reviewing #390.

[jdatcmd]

Plan

This is the right thing to run before touching #369, and I can sharpen what it should
look for, because I did the #369 diagnosis and the shape of the answer is predictable.

What #369 actually rests on

Two causes, and only one of them is a startup-cost question:

1. Fixed in Do not price the serial grouped node from a parallel path (#369) #375. The serial node was priced from a GatherPath, because
   add_path had already dropped the serial scan and the hook took "the cheapest
   non-index path" without excluding parallel ones. Serial input 15,026 against a true
   35,109.
2. Still open, and what this issue tests. The core Finalize is priced off
   dNumGroups, which estimate_num_groups returns as 200,000 against ~8,000 actual on
   a date_trunc() key. That term alone is 28,031 of a 44,324 path.

The PG19 change considers startup costs of partial paths. Our partial node reports
startup_cost == total_cost (it emits nothing until the whole fold is done), so the
prediction is that 19 changes little here, because the term that decides it is the
Finalize's per-group cost, not the partial path's startup.

I would still run it, precisely because that is a prediction and the issue exists to
replace a prediction with a number.

What to measure

The three #369 shapes, 18 as control against 19, arms interleaved:

shape	key	why it is in the set
G1	date_trunc('hour',time), hostname	expression key, estimate 25x wrong, parallel arm declined
G2	same, 10 aggregates	as G1 with the per-aggregate charge amplified
G3	hostname	plain column, estimate accurate, parallel arm already chosen

G3 is the control that makes the whole thing interpretable: it is the shape where the
estimate is right and the parallel arm already wins. If 19 changes G3, the change is not
about group estimates at all and something else moved.

Report per arm, and this is the part that decides the issue:

• the plan actually chosen (Custom Scan with Columnar Vectorized Group Keys
  versus core's Finalize GroupAggregate), and Workers Launched;
• the estimated cost of the chosen path and of the rejected one, via
  enable_* toggling, because a timing change with the same plan means something else
  moved and a cost change with the same plan is the interesting case;
• dNumGroups as estimated versus actual group count, since that is the input under
  suspicion.

The decision it produces

• 19 picks the parallel arm on G1/G2 -> The grouped parallel arm loses to the serial node because the Finalize is priced off estimate_num_groups on an expression key #369 becomes a version-gated problem for
  15 to 18, and the fix is a backport-shaped cost adjustment rather than a redesign.
• 19 does not -> the group estimate is confirmed as the cause independent of
  startup-cost handling, and The grouped parallel arm loses to the serial node because the Finalize is priced off estimate_num_groups on an expression key #369 proceeds to fix (1) charge the serial node for the
  hash table it builds, or (2) cost the Finalize off something less brittle. One fewer
  unknown, which is what the issue asks for.

Practical note

The 20M TSBS-shaped generator I used for #366 and #369 is in my scratch and reproduces
all three shapes in a few minutes; the 100M bench is not needed to answer this, since
the estimate error is a property of the key, not the scale. Forced-parallel GUCs, plans
captured, medians of five, and work_mem stated, per #358.

I am happy to run this one myself since it is my diagnosis under test and I have the
fixtures. Say if you would rather keep it.

[ChronicallyJD]

Taking this, starting now

First of the ones I am picking up. Pure measurement, and it either changes #369s framing
or removes an unknown from it.

Running on the bench, not the shared box, so the dev machine stays free.

[ChronicallyJD]

Measured on the bench, both majors. Short answer: PG19 changes nothing here, and the

question this issue asks has moved because your cause (1) fix landed.

20M-row fixture, 4,000 hosts, PG18.4 and PG19beta2 assert builds, 4 workers,
enable_group_vectorization and enable_parallel_vector_agg both on.

The result

major	G1 estimated / actual groups	plan chosen	G1	G3
PG18.4	484,313 / 8,000 (61x)	core parallel	1,951 ms	1,072 ms
PG19beta2	453,847 / 8,000 (57x)	core parallel	1,658 ms	969 ms

Same plan on both majors. The startup-cost change for partial paths does not alter
the choice on this shape, at an estimate inflation worse than the one in your original
report.

The part that matters more: #369's symptom no longer reproduces

Your issue says the serial node is chosen over the parallel arm. On a fresh fixture,
on both majors, neither of ours is chosen. Core's parallel aggregate wins outright.

That is consistent with your own fix having landed. columnar_vector.c now skips parallel
paths when pricing the serial node:

if (p->parallel_aware || p->parallel_workers > 0 ||
    IsA(p, GatherPath) || IsA(p, GatherMergePath))
    continue;

with the comment recording exactly the effect: the serial node was priced from a Gather at
15,026 against a true serial scan of ~60,104, which "made it unbeatable by any
honestly-costed parallel plan".

So the serial node stopped winning, as intended. It did not hand the work to our parallel
arm. It handed it to core.

The open question for cause (2) is therefore not the one the issue states. It is no
longer "the serial node beats our parallel arm". It is "core's parallel aggregate beats
our parallel arm", which is a different comparison and may have a different answer.

What I could not do

Reproduce the original decline, so I could not test PG19 against it directly. I built two
fixtures, at 7x and 61x estimate inflation, and neither put the serial node in front. If
the shape still exists somewhere, it is a property of the original fixture that I have not
identified, and I would rather say that than present a shape that behaves differently as
though it were the same one.

If you still have the fixture that produced partialScan=15,042 / final=44,324, pointing
me at it would settle whether PG19 moves that specific comparison.

One observation, caveated

PG19 ran 10 to 15 percent faster than PG18 on identical plans, consistently across both
shapes and both fixtures. Same plan, so this is execution rather than the costing change
this issue is about.

Both are assert builds, which the project treats as indicative rather than publishable, so
I am not proposing this as a number for anywhere. It is worth knowing before anyone
attributes a PG19 speedup to a plan change.

Suggestion

Re-frame cause (2) on #369 rather than close this. The measurement stands: PG19 does not
change the plan choice, and the comparison that matters has shifted to core's parallel
aggregate. That is worth knowing before anyone prices the Finalize work.

[jdatcmd]

Measured. PG19 does not change the arm the planner picks.

Ran it in my container, 20M-row TSBS-shaped fixture, all three #369 shapes, 18 as the
control, arms interleaved, plan and Workers Launched captured per arm.

shape	actual groups	arm	PG18	PG19
G1	48,000	groupvec off	6,051 ms, core-agg +gather, w=4	8,319 ms, core-agg +gather, w=4
G1		groupvec on	3,197 ms, vec-group, w=0	3,158 ms, vec-group, w=0
G2	48,000	groupvec off	9,111 ms, core-agg +gather, w=4	10,950 ms, core-agg +gather, w=4
G2		groupvec on	5,664 ms, vec-group, w=0	5,394 ms, vec-group, w=0
G3	4,000	groupvec off	1,030 ms, core-agg +gather, w=4	933 ms, core-agg +gather, w=4
G3		groupvec on	685 ms, vec-group +gather, w=4	647 ms, vec-group +gather, w=4

Plan selection is identical on both majors. G1 and G2 take the serial vectorized
node with zero workers; G3 takes the parallel arm with four. Nothing moved.

This was predicted in advance, which is why it is worth trusting

On the plan I posted here I said 19 should change little, because our partial node
reports startup_cost == total_cost (it emits nothing until the whole fold is done),
so a change that considers startup costs of partial paths has nothing to bite on.
The deciding term is the core Finalize's per-group cost, which is priced off
estimate_num_groups. That is what the numbers show.

G3 is the control that makes the negative readable: it is the shape where the group
estimate is accurate, the parallel arm is already chosen, and it stays chosen on 19. So
"nothing changed" is a real result and not a broken measurement.

What it means for #369

#369 is not version-gated and the fix is not smaller than the issue describes. It
needs a cost change that works on all five majors, and the two candidates stand as
written: charge the serial node for the group hash table it builds, or cost the
Finalize off something less brittle than estimate_num_groups on an expression key.

One thing the table shows in passing that is worth carrying into that work: on G1 and
G2 the serial vectorized node already beats core by 2.6x and 1.6x while running
single-threaded, and forced onto the parallel arm G1 ran 1,184 ms against the serial
node's 4,555 in my earlier measurement. So the parallel arm is not a marginal
improvement on these shapes; it is the difference between 2.6x and roughly 5x.

Timings drifted a little between majors (G1 off-arm 6,051 to 8,319) with the plan
unchanged, which by your own framing means something other than plan choice moved. Not
worth chasing here: it does not affect the answer, and both arms of the comparison are
on the same major.

Closing this as answered. Recording the same conclusion on #369 so the next person
there does not re-ask.

[ChronicallyJD]

Correcting myself: my inference was wrong, and your measurement is the one to trust

We agree on the answer. PG19 does not change plan selection. We got there on different
fixtures and yours is the right one.

What I got wrong

I wrote that #369's symptom "no longer reproduces" and inferred that your cause (1) fix had
moved the comparison from "serial node beats our parallel arm" to "core's parallel
aggregate beats our parallel arm".

That is not supported. Your table shows G1 and G2 taking the serial vectorized node
with w=0, which is exactly the #369 situation, on both majors. The symptom reproduces
fine. It did not reproduce on my fixture, and I turned that into a claim about the
codebase rather than about my fixture. That is the wrong direction of inference and I
should have caught it.

Why mine behaved differently, which is worth recording

	actual groups	shape	outcome
mine	8,000	hostname + 10 float8, 61x estimate inflation	core parallel won outright
yours	48,000	TSBS-shaped, 21 columns	serial vectorized node chosen, w=0

Six times the groups. The core Finalize is priced per group, so at 48,000 it is expensive
enough that our serial node wins, and at 8,000 core's parallel plan is cheap enough to win
outright. My fixture was on the other side of that crossover, so our node was never in the
running and there was no decline to observe.

I built two fixtures and neither reproduced it, and I concluded the codebase had changed
rather than that my fixture was wrong. The group count was the variable and I did not
isolate it.

What stands from my run

Only the negative, and only weakly, since it was measured on a shape where our node is not
chosen on either major: plan selection identical on 18 and 19. Your G3 control is what
makes the negative readable, and I did not have one.

The PG19-is-faster observation I noted also does not survive contact with your numbers. I
saw 10 to 15 percent faster on identical plans, you saw G1 off-arm go 6,051 to 8,319 ms
the other way. Two assert builds disagreeing in direction means neither is measuring
what I implied. I withdraw it.

Agreed on the conclusion

#369 is not version-gated, the fix has to work on all five majors, and the two candidates
stand. Your point that the serial vectorized node already beats core by 2.6x and 1.6x
while single-threaded is the part I would carry into that work: the parallel arm is the
difference between 2.6x and roughly 5x, not a marginal gain.

Happy for you to close this as answered.