Re: Batching in executor

Hi,

I work with OLAP-ish workloads fairly often, so this thread caught my
interest and I ran some experiments. I built v4 one patch at a time,
in four configurations, to see how much each patch contributes. I also
varied two things independently: the column order the quals reference,
and the selectivity of the leading qual.

These seem to connect to the column request interface being discussed,
so I am reporting them together.

=== 1. Setup

AMD Ryzen 5 7530U (2 cores / 4 threads), 7 GiB, WSL2 (Linux 5.15),
gcc 11.4.0
meson -Doptimization=2 -Ddebug=false -Dcassert=false -Dllvm=disabled

Table:
CREATE TABLE bar (a int, b int, c int, d int, e int, f int, g int,
h int, i text, j int, k int, l int, m int, n int,
o int);
INSERT INTO bar SELECT g,g,g,g,g,g,g,g, repeat('x',100), g,g,g,g,g,g
FROM generate_series(1, 5000000) g;

all-visible VACUUM (FREEZE)'d; PD_ALL_VISIBLE set on 1000/1000 pages
not-all-visible same data loaded without VACUUM; 0/1000 pages

shared_buffers = 2GB, pg_prewarm before every run, parallel query,
JIT, synchronize_seqscans and autovacuum disabled, fsync = off.

Per query: 3 warmups, then the median of 21-31 measured executions.
Build order base-p1-p12-p123-p123-p12-p1-base, three rounds, with the
ratios computed independently per round. The first round is discarded
because it runs right after the cluster starts.

Queries:
q1 count(*), no qual
q2 SELECT * FROM (SELECT a,b,c,d,e FROM bar) s OFFSET 1000000000
q3a WHERE a > 0 q3o WHERE o > 0
q4 WHERE a>0 AND b>0 AND c>0 AND d>0 AND e>0 AND f>0
q5 WHERE a<0 AND b>0 AND c>0 AND d>0 AND e>0 AND f>0
q6 WHERE a>0 AND b>0 AND c>0 AND d>0 AND e>0 AND f<2500000

=== 2. Where the gain comes from

0001+0002 is not a proposed configuration; I built it separately to
see where the gain comes from. The machine is not stable, so the
figures are ranges over three rounds rather than single numbers.
Percent against master, negative is faster.

all-visible

query 0001 0002 0003 total
----------------------- ---------- ---------- ---------- ----------
count(*), no qual -16 .. -13 -0 .. +1 +0 .. +2 -16 .. -10
projection, 5 cols -8 .. -6 -2 .. +1 +0 .. +4 -8 .. -4
one qual, first col a -16 .. -6 -17 .. -8 +2 .. +5 -23 .. -18
one qual, last col o -8 .. -4 -24 .. -22 +4 .. +5 -25 .. -23
six quals, all pass -4 .. -3 -7 .. -6 -5 .. -4 -16 .. -14
six quals, 1st rejects -7 .. -4 -35 .. -33 +21 .. +22 -19 .. -17
six quals, last halves -5 .. -2 -7 .. -2 -7 .. -7 -16 .. -14

not-all-visible had almost the same shape (0002 is -25 .. -26% for the
single qual on the last column, -32 .. -35% for the early reject).

The totals agree well with the numbers in your v4 posting: single qual
-18..-25 against your -23.57..-19.67, six quals all pass -14..-16
against -15.71..-14.25, six quals rejecting -17..-19 against
-18.69..-16.56. The no-qual and projection rows differ, which I assume
is because my SQL differs from yours.

Two things seem readable from this.

- For queries without a qual, 0002 contributes nothing (-1 .. +1%).
That is expected: no qual, no qual loop.
- For the single qual on the last column and for the early reject,
0002 is clearly the largest contributor. This held in all three
rounds: -22 .. -24% and -32 .. -35% respectively.

=== 3. The trade-off in patch 0003

The contribution of 0003 changes sign depending on the query. The
numbers below are the contribution of 0003 relative to 0001+0002.

columns a,b,c,d,e,f columns o,a,n,b,m,c
(physical order) (jumping over the text)
------------------- -------------------- -----------------------
all pass -8.5 % -21.9 %
first qual rejects +34.1 % +22.8 %

Looking at the three "all pass" variants, which differ only in the
column order the quals reference, the spread caused by the order is

0001+0002 only 388.6 .. 551.9 ms -> 42.0 %
full v4 381.8 .. 431.1 ms -> 12.9 %

So 0003 does reduce the order dependence substantially, as you reported.
With the order that jumps over the text column it goes 551.9 -> 431.1 ms,
21.9% faster.

On the other hand, it gets slower the more rows the leading qual rejects.
Here the column order is fixed to the physical one and only the fraction
rejected by the leading qual varies; the other five are always
b>0 AND c>0 AND d>0 AND e>0 AND f>0.

rejected by 1st qual 0001+0002 full v4 difference
-------------------- ---------- -------- --------------------
0 % a>0 417.1 381.8 -35.3 ms ( -8.5 %)
50 % a>2500000 294.4 312.9 +18.5 ms ( +6.3 %)
90 % a>4500000 204.7 255.9 +51.2 ms (+25.0 %)
100 % a<0 182.5 244.8 +62.3 ms (+34.1 %)

10,000,000 rows had the same shape (+23.1% for the full reject, +18.5%
for the 90% one). The series as a whole is still faster than master;
the breakdown is the kind of thing that only shows up if you apply the
patches one at a time.

===== Why

In ExecScanBatchPrepare() with v4-0003 the attribute fetch sits before
the qual loop, while with 0002 the same fetch was inside the loop,
guarded twice:

> - if (q == 0 || qual->attnum != state->quals[q - 1].attnum)
> - mask &= ~batch->getattrs(slot, qual->attnum, first, nrows,
> - mask, values);

With 0003 that fetch moved outside the two guards - leaving the loop
once every row has been rejected, and fetching only the rows still
alive. getsomeattrs() takes no rows mask, state->natts is the union
of the columns used by the batchable prefix of quals, and how far the
walk goes is decided by attnums[natts - 1], the highest column number.

Nor can the missing columns be added afterwards as things stand.
deform_heap_tuple() in 0003 is written to support incremental
extraction, and the comment says so:

> * This is essentially an incremental version of heap_deform_tuple:
> * on each call we extract attributes up to the one needed, without
> * re-computing information about previously extracted attributes.
> + * *nvalidp is the number of attributes already extracted.

but within the same function, the selective mode that takes attnums is
the one case where it is forbidden:

> + Assert(attnums == NULL || *nvalidp == 0);

With attnums passed in there is no way to carry "how far we got" across
calls, so each call walks from the start again. Whether that Assert can
be removed is what I tried in section 4.

=== 4. Two attempts

Variant A: leave the signatures of deform_heap_tuple() and
getsomeattrs() alone; fetch only the leading qual's column into a
separate one-column buffer, evaluate that qual, and if the mask becomes
zero skip the getsomeattrs() for the remaining columns.

Variant B: remove the Assert in section 3 so the walk can be resumed,
and fetch only as far as each qual needs.

Both pass the regression tests and return the same results as v4.
Re-measured with A and B included:

query 0001+0002 full v4 A B
-------------------- ---------- -------- ------- -------
all pass, physical 504.0 478.7 535.0 740.3
all pass, over text 699.5 524.7 523.7 541.7
90% rejected 243.3 315.8 258.6 308.3
100% rejected, 1st qual 212.5 294.0 227.0 258.8
100% rejected, 2nd qual 257.3 320.3 377.9 328.2
100% rejected, 6th qual 444.1 409.1 463.0 668.7

A recovers most of what 0003 added once 90% or more is rejected (209.1
against 204.7, 186.0 against 182.5), and it keeps the independence from
column order (spread 12.3%, against 9.6% for full v4). When nothing is
rejected, though, the extra walk is pure cost: +10.7%, and +18.0% when
the second qual is the one that rejects. It looks like the selectivity
of the leading qual has to be taken into account, which I have not
tried.

B was supposed to help for every qual, but it came out slower, and it
also loses the independence from column order (spread 45.0%). The cause
is the number of getsomeattrs() calls, and the differences are almost
entirely explained by that count: +17.0 / -35.2 ms for the cases with
one call, +7.9 with two, +66.7 with three, +261.6 / +259.6 with six.

Where one call is enough, B does help as intended, but the per-call
setup is redone for every row, so the patterns with many calls end up
slower.

=== 5. A question about the interface

This may well have come up earlier in the thread; apologies if I am
going over old ground.

More generally, back to the point raised in the 2026-08-18 mail.

> An optional getcolumnattr looks useful. If it fills non-adjacent
> columns, we need to record which columns are ready, because tts_nvalid
> can only describe the first N columns.

The Assert above looks to me like the same "tts_nvalid can only
describe the first N columns" property showing up in another shape.
Your point is about not being able to record which columns are ready
when non-adjacent columns get filled; mine is about not being able to
fill the same set of columns in stages. Not the same thing, but the
root looks shared.

So my question is: in the column request interface being discussed
(filter_attrs / output_attrs, a getcolumnattr taking a Bitmapset), what
would express "ready" in place of tts_nvalid, and could it also express
filling in stages?

Judging from section 4, it may be necessary to decide not only whether
that can be expressed, but also what call granularity is expected. Even
with an API that can fetch one column at a time, calling it at that
granularity makes the gain disappear.

calls result
----- ----------------------------------------------------------
1 fastest, but the column set is fixed before any qual runs,
so there is no way to stop early
2 -18% at 90% rejected, order independence kept (variant A)
6 the per-call cost accumulates and it loses (variant B)

That said, the only thing I carried across calls was the walk position.
The per-row setup - fetching the tuple pointer and length, checking for
nulls, working out how far the offsets are known - is redone on every
call. None of it changes for the same row within the same batch, so
caching it should make the second and later calls nearly free. I have
not tried that.

Even so, I think the granularity that suits row and column stores
differs. With heap, getting a column means walking each tuple, so
splitting the walk always costs something on re-entry; caching can make
it small but not zero. With a columnar store the data for one column is
contiguous, there is no tuple to walk, and splitting costs almost
nothing. From the 2026-08-13 mail,

> each column needs separate decompression ... you might initially
> decompress only column 4 for vectorized filtering, and then _after_
> filtering, columns 2,1 for projection

That reads to me like exactly that asymmetry.

If so, then even with an interface that can express stages for column
fetching, a row-oriented implementation like heap would need to be able
to choose "fetch that whole set in one go". The same shape as being
allowed to leave tts_batch NULL, but at the granularity of the column
request.

One thing worth separating out: the waste I measured does not go away
with a filter_attrs / output_attrs split. In WHERE a<0 AND b>0 .. AND
f>0 all of a..f are on the filter side, so splitting filter from output
leaves those six columns together. What is needed is a split within the
filter columns, which is a different axis.

Since more stages means more per-call cost, the question seems to narrow
to

can a set be requested in several cheap steps

Columnar is cheap at this already (each column independent, no tuple to
walk); heap is expensive today.

I do think heap could get cheaper, because the row-at-a-time path
already has cheap incremental extraction in
tts_buffer_heap_getsomeattrs().

bslot->base.tuple (the tuple pointer), bslot->base.off (the walk
position) and slot->tts_nvalid all persist in the slot, so calling
getsomeattrs in stages only continues the walk on the second and later
calls.

If the batch path kept state of the same shape as the row-at-a-time
path, might incremental extraction work for heap as well? What has to
be added is 64 rows' worth, so on the order of 1KB. I have not tried
it, so I cannot say for sure, but I do not see a reason it could not be
brought down.

If re-entry can be made cheap on the heap side then splitting into
stages works for both; if not, heap ends up deciding the whole set up
front.

The discussion so far has centred on columnar needing the separation
because each column is decompressed separately. It looks like on heap
too, not only "which columns" but "when they are materialized" matters.

Regards,
Tatsuya Kawata