q5 is 1.73x slower with the vectorization GUCs on, contradicting an earlier measurement of the same shape

[jdatcmd]

Summary

On the q5 shape (grouped aggregate, ten avg() metrics, 12-hour window over the 100M TSBS fixture), turning the three optional vectorization GUCs on makes the query 1.73x slower: 16,443 ms to 28,494 ms.

That contradicts an earlier measurement of the same shape where enable_group_vectorization helped. I do not have an explanation, and this issue exists to hold the evidence rather than to assert one.

Not user-visible by default: all three settings default to off.

The two measurements

Both on the bench host, same 100M fixture, same query shape.

Run A, from the #349 measurement phase (labelled G2 there), varying one GUC:

arm	time
projection on, group_vectorization off	10,731 ms
projection on, group_vectorization on	8,474 ms

group_vectorization helped, 1.27x.

Run B, from the #348 cross-engine re-measurement:

arm	time
pgColumnar defaults	16,443 ms
ungrouped_vector_agg + parallel_vector_agg + group_vectorization all on	28,494 ms

The same setting now appears to hurt, 1.73x, and the absolute numbers do not line up with Run A either: the all-on arm is 8,474 ms in one run and 28,494 ms in the other.

What differs between the runs, in order of how likely I think it is

1. Run B enables two GUCs that Run A did not. Run A varied only enable_group_vectorization. Run B turned on enable_ungrouped_vector_agg and enable_parallel_vector_agg as well. The interaction between the ungrouped and grouped paths on a grouped ten-aggregate query is the first thing to test, by re-running Run B's arms one GUC at a time.

2. The grouped path's cost model changed between the runs. fix: charge the grouped aggregate path for the folding it does (#349) #350 landed between them. It charges the grouped path cpu_operator_cost * scan_rows * naggs, so for a ten-aggregate query the charge is ten times what a one-aggregate query gets. That is the intended behaviour, but q5 is exactly the shape where it bites hardest, and it may be selecting a different plan than Run A did. The plans were not captured in Run B, which is the gap that makes this hard to settle from the data on hand.

3. Ordinary run-to-run variance. Ruled out as a complete explanation: 8,474 against 28,494 is 3.4x, and this box has been stable to a few percent on repeated measurements elsewhere. It may still contribute.

What would settle it

• Re-run q5 on current main capturing EXPLAIN (ANALYZE) for every arm, not just the timing. Plan choice is the most likely discriminator and was not recorded.
• Vary the three GUCs independently rather than as a block, so the interaction in (1) is isolated.
• Check whether the grouped path is chosen in each arm, and if not, what replaced it.

Why it is filed rather than fixed

The measurement is real and repeatable enough to be worth recording, but I cannot name the mechanism, and a guess would be worse than an open question. It is also low urgency: the settings involved all default to off, so no default install is affected.

It was deliberately left out of the docs/benchmarks.md table's narrative in #356 for the same reason. The table reports the number; the surrounding text does not claim to explain it.

Provenance

Observed while re-measuring the cross-engine benchmark for #348. Related to #349, which covers the grouped aggregate path generally.

[jdatcmd]

Measured with plans captured. The proposed mechanism is disproven, and the direction reverses on current main.

Done as scoped: the three GUCs varied one at a time rather than as a block, EXPLAIN (ANALYZE) captured for every arm, plan shape recorded alongside every timing.

Caveat stated first: this is a 20M-row TSBS-shaped fixture, not the 100M bench. Those fixtures no longer exist on pgcolumnar-audit (/root/bench-data is empty), so Run A and Run B cannot be re-examined directly. The group count is identical to the original shape (4,000 hosts x 12 hours = 48,000 groups); only the scanned row count differs.

All eight GUC combinations, q5 shape, three runs each

UG	PV	GV	ms x3	plan	spill
off	off	off	8141 7968 8001	Finalize GroupAggregate	481,101
off	off	on	7567 7539 7798	Custom Scan [vec-group]	0
off	on	off	8096 7921 7920	Finalize GroupAggregate	481,101
off	on	on	7580 7575 7571	Custom Scan [vec-group]	0
on	off	off	7998 7953 7985	Finalize GroupAggregate	481,102
on	off	on	7550 7561 7601	Custom Scan [vec-group]	0
on	on	off	8176 11723 8307	Finalize GroupAggregate	481,108
on	on	on	7594 7607 7465	Custom Scan [vec-group]	0

Finding 1: hypothesis (1) is disproven — ungrouped_vector_agg and parallel_vector_agg do nothing here

The issue's leading explanation was an interaction between the ungrouped and grouped paths, since Run B enabled two GUCs Run A did not. It is not that. Hold GV fixed and every UG/PV combination gives the same plan and the same time within noise — ~8,000 ms at GV=off, ~7,550 ms at GV=on, in all four combinations of the other two.

That is expected in hindsight: both govern the ungrouped path, and q5 is grouped. But it was the first thing to test and it is now tested rather than assumed. "The three vectorization GUCs" is one GUC for this query.

Finding 2: the direction reverses on current main

Ten runs each, same session, same fixture:

arm	10 runs	min	max	spread
defaults (GV=off)	7751 7818 8194 9005 8960 8975 8901 9020 8823 9024	7,751	9,024	1.16x
all three on	5272 5407 5373 5446 5272 5577 5561 5330 5489 5446	5,272	5,577	1.06x

The all-on arm is 1.5x faster, not 1.73x slower. Whatever produced Run B's 28,494 ms does not reproduce on current main at this scale.

Finding 3: the mechanism behind the instability, and why (3) should not have been ruled out

The GV=off plan spills ~481,000 temp blocks at the default work_mem=4MB — it is a GroupAggregate over a Sort. The GV=on plan spills nothing, because the vectorized node builds a hash table instead of sorting.

A plan doing half a million temp block reads and writes has timing dominated by I/O scheduling, and it is the unstable arm here: 1.16x spread against the vectorized arm's 1.06x. In the eight-arm table above one arm produced 8176 / 11723 / 8307 with every setting held constant — a 1.43x swing inside a single arm, larger than most of the differences the original runs were compared across.

The issue ruled out ordinary variance because "this box has been stable to a few percent on repeated measurements elsewhere". That is true of the measurements it was based on, and not true of this plan: the shapes measured elsewhere did not spill. #358's 1.73x sits inside the range a spilling plan produces.

Conclusion

• Hypothesis (1), GUC interaction: disproven. UG and PV are no-ops on this shape.
• Hypothesis (2), fix: charge the grouped aggregate path for the folding it does (#349) #350's costing changing plan choice: not the explanation for the direction — GV=on is chosen and is faster in every arm here.
• Hypothesis (3), variance: the most likely explanation, and specifically variance of the spilling non-vectorized plan, which is the arm that was treated as the stable baseline.

I cannot close this by explaining 28,494 ms, because the fixture that produced it is gone. What I can say with evidence: the mechanism the issue proposed is not real, the contradiction does not reproduce, and the comparison rests on a plan whose run-to-run spread I have measured at up to 1.43x with settings held constant.

Recommendation for the benchmark re-measure (#367)

Since @ChronicallyJD is taking that pass: report work_mem and temp spill alongside every published number, and repeat any arm that spills. The published table's stability claim depends on it, and this issue is what a single-shot measurement of a spilling plan looks like afterwards. Raising work_mem for the grouped shapes would also change which engine the table flatters.

Closing as not-reproducible with the mechanism identified. Reopen if the 100M fixture returns and it recurs there.