A runtime filter is worth nothing unless the fact table is clustered on the join key, and the fold a join removes is worth 4.1x but is off by default

[jdatcmd]

#401 (closed, completed) did the measuring and the fix was never filed. Its own final
number is the case for this issue:

	columnar	heap
avg(m1), no join, vectorized aggregate on	624 ms	1,206 ms	1.9x faster
the same via a join	2,337 ms	1,833 ms	1.27x slower

Same rows, same bytes, same encoding. Storage on that fixture was 52 MB against heap's
1,776 MB. The whole join shape family was behind:

shape	columnar vs heap
S1 selective dimension join	1.27x slower
S2 unselective join	1.07x slower
S3 multi-dimension star	1.73x slower
S4 wide projection under a join	2.65x slower

(Those are #401's corrected numbers on realistic quantised data. Its first table used
random() float8 with 20M distinct values per column and overstated the gap; it should not
be quoted.)

The structural reason, from #401

We install set_rel_pathlist_hook, create_upper_paths_hook, build_simple_rel_hook,
object_access_hook and ProcessUtility_hook. There is no set_join_pathlist_hook, so
we never offer a join path and our optimisations stop at the scan and the aggregate directly
above it.

The cheap version, which reuses machinery that already exists

A full join path is not the only option and probably not the first one. The scan already
skips chunk groups using per-chunk bloom filters, wired into
pgcolumnar_native_group_can_match, for hashable columns with deterministic collation. A
star-schema join builds a hash table on the dimension side and then reads every chunk group
of the fact table, because nothing tells the scan which keys survived.

Passing the build side's key set down as a runtime filter would let the fact-table scan skip
groups on exactly the mechanism already in place. That is sideways information passing, and
it is the standard answer for this shape in every columnar engine.

What to settle first

• Whether the win is skipping (fewer groups read) or decode (fewer columns materialised).
  Those want different implementations and We have no join-heavy benchmark, and no columnar-specific join optimisation; measure before deciding on set_join_pathlist_hook #401's S4 row, a wide projection under a join at
  2.65x, hints the second matters too.
• Whether it can be done without set_join_pathlist_hook, which is a larger commitment.

Measure before building, on #401's fixture so the numbers are comparable to the table above.

[OffgridwithJD]

Reviewed the claims here against the tree and against #401. The structural claims hold, and
the inherited numbers are sound in a way that is worth stating explicitly, because I have
just spent a while on #753 where the opposite was true.

The structural claims, verified on a947032

• set_join_pathlist_hook — 0 occurrences in src/. Confirmed.
• The five hooks listed are all present and installed:
  set_rel_pathlist_hook (columnar_customscan.c:84), create_upper_paths_hook
  (columnar_vector.c:791), build_simple_rel_hook (columnar_tableam.c:107),
  object_access_hook (:103), ProcessUtility_hook (:104, installed at :3014-3015).
• pgcolumnar.enable_bloom_filter defaults to true (columnar_tableam.c:80,
  DefineCustomBoolVariable at :2705), and the bloom probe really is inside
  pgcolumnar_native_group_can_match (columnar_reader.c:1354, NativeBloomMetadata fetched
  per column). So "reuses machinery that already exists" is accurate, and the machinery is on
  by default rather than behind an opt-in.

The inherited numbers are reproducible, which is the part I checked hardest

I went looking for the failure mode I found on #753 — a figure quoted from an earlier document
that cannot be re-derived from what that document records. It is not the case here, on
three counts:

1. This issue quotes We have no join-heavy benchmark, and no columnar-specific join optimisation; measure before deciding on set_join_pathlist_hook #401's corrected table and says so, and explicitly warns off the
   superseded one: "Its first table used random() float8 with 20M distinct values per column
   and overstated the gap; it should not be quoted." That superseded table is the one with
   S4 at 6.2x. Quoting 2.65x instead is correct.
2. We have no join-heavy benchmark, and no columnar-specific join optimisation; measure before deciding on set_join_pathlist_hook #401 did the correction itself, for a stated reason — the first run measured random()
   float8, 20,000,000 distinct values per column, which is the worst possible case for the
   encoder and not what an instrument reports.
3. The fixture is in the repo: bench/run_bench_join.sh, with S1-S4 parameterised
   (:236-241) and both data shapes still selectable (BENCH_SHAPES="quantised,random",
   :72). So these numbers can be re-run rather than re-argued.

That is the difference between this issue and #753 as filed, and it is worth the sentence.

One thing I checked and it did not hold

I suspected S4 — "wide projection under a join", the worst row at 2.65x — might be
contaminated by #768's text-decode cost, which would have made it not a join finding at all.
It is not. The fact table is ts timestamptz, host_id int, m1..m8 float8
(run_bench_join.sh:210-212); there is no text column anywhere in it.

But a weaker version of that does hold, and the fixture is one shape short of settling it

#768 measures a single float8 column at 1.52x the heap, on a plain serial scan with
no join in sight. S4 projects eight float8 columns. So some unknown part of S4's 2.65x
is the per-column decode cost that #768 is about, and not anything the join causes.

This bears directly on the question this issue says to settle first — "whether the win is
skipping (fewer groups read) or decode (fewer columns materialised)" — and the bench can
answer it as it stands, with one shape added:

S0      SELECT avg(m1) FROM %T                              -- already exists (:235)
S0wide  SELECT avg(m1+m2+m3+m4+m5+m6+m7+m8) FROM %T         -- the missing one
S4      SELECT avg(f.m1+…+f.m8) FROM %T f JOIN hosts h …    -- already exists (:241)

S4 ÷ S0wide is the join's contribution to the wide-projection penalty with the decode cost
divided out. If that ratio is near 1, S4's 2.65x is decode and belongs to #768, and runtime
filter pushdown would not touch it. If it is well above 1, the join is adding something and
the row means what the issue takes it to mean.

That is a cheap discriminator on an existing fixture, and I would run it before either half of
"what to settle first", because it decides which half is even in scope.

On the skipping-versus-decode question, #401 already has half the answer

S1 and S4 decode 20,000,000 rows so the join can keep 800,000.

96% of decoded rows discarded is direct evidence for the skipping lever, and the chunk-group
counters #401 recommends reading are the instrument for it. Worth carrying that sentence into
this issue rather than leaving it in a closed one — it is the strongest single argument here
and it is currently a link away.

[jdatcmd]

Measured: the join contributes 12% of S4, not all of it

S4 is this issue's worst row and it projects eight float8 columns. #768 measures a
single float8 column at 1.52x the heap with no join anywhere in sight, so some unknown
part of S4 was per-column decode rather than anything the join did. The bench had no arm that
could separate them: S0 is the no-join control, but it is avg(m1), narrow.

bench/run_bench_join.sh now carries S0W = avg(m1+…+m8) FROM %T, no join (PR #770). 4,000,000
rows, quantised, median of 3, serial:

id	shape	heap	columnar	col/heap
S0	no join (control)	152.191	59.854	0.39
S0W	no join, wide projection	242.782	392.257	1.62
S1	selective dimension join	201.538	236.778	1.17
S4	wide projection under a join	234.178	425.298	1.82

NARROW: the join costs columnar 3.96x and heap 1.32x  ->  2.99x relative
WIDE:   the join costs columnar 1.08x and heap 0.96x  ->  1.12x relative

So S4's 1.82x decomposes into 1.62x decode and 1.12x join. Most of the worst row in this
issue belongs to #768, and a runtime filter pushdown would not move it.

Where the join finding actually lives, and it is bigger than S4

The narrow pair: 0.39 → 1.17, a 2.99x relative penalty — columnar goes from 2.6x
faster than the heap to 1.17x slower, purely by adding a join above it.

That is consistent with losing the vectorized aggregate, which the bench already asserts on
both sides: S0 is vectorized, S1 is not, because the join sits between the scan and the
aggregate. So this issue's strongest case is S1, not S4, and its mechanism is the fold path
disappearing rather than the join's own work.

That does not weaken the issue. It relocates it: the thing to fix is that a join above the
scan costs columnar 3x what it costs a heap on the same rows, and the wide row is mostly a
different issue's problem.

Caveat on the numbers

These are 4,000,000 rows on a shared workstation, against this issue's original figures taken
at a different scale. The ratios between arms in one run are what is being claimed, not
the absolute times, and not agreement with the earlier absolute numbers. The same host gave a
24% swing in one arm's wall clock during #766's work, so any single ratio here is good to
roughly that.

The like-for-like premise is asserted rather than assumed: S0W is unvectorized for a different
reason than S4 is (an expression target-list entry rather than a join), and if it ever starts
vectorizing the division silently changes meaning. #770 adds that premise.

Credit to the reviewer for suspecting S4 was contaminated and for establishing that S0W is
already unvectorized, which is what makes the division valid without a third arm.

[jdatcmd]

Correction to the comment above: the 12% is not distinguishable from zero, and the conclusion is stronger for it

The evidence was already in my own table and I did not read it. heap S4/S0W = 0.96:
adding a join, a second table and a filter made the heap faster on the same scan. That is
impossible — S4 is strictly more work than S0W on both arms — so the wide pair carries error in
a direction physics forbids, and that error sits in the denominator of the columnar figure.

Re-run at median of 9 rather than 3, and the impossibility persists:

id	shape	heap	columnar	col/heap
S0	no join (control)	149.822	46.020	0.31
S0W	no join, wide projection	255.515	395.943	1.55
S1	selective dimension join	196.891	230.342	1.17
S4	wide projection under a join	247.323	446.211	1.80
	S4/S0W (the join, decode removed)	0.97	1.13

A 13% columnar effect against a denominator provably wrong by 3% the impossible way is not a
measurement of 13%. So "the join contributes 12% of S4" is withdrawn.

The replacement is a stronger claim, not a weaker one: the join's contribution to S4 is
at or below the noise floor, and S4 is per-column decode essentially in its entirety.
Runtime filter pushdown would not move that row at all, rather than moving most of it.

bench/run_bench_join.sh now prints S4/S0W for both arms and warns explicitly when the heap
ratio falls below 1.00, so the next reader meets the impossibility in the output instead of
rediscovering it by arithmetic.

The narrow pair is unaffected and it is the result

NARROW  join penalty: columnar 5.01x   heap 1.31x   ->  3.81x relative
WIDE    join penalty: columnar 1.13x   heap 0.97x   <-  heap impossible

Columnar goes from 2.6x faster than the heap to 1.17x slower purely by putting a join
above it. Both arms move the right way and both are far outside any noise floor measured on
this host. At 3 reps the same figure was 2.99x; at 9 it is 3.81x, so it is stable in kind if
not to two figures.

This is a relocation of this issue and not a weakening of it. The join penalty is larger
than the issue claimed. It is simply not where the issue was pointing: S1 is the case, S4 is
mostly #768's, and the mechanism is the vectorized aggregate being lost when anything comes
between the scan and a bare Aggref — which the bench asserts on both sides, and which the
reviewer measured independently on #755 for the expression and wrapping-function cases.

Credit to the reviewer for checking my headline number against a physical bound rather than
against its plausibility.

[jdatcmd]

Precision note on the number that survived

The narrow relative penalty is quoted above as 3.81x (median of 9) against 2.99x
(median of 3). That is a 27% move with rep count, and three significant figures is more
precision than a quantity that moves 27% can carry. The same standard that retired the 12%
applies to the figure that lived, not only to the one that died.

Read it as roughly 3 to 4x.

The finding is robust in kind and that part is not in doubt at any rep count: both arms
move the right way, and columnar going from 2.6x faster than the heap to 1.17x slower
purely by putting a join above it is the result. It is the second decimal that is not real.

This matters here specifically because this issue is where someone will later pick the work
up, and a precise-looking number in a motivating comment is the exact shape that produced
2.6x / 2.14x / 1.70x on #766. The only difference is that this one is directionally safe.

Raised by the reviewer, holding me to the standard I had just applied to my own withdrawn
number.

[jdatcmd]

Third run, and the scatter is now visible rather than inferred

bench/run_bench_join.sh now prints each arm's min..max beside its median, because the
impossible-heap-ratio tripwire is one-sided — it fires only when noise pushes the heap
ratio below 1.0, which is about half the runs in which the same error exists. Its silence is
not a clean bill, and the warning now says so.

That makes the wide pair's problem readable without arithmetic:

S0W  columnar  391.121..419.936   median 395.085    7.4% of min
S4   columnar  431.028..464.749   median 442.080    7.8% of min

A ratio of two quantities each spread 7.5% cannot resolve a 12% effect. Same conclusion
the impossible heap ratio reached, arrived at directly and without depending on which way the
noise fell.

And it revises the number that survived, downward in precision

The narrow relative penalty, across three runs:

run	columnar	heap	relative
median of 3	3.96x	1.32x	2.99x
median of 9 (a)	5.01x	1.31x	3.81x
median of 9 (b)	3.79x	1.36x	2.78x

Range 2.78x to 3.81x. The spread column shows why: S0's columnar arm runs
44.888..66.376 on a median of 60.488 — 48% of its own minimum. That arm is the noisiest
thing in the dataset, and it is the denominator.

So read the narrow penalty as roughly 3x, and not to two decimals. The finding is robust
in kind at every rep count and that part is not in doubt: both arms move the right way, and
columnar going from 2.6x faster than the heap to 1.17x slower purely by putting a join
above it is the result. The second digit is not real.

Superseding my "3.81x" above, which was a single run reported to three significant figures.

[jdatcmd]

The number is recoverable: ~3.5x. The median was the wrong statistic.

Superseding "roughly 3x" above. The reason the three runs never converged was not sample size,
it was the statistic.

Contention on a shared box can only make a run slower, never faster, so the noise is
one-sided. A median is robust against symmetric outliers. It is not robust against a
one-sided contamination that shifts most of the samples, because then most of the samples
are the contamination. The min is the run where least was stolen.

The dataset says so without needing the argument:

S0 columnar, run (a) MEDIAN = 46.020
S0 columnar, run (c) MIN    = 44.888    <- agree to 2.5%
S0 columnar, run (c) MEDIAN = 60.488    <- the two MEDIANS disagree by 31%

One run's median agrees with another run's min to 2.5% while the medians disagree by 31%. And
in that run min 44.888 / median 60.488 / max 66.376 puts the median 73% of the way from
min to max — right-skewed, not scatter about a centre.

Measured consequence: min converges, median does not

Narrow relative join penalty, two fresh 9-rep runs:

statistic	run 5	run 6	spread
from min	3.45x	3.58x	3.8%
from median	3.05x	2.87x	6.2%
from median, the earlier three runs	2.99x / 3.81x / 2.78x		37%

That also explains why more reps never helped: more samples improve a median's estimate of a
distribution whose centre is itself moving with ambient load, and no amount of sampling
fixes that.

And it moves the finding up, which is the predicted direction

S0 is the shortest arm at ~45–60 ms, so a fixed few milliseconds of scheduling jitter is the
largest proportional share there — and S0 is the denominator. So median(S1)/median(S0)
understates.

The narrow relative penalty is about 3.5x, not the ~3x the medians suggested. Columnar
goes from 2.6x faster than the heap to 1.17–1.20x slower purely by putting a join
above it.

bench/run_bench_join.sh now takes every headline ratio from min and prints the median beside
it, because the two disagreeing is itself the signal that a run was contended.

The one-sided-contamination argument and the min-vs-median cross-check are the reviewer's.