We have no join-heavy benchmark, and no columnar-specific join optimisation; measure before deciding on set_join_pathlist_hook

[ChronicallyJD]

Our optimisations stop at the scan and the aggregate directly above it. A join is core's
node, and we get no say in join order, join method, or what reaches the columnar side.

Confirmed structurally: we install set_rel_pathlist_hook, create_upper_paths_hook,
build_simple_rel_hook (get_relation_info_hook below 19), object_access_hook and
ProcessUtility_hook. There is no set_join_pathlist_hook, so we never offer a join
path.

Measured, a columnar-to-heap join today:

 Finalize GroupAggregate
   ->  Gather Merge  (Workers Planned: 6)
         ->  Partial HashAggregate
               ->  Hash Join
                     Hash Cond: (c.hostname = d.hostname)
                     ->  Parallel Custom Scan (ColumnarScan) on cpu_pgc c
                     ->  Hash -> Seq Scan on hosts_dim d

That works, and it works because we are a table access method rather than a foreign data
wrapper. Cross-format joins have never needed anything from us.

First, what this is not

It will not help the cross-engine numbers. All eight TSBS queries in
docs/benchmarks.md are single-table:

$ grep -lciE '\bjoin\b' queries/tsbs/*.sql | wc -l
0
$ grep -hiE '^\s*FROM' queries/tsbs/*.sql | sort | uniq -c
      8 FROM %T

There is no join in that benchmark to accelerate. Anyone reaching for this as a way to
close the TimescaleDB gap should reach for the clustering result instead, which is
measured and large.

The actual gap, which is a measurement gap

We have never measured a join-heavy workload on pgColumnar. Our whole performance story
is single-table scans and aggregates, which is the shape TSBS has. Star schemas and
dimension joins are the shape columnar storage is usually bought for, and we do not know
what we do on them. Not "we know and it is bad" -- we do not know.

That is the prerequisite. Proposing a join implementation before measuring whether we lose,
and where, is committing to work with no evidence behind it.

The mechanism worth evaluating, which is not join ordering

If there is a win available inside a table access method, the literature says it is
runtime filter pushdown, not join order. When the build side of a hash join produces a
small key set, pushing that set into the probe side's scan lets it skip data before
decoding it.

We are unusually well placed for this. The scan already skips chunk groups on zone maps and
already does equality skipping through pgcolumnar.enable_bloom_filter. What is missing is
a way to hand it a key set discovered at runtime rather than one written in the query.

Whether PostgreSQL's executor lets an extension do that cleanly is exactly the unknown.

Suggested sequence

1. Build a join-heavy fixture and measure it. A star schema: one columnar fact table,
   several heap dimensions, and the ordinary selective-dimension-join shape. Compare against
   heap and against TimescaleDB, the same way the cross-engine page does, and publish the
   result whichever way it goes.
2. Read the plans, not just the timings. The question is where the time goes: decoding
   rows the join then discards, or the join itself. Those lead to different work, and the
   chunk-group counters already tell us which.
3. Only then decide whether runtime filter pushdown is reachable, and only then whether
   set_join_pathlist_hook is needed for it.

Step 1 is worth doing on its own merits regardless of what follows, because a columnar
extension with no join benchmark has a hole in its evidence.

Related

design/ROADMAP.md carries this as an open question, "which join-acceleration technique
returns the most inside a table access method, and how does it interact with the planner
and executor hooks". This issue is that question with the measurement step in front of it.
See also #395: that question living only in a design document is why it has sat unexamined.

[ChronicallyJD]

Scope note: this needs to change the benchmark, not just produce a number

jd's point, and it sharpens what "measure it" means here. A one-off measurement that lives
in an issue comment rots the same way a design document does. The deliverable is a
standing join-heavy arm in the benchmark, alongside the single-table one.

Immediate, not waiting for the measurement

#402 adds a What this page does not measure section to docs/benchmarks.md. The page
concluded that columnar is "the right choice for scan-heavy and aggregate-heavy analytics"
on evidence where all eight queries are FROM %T with no join. That disclosure is true
today and costs nothing, so it should not wait for the work below.

It says the shapes are unmeasured, not that we are slow on them. We do not know.

The standing work, which is what this issue is for

A fixture in bench/, not a script in a scratch directory. One columnar fact table,
several heap dimensions, and the shapes that actually distinguish the storage:

• selective dimension join: small filtered dimension, large fact, the case runtime filtering
  would help most
• unselective join: dimension barely filters, so the join is doing real work
• multi-dimension star: several joins, where join order starts to matter
• wide projection under a join: does the join force us to decode columns nothing needs

Arms to compare, matching how docs/benchmarks.md already works: pgColumnar, heap, and
TimescaleDB. Citus columnar too if the cross-engine section keeps it.

Read the plans, not only the timings. The chunk-group counters already tell us where
the time goes, and that determines what, if anything, is worth building:

• groups read of groups total, on the fact scan
• rows the join discards after we decoded them, which is the number runtime filtering
  would attack
• whether the fact side is scanned once or rescanned

Then docs/benchmarks.md gains the section, and the disclosure #402 adds gets replaced
by results.

Only after that

Whether runtime filter pushdown is reachable, and whether it needs
set_join_pathlist_hook. Both remain unanswered and neither should be committed to before
step 1 says where the time goes.

One prediction, recorded so it can be wrong

I expect the selective-dimension-join case to show a large count of rows decoded and then
discarded by the join, because nothing today can push the dimension's key set into the
scan. If that is what the counters show, runtime filtering has a measured case behind it.
If the time is inside the join instead, it does not, and this closes with a benchmark and
no code.

Writing that down now so the measurement can contradict it.

[ChronicallyJD]

Taking this. The largest of the ones I am holding, so it goes last, after the smaller measurements clear.

[ChronicallyJD]

Measured. We lose to heap on every join shape, and the prediction I recorded holds.

Star schema on the bench, 20,000,000-row fact table, three heap dimensions, PG18
non-assert, serial, no index on either fact table.

TimescaleDB is not in this run. I said the arm would include it. It does not yet, and
I would rather say so than let a two-arm table imply three.

The result

shape	pgColumnar	heap
S1 selective dimension join	3,510 ms	1,854 ms	1.9x slower
S2 unselective join	7,193 ms	5,694 ms	1.3x slower
S3 multi-dimension star	4,232 ms	1,673 ms	2.5x slower
S4 wide projection under a join	10,424 ms	1,681 ms	6.2x slower

Storage is not the problem: the columnar fact is 790 MB against heap's 1,930 MB.

The prediction was right, and the counters say why

I wrote on this issue that the selective case would show a large count of rows decoded and
then discarded. S1 and S4 decode 20,000,000 rows so the join can keep 800,000. S3
discards 19,600,000 of 20,000,000.

->  Hash Join (actual rows=800000.00)
      ->  Custom Scan (ColumnarScan) on f_pgcolumnar (actual rows=20000000.00)
            Columnar Chunk Groups Total: 134
            Columnar Chunk Groups Read: 134
            Columnar Chunk Groups Removed by Filter: 0

No pruning at all, and correctly so. The predicate is on the dimension. Zone maps have
nothing to test, because the fact table carries no restriction of its own. This is the
shape where every mechanism we have is unavailable.

The scaling, which is the actionable part

Same query, adding one projected column at a time:

projected columns	pgColumnar	heap
1	3,445 ms	1,725 ms
2	5,176 ms	1,738 ms
3	6,927 ms	1,638 ms
4	8,640 ms	1,532 ms
5	10,280 ms	1,559 ms

Columnar is linear in projected columns at about 1,720 ms each. Heap is flat. Heap
reads whole rows regardless, so column count costs it nothing. We pay per column, on 20
million rows, and the join then discards 96 percent of them.

Note we are already 2x slower at one column, where we read a fraction of heap's bytes.
That is decode cost, not I/O.

Fairness, checked before publishing a result that says we lose

• Both arms chose Hash Join. A different join method either side would make the table
  meaningless.
• Zero indexes on both fact tables, so neither gets an index advantage.
• Non-assert build, serial, median of three.

What this means

This is a much stronger case for runtime filter pushdown than #405 produced for
position-level materialisation. There the recoverable cost was 29 ms per 2M rows on
fixed-width columns. Here 96 percent of decoded rows are discarded and the cost scales
with column count, so the recoverable fraction is most of the query.

If the fact scan could be handed the build side's key set, it could skip vectors whose
join keys cannot match, using the machinery already behind
pgcolumnar.enable_bloom_filter.

Whether to build that is your call, not mine. It needs set_join_pathlist_hook, which
I deliberately did not commit us to when filing this. What I would say is that the
measurement no longer leaves it speculative.

What I will do next unless you say otherwise

1. Turn this into a standing bench/ harness rather than a scratch script, per the
   deliverable on this issue.
2. Add the TimescaleDB arm.
3. Update docs/benchmarks.md: Say that the benchmark page measures no joins at all #402 currently says these shapes are unmeasured, and that
   is now false. It should carry this table, including that we lose.

[ChronicallyJD]

Correcting my own result. The table I posted above used the worst possible data.

The numbers I published measured random() float8 metrics: 20,000,000 distinct values
per column, maximum entropy, nothing to compress. That is the one data shape where
columnar cannot win, and I built it without noticing, then reported that we lose by up to
6.2x.

Re-run with metrics quantised to two decimals, which is what an instrument actually
reports, and the random() arm kept alongside so the difference is measured rather than
asserted.

Corrected

shape	realistic data	random() data	heap
S1 selective dimension join	2,337 ms (1.27x)	3,486 ms (1.85x)	1,833 ms
S2 unselective join	5,946 ms (1.07x)	7,112 ms (1.25x)	5,576 ms
S3 multi-dimension star	2,956 ms (1.73x)	4,255 ms (2.52x)	1,708 ms
S4 wide projection under a join	4,389 ms (2.65x)	10,332 ms (6.14x)	1,653 ms

Storage on the realistic fixture: 52 MB against heap's 1,776 MB, a 34x difference.

So the data shape accounted for a large part of the gap, and S4 more than halves, from
6.14x to 2.65x. My earlier table overstated the problem substantially and should not be
used.

But we still lose, and that part is real

Even on data that compresses 34 to 1, we are behind on every join shape. So compressibility
was not the whole story and I am not going to present it as an excuse.

Here is what makes it legible. The same quantised data, same query, without a join:

	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

Without a join we win by 1.9x. With one we lose. Same rows, same bytes, same encoding.

The mechanism, and I was half right the first time

I hypothesised the join boundary was the cost, then measured A/B/C and said the data had
refuted me. It had not: I ran that decomposition with the vectorized aggregate off, so
the no-join arm was unvectorized too and both looked equally slow.

With vectorization enabled the split appears, and the reason is structural: our
vectorized aggregate only sits directly above our scan. Put any join between the scan
and the aggregate and it cannot apply. Enabling it on the join arm changes nothing, 3,428
to 3,465 ms, which is noise.

So the join does not hurt us through slot overhead. It hurts us by disabling the thing we
are fast at, and then we compete row-at-a-time against a storage format built for exactly
that.

What this changes about the recommendation

Runtime filter pushdown is still worth having, and S4 still discards 96 percent of decoded
rows. But it is now clearly the second item, because it makes the losing path cheaper
rather than restoring the winning one.

The first question is whether a vectorized aggregate can survive a join at all. I do not
know, and it is a much larger question than a filter hook.

Corrections I owe

• The table in my previous comment is wrong and this one supersedes it.
• I said "runtime filter pushdown has a measured case". It has a weaker case than I
  implied, and a differently-shaped one.
• docs/benchmarks.md must not carry my first numbers. Say that the benchmark page measures no joins at all #402's disclosure that these
  shapes are unmeasured is still closer to the truth than the first table was.

TimescaleDB is still not in this run.