Subject: Batching in executor

From: Amit Langote <[email-masked]>
Date: Fri, 26 Sep 2025 22:28:33 +0900

At PGConf.dev this year we had an unconference session [1] on whether
the community can support an additional batch executor. The discussion
there led me to start hacking on $subject. I have also had off-list
discussions on this topic in recent months with Andres and David, who
have offered useful thoughts.

This patch series is an early attempt to make executor nodes pass
around batches of tuples instead of tuple-at-a-time slots. The main
motivation is to enable expression evaluation in batch form, which can
substantially reduce per-tuple overhead (mainly from function calls)
and open the door to further optimizations such as SIMD usage in
aggregate transition functions. We could even change algorithms of
some plan nodes to operate on batches when, for example, a child node
can return batches.

The expression evaluation changes are still exploratory, but before
moving to make them ready for serious review, we first need a way for
scan nodes to produce tuples in batches and an executor API that
allows upper nodes to consume them. The series includes both the
foundational work to let scan nodes produce batches and an executor
API to pass them around, and a set of follow-on patches that
experiment with batch-aware expression evaluation.

The patch set is structured in two parts. The first three patches lay
the groundwork in the executor and table AM, and the later patches
prototype batch-aware expression evaluation.

Patches 0001-0003 introduce a new batch table AM API and an initial
heapam implementation that can return multiple tuples per call.
SeqScan is adapted to use this interface, with new ExecSeqScanBatch*
routines that fetch tuples in bulk but can still return one
TupleTableSlot at a time to preserve compatibility. On the executor
side, ExecProcNodeBatch() is added alongside ExecProcNode(), with
TupleBatch as the new container for passing groups of tuples. ExecScan
has batch-aware variants that use the AM API internally, but can fall
back to row-at-a-time behavior when required. Plan shapes and EXPLAIN
output remain unchanged; the differences here are executor-internal.

At present, heapam batches are restricted to tuples from a single
page, which means they may not always fill EXEC_BATCH_ROWS (currently
64). That limits how much upper executor nodes can leverage batching,
especially with selective quals where batches may end up sparsely
populated. A future improvement would be to allow batches to span
pages or to let the scan node request more tuples when its buffer is
not yet full, so it avoids passing mostly empty TupleBatch to upper
nodes.

It might also be worth adding some lightweight instrumentation to make
it easier to reason about batch behavior. For example, counters for
average rows per batch, reasons why a batch ended (capacity reached,
page boundary, end of scan), or batches per million rows could help
confirm whether limitations like the single-page restriction or
EXEC_BATCH_ROWS size are showing up in benchmarks. Suggestions from
others on which forms of instrumentation would be most useful are
welcome.

Patches 0004 onwards start experimenting with making expression
evaluation batch-aware, first in the aggregate node. These patches add
new EEOPs (ExprEvalOps and ExprEvalSteps) to fetch attributes into
TupleBatch vectors, evaluate quals across a batch, and run aggregate
transitions over multiple rows at once. Agg is extended to pull
TupleBatch from its child via ExecProcNodeBatch(), with two prototype
paths: one that loops inside the interpreter and another that calls
the transition function once per batch using AggBulkArgs. These are
still PoCs, but with scan nodes and the executor capable of moving
batches around, they provide a base from which the work can be refined
into something potentially committable after the usual polish,
testing, and review.

One area that needs more thought is how TupleBatch interacts with
ExprContext. At present the patches extend ExprContext with
scan_batch, inner_batch, and outer_batch fields, but per-batch
evaluation still spills into ecxt_per_tuple_memory, effectively
reusing the per-tuple context for per-batch work. That’s arguably an
abuse of the contract described in ExecEvalExprSwitchContext(), and it
will need a cleaner definition of how batch-scoped memory should be
managed. Feedback on how best to structure that would be particularly
helpful.

To evaluate the overheads and benefits, I ran microbenchmarks with
single and multi-aggregate queries on a single table, with and without
WHERE clauses. Tables were fully VACUUMed so visibility maps are set
and IO costs are minimal. shared_buffers was large enough to fit the
whole table (up to 10M rows, ~43 on each page), and all pages were
prewarmed into cache before tests. Table schema/script is at [2].

Observations from benchmarking (Detailed benchmark tables are at [3];
below is just a high-level summary of the main patterns):

* Single aggregate, no WHERE (SELECT count(*) FROM bar_N, SELECT
sum(a) FROM bar_N): batching scan output alone improved latency by
~10-20%. Adding batched transition evaluation pushed gains to ~30-40%,
especially once fmgr overhead was paid per batch instead of per row.

* Single aggregate, with WHERE (WHERE a > 0 AND a < N): batching the
qual interpreter gave a big step up, with latencies dropping by
~30-40% compared to batching=off.

* Five aggregates, no WHERE: batching input from the child scan cut
~15% off runtime. Adding batched transition evaluation increased
improvements to ~30%.

* Five aggregates, with WHERE: modest gains from scan/input batching,
but per-batch transition evaluation and batched quals brought ~20-30%
improvement.

* Across all cases, executor overheads became visible only after IO
was minimized. Once executor cost dominated, batching consistently
reduced CPU time, with the largest benefits coming from avoiding
per-row fmgr calls and evaluating quals across batches.

I would appreciate if others could try these patches with their own
microbenchmarks or workloads and see if they can reproduce numbers
similar to mine. Feedback on both the general direction and the
details of the patches would be very helpful. In particular, patches
0001-0003, which add the basic batch APIs and integrate them into
SeqScan, are intended to be the first candidates for review and
eventual commit. Comments on the later, more experimental patches
(aggregate input batching and expression evaluation (qual, aggregate
transition) batching) are also welcome.

--
Thanks, Amit Langote

[1] https://wiki.postgresql.org/wiki/PGConf.dev_2025_Developer_Unconference#Can_the_Community_Support_an_Additional_Batch_Executor

[2] Tables:
cat create_tables.sh
for i in 1000000 2000000 3000000 4000000 5000000 10000000; do
psql -c "drop table if exists bar_$i; create table bar_$i (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);" 2>&1 > /dev/null
psql -c "insert into bar_$i select i, i, i, i, i, i, i, i, repeat('x',
100), i, i, i, i, i, i from generate_series(1, $i) i;" 2>&1 >
/dev/null
echo "bar_$i created."
done

[3] Benchmark result tables

All timings are in milliseconds. off = executor_batching off, on =
executor_batching on.  Negative %diff means on is better than off.

Single aggregate, no WHERE
(~20% faster with scan batching only; ~40%+ faster with batched transitions)

With only batched-seqscan (0001-0003):
Rows    off       on       %diff
1M      10.448    8.147    -22.0
2M      18.442    14.552   -21.1
3M      25.296    22.195   -12.3
4M      36.285    33.383   -8.0
5M      44.441    39.894   -10.2
10M     93.110    82.744   -11.1

With batched-agg on top (0001-0007):
Rows    off       on       %diff
1M      9.891     5.579    -43.6
2M      17.648    9.653    -45.3
3M      27.451    13.919   -49.3
4M      36.394    24.269   -33.3
5M      44.665    29.260   -34.5
10M     87.898    56.221   -36.0

Single aggregate, with WHERE
(~30–40% faster once quals + transitions are batched)

With only batched-seqscan (0001-0003):
Rows    off       on       %diff
1M      18.485    17.749   -4.0
2M      34.696    33.033   -4.8
3M      49.582    46.155   -6.9
4M      70.270    67.036   -4.6
5M      84.616    81.013   -4.3
10M     174.649   164.611  -5.7

With batched-agg and batched-qual on top (0001-0008):
Rows    off       on       %diff
1M      18.887    12.367   -34.5
2M      35.706    22.457   -37.1
3M      51.626    30.902   -40.1
4M      72.694    48.214   -33.7
5M      88.103    57.623   -34.6
10M     181.350   124.278  -31.5

Five aggregates, no WHERE
(~15% faster with scan/input batching; ~30% with batched transitions)

Agg input batching only (0001-0004):
Rows    off       on       %diff
1M      23.193    19.196   -17.2
2M      42.177    35.862   -15.0
3M      62.192    51.121   -17.8
4M      83.215    74.665   -10.3
5M      99.426    91.904   -7.6
10M     213.794   184.263  -13.8

Batched transition eval, per-row fmgr (0001-0006):
Rows    off       on       %diff
1M      23.501    19.672   -16.3
2M      44.128    36.603   -17.0
3M      64.466    53.079   -17.7
5M      103.442   97.623   -5.6
10M     219.120   190.354  -13.1

Batched transition eval, per-batch fmgr (0001-0007):
Rows    off       on       %diff
1M      24.238    16.806   -30.7
2M      43.056    30.939   -28.1
3M      62.938    43.295   -31.2
4M      83.346    63.357   -24.0
5M      100.772   78.351   -22.2
10M     213.755   162.203  -24.1

Five aggregates, with WHERE
(~10–15% faster with scan/input batching; ~30% with batched transitions + quals)

Agg input batching only (0001-0004):
Rows    off       on       %diff
1M      24.261    22.744   -6.3
2M      45.802    41.712   -8.9
3M      79.311    72.732   -8.3
4M      107.189   93.870   -12.4
5M      129.172   115.300  -10.7
10M     278.785   236.275  -15.2

Batched transition eval, per-batch fmgr (0001-0007):
Rows    off       on       %diff
1M      24.354    19.409   -20.3
2M      46.888    36.687   -21.8
3M      82.147    57.683   -29.8
4M      109.616   76.471   -30.2
5M      133.777   94.776   -29.2
10M     282.514   194.954  -31.0

Batched transition eval + batched qual (0001-0008):
Rows    off       on       %diff
1M      24.691    20.193   -18.2
2M      47.182    36.530   -22.6
3M      82.030    58.663   -28.5
4M      110.573   76.500   -30.8
5M      136.701   93.299   -31.7
10M     280.551   191.021  -31.9

---

From: Bruce Momjian <[email-masked]>
Date: Fri, 26 Sep 2025 09:49:31 -0400

> some plan nodes to operate on batches when, for example, a child node

For background, people might want to watch these two videos from POSETTE
2025.  The first video explains how data warehouse query needs are
different from OLTP needs:

	Building a PostgreSQL data warehouse
	https://www.youtube.com/watch?v=tpq4nfEoioE

and the second one explains the executor optimizations done in PG 18:

	Hacking Postgres Executor For Performance
	https://www.youtube.com/watch?v=D3Ye9UlcR5Y

I learned from these two videos that to handle new workloads, I need to
think of the query demands differently, and of course can this be
accomplished without hampering OLTP workloads?

-- 
  Bruce Momjian  <[email-masked]>        https://momjian.us
  EDB                                      https://enterprisedb.com

  Do not let urgent matters crowd out time for investment in the future.

---

From: Tomas Vondra <[email-masked]>
Date: Mon, 29 Sep 2025 13:01:15 +0200

Hi Amit,

Thanks for the patch. I took a look over the weekend, and done a couple
experiments / benchmarks, so let me share some initial feedback (or
rather a bunch of questions I came up with).

I'll start with some general thoughts, before going into some nitpicky
comments about patches / code and perf results.

I think the general goal of the patch - reducing the per-tuple overhead
and making the executor more efficient for OLAP workloads - is very
desirable. I believe the limitations of per-row executor are one of the
reasons why attempts to implement a columnar TAM mostly failed. The
compression is nice, but it's hard to be competitive without an executor
that leverages that too. So starting with an executor, in a way that
helps even heap, seems like a good plan. So +1 to this.

While looking at the patch, I couldn't help but think about the index
prefetching stuff that I work on. It also introduces the concept of a
"batch", for passing data between an index AM and the executor. It's
interesting how different the designs are in some respects. I'm not
saying one of those designs is wrong, it's more due different goals.

For example, the index prefetching patch establishes a "shared" batch
struct, and the index AM is expected to fill it with data. After that,
the batch is managed entirely by indexam.c, with no AM calls. The only
AM-specific bit in the batch is "position", but that's used only when
advancing to the next page, etc.

This patch does things differently. IIUC, each TAM may produce it's own
"batch", which is then wrapped in a generic one. For example, heap
produces HeapBatch, and it gets wrapped in TupleBatch. But I think this
is fine. In the prefetching we chose to move all this code (walking the
batch items) from the AMs into the layer above, and make it AM agnostic.

But for the batching, we want to retain the custom format as long as
possible. Presumably, the various advantages of the TAMs are tied to the
custom/columnar storage format. Memory efficiency thanks to compression,
execution on compressed data, etc. Keeping the custom format as long as
possible is the whole point of "late materialization" (and materializing
as late as possible is one of the important details in column stores).

How far ahead have you though about these capabilities? I was wondering
about two things in particular. First, at which point do we have to
"materialize" the TupleBatch into some generic format (e.g. TupleSlots).
I get it that you want to enable passing batches between nodes, but
would those use the same "format" as the underlying scan node, or some
generic one? Second, will it be possible to execute expressions on the
custom batches (i.e. on "compressed data")? Or is it necessary to
"materialize" the batch into regular tuple slots? I realize those may
not be there "now" but maybe it'd be nice to plan for the future.

It might be worth exploring some columnar formats, and see if this
design would be a good fit. Let's say we want to process data read from
a parquet file. Would we be able to leverage the format, or would we
need to "materialize" into slots too early? Or maybe it'd be good to
look at the VCI extension [1], discussed in a nearby thread. AFAICS
that's still based on an index AM, but there were suggestions to use TAM
instead (and maybe that'd be a better choice).

The other option would be to "create batches" during execution, say by
having a new node that accumulates tuples, builds a batch and sends it
to the node above. This would help both in cases when either the lower
node does not produce batches at all, or the batches are too small (due
to filtering, aggregation, ...). Or course, it'd only win if this
increases efficiency of the upper part of the plan enough to pay for
building the batches. That can be a hard decision.

You also mentioned we could make batches larger by letting them span
multiple pages, etc. I'm not sure that's worth it - wouldn't that
substantially complicate the TAM code, which would need to pin+track
multiple buffers for each batch, etc.? Possible, but is it worth it?

I'm not sure allowing multi-page batches would actually solve the issue.
It'd help with batches at the "scan level", but presumably the batch
size in the upper nodes matters just as much. Large scan batches may
help, but hard to predict.

In the index prefetching patch we chose to keep batches 1:1 with leaf
pages, at least for now. Instead we allowed having multiple batches at
once. I'm not sure that'd be necessary for TAMs, though.

This also reminds me of LIMIT queries. The way I imagine a "batchified"
executor to work is that batches are essentially "units of work". For
example, a nested loop would grab a batch of tuples from the outer
relation, lookup inner tuples for the whole batch, and only then pass
the result batch. (I'm ignoring the cases when the batch explodes due to
duplicates.)

But what if there's a LIMIT 1 on top? Maybe it'd be enough to process
just the first tuple, and the rest of the batch is wasted work? Plenty
of (very expensive) OLAP have that, and many would likely benefit from
batching, so just disabling batching if there's LIMIT seems way too
heavy handed.

Perhaps it'd be good to gradually ramp up the batch size? Start with
small batches, and then make them larger. The index prefetching does
that too, indirectly - it reads the whole leaf page as a batch, but then
gradually ramps up the prefetch distance (well, read_stream does that).
Maybe the batching should have similar thing ...

In fact, how shall the optimizer decide whether to use batching? It's
one thing to decide whether a node can produce/consume batches, but
another thing is "should it"? With a node that "builds" a batch, this
decision would apply to even more plans, I guess.

I don't have a great answer to this, it seems like an incredibly tricky
costing issue. I'm a bit worried we might end up with something too
coarse, like "jit=on" which we know is causing problems (admittedly,
mostly due to a lot of the LLVM work being unpredictable/external). But
having some "adaptive" heuristics (like the gradual ramp up) might make
it less risky.

FWIW the current batch size limit (64 tuples) seems rather low, but it's
hard to say. It'd be good to be able to experiment with different
values, so I suggest we make this a GUC and not a hard-coded constant.

As for what to add to explain, I'd start by adding info about which
nodes are "batched" (consuming/producing batches), and some info about
the batch sizes. An average size, maybe a histogram if you want to be a
bit fancy.

I have no thoughts about the expression patches, at least not beyond
what I already wrote above. I don't know enough about that part.

[1]
https://www.postgresql.org/message-id/OS7PR01MB119648CA4E8502FE89056E56EEA7D2%40OS7PR01MB11964.jpnprd01.prod.outlook.com

Now, numbers from some microbenchmarks:

> transition) batching) are also welcome.

I tried to replicate the results, but the numbers I see are not this
good. In fact, I see a fair number of regressions (and some are not
negligible).

I'm attaching the scripts I used to build the tables / run the test. I
used the same table structure, and tried to follow the same query
pattern with 1 or 5 aggregates (I used "avg"), [0, 1, 5] where
conditions (with 100% selectivity).

I measured master vs. 0001-0003 vs. 0001-0007 (with batching on/off).
And I did that on my (relatively) new ryzen machine, and old xeon. The
behavior is quite different for the two machines, but none of them shows
such improvements. I used clang 19.0, and --with-llvm.

See the attached PDFs with a summary of the results, comparing the
results for master and the two batching branches.

The ryzen is much "smoother" - it shows almost no difference with
batching "off" (as expected). The "scan" branch (with 0001-0003) shows
an improvement of 5-10% - it's consistent, but much less than the 10-20%
you report. For the "agg" branch the benefits are much larger, but
there's also a significant regression for the largest table with 100M
rows (which is ~18GB on disk).

For xeon, the results are a bit more variable, but it affects runs both
with batching "on" and "off". The machine is just more noisy. There
seems to be a small benefit of "scan" batching (in most cases much less
than the 10-20%). The "agg" is a clear win, with up to 30-40% speedup,
and no regression similar to the ryzen.

Perhaps I did something wrong. It does not surprise me this is somewhat
CPU dependent. It's a bit sad the improvements are smaller for the newer
CPU, though.

I also tried running TPC-H. I don't have useful numbers yet, but I ran
into a segfault - see the attached backtrace. It only happens with the
batching, and only on Q22 for some reason. I initially thought it's a
bug in clang, because I saw it with clang-22 built from git, and not
with clang-14 or gcc. But since then I reproduced it with clang-19 (on
debian 13). Still could be a clang bug, of course. I've seen ~20 of
those segfaults so far, and the backtraces look exactly the same.

regards

-- 
Tomas Vondra

---

From: Amit Langote <[email-masked]>
Date: Tue, 30 Sep 2025 11:11:30 +0900

Hi Tomas,

Thanks a lot for your comments and benchmarking.

I plan to reply to your detailed comments and benchmark results, but I
just realized I had forgotten to attach patch 0008 (oops!) in my last
email. That patch adds batched qual evaluation.

I also noticed that the batched path was unnecessarily doing early
“batch-materialization” in cases like SELECT count(*) FROM bar. I’ve
fixed that as well. It was originally designed to avoid such
materialization, but I must have broken it while refactoring.

---

From: Amit Langote <[email-masked]>
Date: Tue, 30 Sep 2025 11:15:02 +0900

Hi Bruce,

> accomplished without hampering OLTP workloads?

Thanks for pointing to those talks -- I gave the second one. :-)

Yes, the idea here is to introduce batching without adding much
overhead or new code into the OLTP path.

-- 
Thanks, Amit Langote

---

From: Amit Langote <[email-masked]>
Date: Tue, 30 Sep 2025 22:35:52 +0900

On Tue, Sep 30, 2025 at 11:11 AM Amit Langote <[email-masked]> wrote:
> I plan to reply to your detailed comments and benchmark results

For now, I reran a few benchmarks with the master branch as an
explicit baseline, since Tomas reported possible regressions with
executor_batching=off. I can reproduce that on my side:

5 aggregates, no where:
select avg(a), avg(b), avg(c), avg(d), avg(e) from bar;

parallel_workers=0, jit=off
Rows    master    batching off    batching on    master vs off    master vs on
1M      47.118    48.545          39.531         +3.0%            -16.1%
2M      95.098    97.241          80.189         +2.3%            -15.7%
3M      141.821   148.540         122.005        +4.7%            -14.0%
4M      188.969   197.056         163.779        +4.3%            -13.3%
5M      240.113   245.902         213.645        +2.4%            -11.0%
10M     556.738   564.120         486.359        +1.3%            -12.6%

parallel_workers=2, jit=on
Rows    master    batching off    batching on    master vs off    master vs on
1M      21.147    22.278          20.737         +5.3%            -1.9%
2M      40.319    41.509          37.851         +3.0%            -6.1%
3M      61.582    63.026          55.927         +2.3%            -9.2%
4M      96.363    95.245          78.494         -1.2%            -18.5%
5M      117.226   117.649         97.968         +0.4%            -16.4%
10M     245.503   246.896         196.335        +0.6%            -20.0%

1 aggregate, no where:
select count(*) from bar;

parallel_workers=0, jit=off
Rows    master    batching off    batching on    master vs off    master vs on
1M      17.071    20.135          6.698          +17.9%           -60.8%
2M      36.905    41.522          15.188         +12.5%           -58.9%
3M      56.094    63.110          23.485         +12.5%           -58.1%
4M      74.299    83.912          32.950         +12.9%           -55.7%
5M      94.229    108.621         41.338         +15.2%           -56.1%
10M     234.425   261.490         117.833        +11.6%           -49.7%

parallel_workers=2, jit=on
Rows    master    batching off    batching on    master vs off    master vs on
1M      8.820     9.832           5.324          +11.5%           -39.6%
2M      16.368    18.001          9.526          +10.0%           -41.8%
3M      24.810    28.193          14.482         +13.6%           -41.6%
4M      34.369    35.741          23.212         +4.0%            -32.5%
5M      41.595    45.103          27.918         +8.4%            -32.9%
10M     99.494    112.226         94.081         +12.8%           -5.4%

The regression is more noticeable in the single aggregate case, where
more time is spent in scanning.

Looking into it.

-- 
Thanks, Amit Langote

---

From: Amit Langote <[email-masked]>
Date: Fri, 10 Oct 2025 15:40:37 +0900

Hi,

> those segfaults so far, and the backtraces look exactly the same.

I can reproduce the Q22 segfault with clang-17 on macOS and the
attached patch 0009 fixes it.

The issue I observed is that two EEOPs both called the same helper,
and that helper re-peeked ExecExprEvalOp(op) to choose its path; in
this particular build the two EEOP cases in ExecInterpExpr() compiled
to identical code so their dispatch labels had the same address
(reverse_dispatch_table logging in ExecInitInterpreter() showed the
duplicate), and because ExecEvalStepOp() maps by label address the
reverse lookup could yield the other EEOP -- I saw ExprInit select
ROWLOOP EEOP while the ExprExec-time helper observed DIRECT EEOP and
ran code for it, which then crashed.

In 0009 (the fix), I split the helper into two functions, one per
EEOP, so the helper does not re-derive the opcode; with that change I
cannot reproduce the crash on macOS clang-17.

-- 
Thanks, Amit Langote

---

From: Amit Langote <[email-masked]>
Date: Mon, 27 Oct 2025 16:24:29 +0900

Hi Tomas,

> rather a bunch of questions I came up with).

Thank you for reviewing the patch and taking the time to run those
experiments. I appreciate the detailed feedback and questions.  I also
apologize for my late reply, I spent perhaps way too much time going
over your index prefetching thread trying to understand the notion of
batching that it uses and getting sidelined by other things while
writing this reply.

> helps even heap, seems like a good plan. So +1 to this.

I'm happy to hear that you find the overall direction worthwhile.

> batch items) from the AMs into the layer above, and make it AM agnostic.

Yes, the design of this patch does differ from the index prefetching
approach, and that’s largely due to the differing goals as you say.
AIUI, the index prefetching patch uses a shared batch structure
managed mostly by indexam.c and populated by the index AM.  In my
patch, each table AM produces its own batch format that gets wrapped
in a generic TupleBatch which contains the AM-specified TupleBatchOps
for operations on the AM's opaque data. This was a conscious choice:
in prefetching, the aim seems to be to make indexam.c manage batches
and operations based on it in a mostly AM-agnostic manner.  But for
executor batching, the aim is to retain TAM-specific optimizations as
much as possible and rely on the TAM for most operations on the batch
contents. Both designs have their merits given their respective use
cases, but I guess you understand that very well.

> as late as possible is one of the important details in column stores).

Exactly -- keeping the TAM-specific batch format as long as possible
is a key goal here. As you noted, the benefits of a custom storage
format (compression, operating on compressed data, etc.) are best
realized when we delay materialization until absolutely necessary.  I
want to design this patch that each TAM can produce and use its own
batch representation internally, only wrapping it when interfacing
with the executor in a generic way.  I admit that's not entirely true
with the patch as it stands as I write above below.

> not be there "now" but maybe it'd be nice to plan for the future.

I have been thinking about those future capabilities. Currently, the
patch keeps tuples in the TAM-specific batch format up until they need
to be consumed by a node that doesn’t understand that format or has
not been modified to invoke the TAM callbacks to decode it.  In the
current patch, that means we materialize to regular TupleTableSlots at
nodes that require it (for example, the scan node reading from TAM
needing to evaluate quals, etc.). However, the intention is to allow
batches to be passed through as many nodes as possible without
materialization, ideally using the same format produced by the scan
node all the way up until reaching a node that can only work with
tuples in TupleTableSlots.

As for executing expressions directly on the custom batch data: that’s
something I would like to enable in the future. Right now, expressions
(quals, projections, etc.) are evaluated after materializing into
normal tuples in TupleTableSlots stored in TupleBatch, because the
expression evaluation code isn’t yet totally batch-aware or is very
from doing things like operate on compressed data in its native form.
Patches 0004-0008 do try to add batch-aware expression evaluation but
that's just a prototype.  In the long term, the goal is to allow
expression evaluation on batch data (for example, applying a WHERE
clause or aggregate transition directly on a columnar batch without
converting it to heap tuples first). This will require significant new
infrastructure (perhaps specialized batch-aware expression operators
and functions), so it's not in the current patch, but I agree it's
important to plan for it. The current design doesn’t preclude it, it
lays some groundwork by introducing the batch abstraction -- but fully
supporting that will be future work.

That said, one area I’d like to mention while at it, especially to
enable native execution on compressed or columnar batches, is giving
the table AM more control over how expression evaluation is performed
on its batch data. In the current patch, the AM can provide a
materialize function via TupleBatchOps, but that always produces an
array of TupleTableSlots stored in the TupleBatch, not an opaque
representation that remains under AM control. Maybe that's not bad for
a v1 patch.  When evaluating expressions over a batch, a BatchVector
is built by looping over these slots and invoking the standard
per-tuple getsomeattrs() to "deform" a tuple into needed columns.
While that enables batch-style EEOPs for qual evaluation and aggregate
transition (and is already a gain over per-row evaluation), it misses
the opportunity to leverage any batch-specific optimizations the AM
could offer, such as vectorized decoding or filtering over compressed
data, and other AM optimizations for getting only the necessary
columns out possibly in a vector format.

I’m considering extending TupleTableSlotOps with a batch-aware variant
of getsomeattrs(), something like slot_getsomeattrs_batch(), so that
AMs can populate column vectors (e.g., BatchVector) directly from
their native format. That would allow bypassing slot materialization
entirely and plug AM-provided decoding logic directly into the
executor’s batch expression paths. This isn’t implemented yet, but I
see it as a necessary step toward supporting fully native expression
evaluation over compressed or columnar formats. I’m not yet sure if
TupleTableSlotOps is the right place for such a hook, it might belong
elsewhere in the abstraction, but exposing a batch-aware interface for
this purpose seems like the right direction.

> instead (and maybe that'd be a better choice).

Yeah, looking at columnar TAMs or FDWs is on my list. I do think the
design should be able to accommodate true columnar formats like
Parquet. If we had a table AM (or FDW) that reads Parquet files into a
columnar batch structure, the executor batching framework should
ideally allow us to pass that batch along without immediately
materializing to tuples.  As mentioned before, we might have to adjust
or extend the TupleBatch abstraction to handle a wider variety of
batch formats, but conceptually it fits -- the goal is to avoid
forcing early materialization. I will definitely keep the Parquet
use-case in mind and perhaps do some experiments with a columnar
source to ensure we aren’t baking in any unnecessary materialization.
Also, thanks for the reference to the VCI extension thread; I'll take
a look at that.

> building the batches. That can be a hard decision.

Yes, introducing a dedicated executor node to accumulate and form
batches on the fly is an interesting idea, I have thought about it and
even mentioned it in passing in the pgconf.dev unconference. This
could indeed cover scenarios where the data source (a node) doesn't
produce batches (e.g., a non-batching node feeding into a
batching-aware upper node) or where batches coming from below are too
small to be efficient. The current patch set doesn’t implement such a
node; I focused on enabling batching at the scan/TAM level first. The
cost/benefit decision for a batch-aggregator node is tricky, as you
said. We’d need a way to decide when the overhead of gathering tuples
into a batch is outweighed by the benefits to the upper node. This
likely ties into costing or adaptive execution decisions. It's
something I’m open to exploring in a future iteration, perhaps once we
have more feedback on how the existing batching performs in various
scenarios. It might also require some planner or executor smarts
(maybe the executor can decide to batch on the fly if it sees a
pattern of use, or the planner could insert such nodes when
beneficial).

> once. I'm not sure that'd be necessary for TAMs, though.

I tend to agree with you here. Allowing a single batch to span
multiple pages would add quite a bit of complexity to the table AM
implementations (managing multiple buffer pins per batch, tracking
page boundaries, etc.), and it's unclear if the benefit would justify
that complexity. For now, I'm inclined not to pursue multi-page
batches at the scan level in this patch. We can keep the batch
page-local (e.g., for heap, one batch corresponds to max one page, as
it does now). If we need larger batch sizes overall, we might address
that by other means -- for example, by the above-mentioned idea of a
higher-level batching node or by simply producing multiple batches in
quick succession.

You’re right that even if we made scan batches larger, it doesn’t
necessarily solve everything, since the effective batch size at
higher-level nodes could still be constrained by other factors. So
rather than complicating the low-level TAM code with multi-page
batches, I'd prefer to first see if the current approach (with
one-page batches) yields good benefits and then consider alternatives.
We could also consider letting a scan node produce multiple batches
before yielding to the upper node (similar to how the index
prefetching patch can have multiple leaf page batches in flight) if
needed, but as you note, it might not be necessary for TAMs yet. So at
this stage, I'll keep it simple.

> batching, so just disabling batching if there's LIMIT seems way too

Yeah, LIMIT does complicate downstream batching decisions. If we
always use a full-size batch (say 64 tuples) for every operation, a
query with LIMIT 1 could end up doing a lot of unnecessary work
fetching and processing 63 tuples that never get used. Disabling
batching entirely for queries with LIMIT would indeed be overkill and
lose benefits for cases where the limit is not extremely selective.

> Maybe the batching should have similar thing ...

An adaptive batch size that ramps up makes a lot of sense as a
solution. We could start with a very small batch (say 4 tuples) and if
we detect that the query needs more (e.g., the LIMIT wasn’t satisfied
yet or more output is still being consumed), then increase the batch
size for subsequent operations. This way, a query that stops early
doesn’t incur the full batching overhead, whereas a query that does
process lots of tuples will gradually get to a larger batch size to
gain efficiency. This is analogous to how the index prefetching ramps
up prefetch distance, as you mentioned.

Implementing that will require some careful thought. It could be done
either in the planner (choose initial batch sizes based on context
like LIMIT) or more dynamically in the executor (adjust on the fly). I
lean towards a runtime heuristic because it’s hard for the planner to
predict exactly how a LIMIT will play out, especially in complex
plans. In any case, I agree that a gradual ramp-up or other adaptive
approach would make batching more robust in the presence of query
execution variability. I will definitely consider adding such logic,
perhaps as an improvement once the basic framework is in.

> having some "adaptive" heuristics (like the gradual ramp up) might make

I agree that deciding when to use batching is tricky. So far, the
patch takes a fairly simplistic approach: if a node (particularly a
scan node) supports batching, it just does it, and other parts of the
plan will consume batches if they are capable. There isn’t yet a
nuanced cost-based decision in the planner for enabling batching. This
is indeed something we’ll have to refine. We don’t want to end up with
a blunt on/off GUC that could cause regressions in some cases.

One idea is to introduce costing for batching: for example, estimate
the per-tuple savings from batching vs the overhead of materialization
or batch setup. However, developing a reliable cost model for that
will take time and experimentation, especially with the possibility of
variable batch sizes or adaptive behavior. Not to mention, that will
be adding one more dimension to planner's costing model making the
planning more expensive and unpredictable.  In the near term, I’m fine
with relying on feedback and perhaps manual tuning (GUCs, etc.) to
decide on batching, but that’s perhaps not a long-term solution.

I share your inclination that adaptive heuristics might be the safer
path initially. Perhaps the executor can decide to batch or not batch
based on runtime conditions. The gradual ramp-up of batch size is one
such adaptive approach. We could also consider things like monitoring
how effective batching is (are we actually processing full batches or
frequently getting cut off?) and adjust behavior. These are somewhat
speculative ideas at the moment, but the bottom line is I’m aware we
need a smarter strategy than a simple switch. This will likely evolve
as we test the patch in more scenarios.

> values, so I suggest we make this a GUC and not a hard-coded constant.

Yeah, I was thinking the same while testing -- the optimal batch size
might vary by workload or hardware, and 64 was a somewhat arbitrary
starting point. I will make the batch size limit configurable
(probably as a GUC executor_batch_tuples, maybe only developer-focused
at first). That will let us and others experiment easily with
different batch sizes to see how it affects performance. It should
also help with your earlier point: for example, on a machine where 64
is too low or too high, we can adjust it without recompiling. So yes,
I'll add a GUC for the batch size in the next version of the patch.

> the batch sizes. An average size, maybe a histogram if you want to be a

Adding more information to EXPLAIN is a good idea. In the current
patch, EXPLAIN does not show anything about batching, but it would be
very helpful for debugging and user transparency to indicate which
nodes are operating in batch mode.  I will update EXPLAIN to mark
nodes that produce or consume batches. Likely I’ll start with
something simple like an extra line or tag for a node, e.g., "Batch:
true (avg batch size 64)" or something along those lines. An average
batch size could be computed if we have instrumentation, which would
be useful to see if, say, the batch sizes ended up smaller due to
LIMIT or other factors. A full histogram might be more detail than
most users need, but I agree even just knowing average or maximum
batch size per node could be useful for performance analysis. I'll
implement at least the basics for now, and we can refine it (maybe add
more stats) if needed.

(I had added a flag in the EXPLAIN output at one point, but removed it
due to finding the regression output churn too noisy, though I
understand I'll have to bite the bullet at some point.)

> CPU dependent. It's a bit sad the improvements are smaller for the newer

Thanks for sharing your benchmark results -- that’s very useful data.
I haven’t yet finished investigating why there's a regression relative
to master when executor_batching is turned off. I re-ran my benchmarks
to include comparisons with master and did observe some regressions in
a few cases too, but I didn't see anything obvious in profiles that
explained the slowdown. I initially assumed it might be noise, but now
I suspect it could be related to structural changes in the scan code
-- for example, I added a few new fields in the middle of
HeapScanDescData, and even though the batching logic is bypassed when
executor_batching is off, it’s possible that change alone affects
memory layout or cache behavior in a way that penalizes the unbatched
path. I haven’t confirmed that yet, but it’s on my list to look into
more closely.

Your observation that newer CPUs like the Ryzen may see smaller
improvements makes sense -- perhaps they handle the per-tuple overhead
more efficiently to begin with. Still, I’d prefer not to see
regressions at all, even in the unbatched case, so I’ll focus on
understanding and fixing that part before drawing conclusions from the
performance data.

Thanks again for the scripts -- those will help a lot in narrowing things down.

> those segfaults so far, and the backtraces look exactly the same.

The v3 I posted fixes a tricky bug in the new EEOPs for batched-agg
evaluation that I suspect is also causing the crash you saw.

I'll try to post a v4 in a couple of weeks with some of the things I
mentioned above.

--
Thanks, Amit Langote

---

From: Tomas Vondra <[email-masked]>
Date: Mon, 27 Oct 2025 17:18:50 +0100

> batching that it uses and getting sidelined by other things while

Cool! Now you can do a review of the index prefetch patch ;-)

> ...

Understood. Makes sense in general.

> a v1 patch.

I think materializing into a batch of TupleTableSlots (and then doing
the regular expression evaluation) seems perfectly fine for v1. It's the
simplest fallback possible, and we'll need it anyway if overriding the
expression evaluation will be optional (which I assume it will be?).

> When evaluating expressions over a batch, a BatchVector

I'm not sure about this BatchVector thing. I haven't looked into that
very much, I'd expect the construction to be more expensive than the
benefits (compared to just doing the materialize + regular evaluation),
but maybe I'm completely wrong. Or maybe we could keep the vector
representation for multiple operations? No idea.

But it seems like a great area for experimenting ...

> this purpose seems like the right direction.

No opinion. I don't see it as a necessary prerequisite for the other
parts of the patch series, but maybe the BatchVector really helps, and
then this would make perfect sense. I'm not sure there's a single
"correct" sequence in which to do these improvements, it's always a
matter of opinion.

> Also, thanks for the reference to the VCI extension thread; I'll take

+1 I think having a TAM/FDW reading those established and common formats
is a good way to validate the overall design.

> pattern of use, or the planner could insert such nodes when

Yeah, those are good questions. I don't have a clear idea how should we
decide when to do this batching. Costing during planning is the
"traditional" option, with all the issues (e.g. it requires a reasonably
good cost model). Another option would be some sort of execution-time
heuristics - buts then which node would be responsible for building the
batches (if we didn't create them during planning)?

I agree it makes sense to focus on batching at the TAM/scan level for
now. That's a pretty big project already.

> higher-level batching node or by simply producing multiple batches in

+1

> this stage, I'll keep it simple.

+1

> perhaps as an improvement once the basic framework is in.

I agree a runtime heuristics is probably the right approach. After all,
a lot of the issues with LIMIT queries is due to the planner not knowing
the real data distribution, etc.

> decide on batching, but that’s perhaps not a long-term solution.

Yeah, the cost model is going to be hard, because this depends on so
much low-level plan/hardware details. Like, the TAM may allow execution
on compressed data / leverage vectorization, .... But maybe the CPU does
not do that efficiently? There's so many unknown unknowns ...

Considering we still haven't fixed the JIT cost model, maybe it's better
to not rely on it too much for this batching patch? Also, all those
details contradict the idea that cost models are a simplified model of
the reality.

> as we test the patch in more scenarios.

I think the big question is how much can the batching change the
relative cost of two plans (I mean, actual cost, not just estimates).

Imagine plans P1 and P2, where

   cost(P1) < cost(P2) = cost(P1) + delta

where "delta" is small (so P1 is faster, but not much).  If we
"batchify" the plans into P1' and P2', can this happen?

  cost(P1') >> cost(P2')

That is, can the "slower" plan P2 benefit much more from the batching,
making it significantly faster?

If this is unlikely, we could entirely ignore batching during planning,
and only do that as post-processing on the selected plan, or perhaps
even just during execution.

OTOH that's what JIT does, and we know it's not perfect - but that's
mostly because JIT has rather unpredictable costs when enabling. Maybe
batching doesn't have that.

> I'll add a GUC for the batch size in the next version of the patch.

+1 to have developer-only GUC for testing. But the goal should be to not
expect users to tune this.

> implement at least the basics for now, and we can refine it (maybe add

+1 to start with something simple

> understand I'll have to bite the bullet at some point.)

Why would there be regression churn, if the option is disabled by default?

>> ...

If needed, I can rerun the tests and collect additional information
(e.g. maybe perf-stat or perf-diff would be interesting).

> I'll try to post a v4 in a couple of weeks with some of the things I

Sounds good. Thank you.

regards

-- 
Tomas Vondra

---

From: Peter Geoghegan <[email-masked]>
Date: Mon, 27 Oct 2025 13:37:25 -0400

> saying one of those designs is wrong, it's more due different goals.

I've been working on a new prototype enhancement to the index
prefetching patch. The new spinoff patch has index scans batch up
calls to heap_hot_search_buffer for heap TIDs that the scan has yet to
return. This optimization is effective whenever an index scan returns
a contiguous group of TIDs that all point to the same heap page. We're
able to lock and unlock heap page buffers at the same point that
they're pinned and unpinned, which can dramatically decrease the
number of heap buffer locks acquired by index scans that return
contiguous TIDs (which is very common).

I find that speedups for pgbench SELECT variants with a predicate such
as "WHERE aid BETWEEN 1000 AND 1500" can have up to ~20% higher
throughput, at least in cases with low client counts (think 1 or 2
clients). These are cases where everything can fit in shared buffers,
so we're not getting any benefit from I/O prefetching (in spite of the
fact that this is built on top of the index prefetching patchset).

It makes sense to put this in scope for the index prefetching work
because that work will already give code outside of an index AM
visibility into which group of TIDs need to be read next. Right now
(on master) there is some trivial sense in which index AMs use their
own batches, but that's completely hidden from external callers.

> advancing to the next page, etc.

The major difficulty with my heap batching prototype is getting the
layering right (no surprises there). In some sense we're deliberately
sharing information across different what we currently think of as
different layers of abstraction, in order to be able to "schedule" the
work more intelligently. There's a number of competing considerations.

I have invented a new concept of heap batch, that is orthogonal to the
existing concept of index batches. Right now these are just an array
of HeapTuple structs that relate to exactly one group of group of
contiguous heap TIDs (i.e. if the index scan returns TIDs even a
little out of order, which is fairly common, we cannot currently
reorder the work in the current prototype patch).

Once a batch is prepared, calls to heapam_index_fetch_tuple just
return the next TID from the batch (until the next time we have to
return a TID pointing to some distinct heap block). In the case of
pgbench queries like the one I mentioned, we only need to call
LockBuffer/heap_hot_search_buffer once for every 61 heap tuples
returned (not once per heap tuple returned).

Importantly, the new interface added by my new prototype spinoff patch
is higher level than the existing
table_index_fetch_tuple/heapam_index_fetch_tuple interface. The
executor asks the table AM "give me the next heap TID in the current
scan direction", rather than asking "give me this heap TID". The
general idea is that the table AM has a direct understanding of
ordered index scans.

The advantage of this higher-level interface is that it gives the
table AM maximum freedom to reorder work. As I said already, we won't
do things like merge together logically noncontiguous accesses to the
same heap page into one physical access right now. But I think that
that should at least be enabled by this interface.

The downside of this approach is that table AM (not the executor
proper) is responsible for interfacing with the index AM layer. I
think that this can be generalized without very much code duplication
across table AMs. But it's hard.

> batch items) from the AMs into the layer above, and make it AM agnostic.

I think that the base index prefetching patch's current notion of
index-AM-wise batches can be kept quite separate from any table AM
batch concept that might be invented, either as part of what I'm
working on, or in Amit's patch.

It probably wouldn't be terribly difficult to get the new interface
I've described to return heap tuples in whatever batch format Amit
comes up with. That only has a benefit if it makes life easier for
expression evaluation in higher levels of the plan tree, but it might
just make sense to always do it that way. I doubt that adopting Amit's
batch format will make life much harder for the
heap_hot_search_buffer-batching mechanism (at least if it is generally
understood that its new index scan interface's builds batches in
Amit's format on a best-effort basis).

-- 
Peter Geoghegan

---

From: Amit Langote <[email-masked]>
Date: Tue, 28 Oct 2025 22:11:46 +0900

Hi Peter,

Thanks for chiming in here.

> fact that this is built on top of the index prefetching patchset).

I gathered from the index prefetching thread that it is mainly about
enabling I/O prefetching, so it's nice to see that kind of speedup
even for the in-memory case.

Is this spinoff patch separate from the one that adds amgetbatch() to
IndexAmRoutine which you posted on Oct 12? If so, where can I find it?

> own batches, but that's completely hidden from external callers.

As you might know, heapam's TableAmRoutine.scan_* functions use a
"pagemode" in some cases, which fills a batch of tuples in
HeapScanData.rs_vistuples. However, that batch currently only stores
the tuples’ offset numbers. I started this work based on Andres’s
suggestion to propagate that batch up into the executor’s scan nodes.
The idea is to create a HeapTuple array sized according to the
executor’s batch size, and then populate it when the scan node calls
the new TableAmRoutine.scan_batch* variant. There might be some
overlap between our respective ideas.

> that should at least be enabled by this interface.

Interesting. It sounds like you aim to replace the fetch_tuple
interface with a more generic one, is that right?

> across table AMs. But it's hard.

Seems so.

> Amit's format on a best-effort basis).

In my implementation, the new TableAmRoutine.scan_getnextbatch()
returns a batch as an opaque table AM structure, which can then be
passed up to the upper levels of the plan. Patch 0001 in my series
adds the following to the TableAmRoutine API:

+    /* ------------------------------------------------------------------------
+     * Batched scan support
+     * ------------------------------------------------------------------------
+     */
+
+    void       *(*scan_begin_batch)(TableScanDesc sscan, int maxitems);
+    int         (*scan_getnextbatch)(TableScanDesc sscan, void *am_batch,
+                                     ScanDirection dir);
+    void        (*scan_end_batch)(TableScanDesc sscan, void *am_batch);

I haven't seen what your version looks like, but if it is compatible
with the above, I'd be happy to adopt a batch format that accommodates
multiple use cases.

-- 
Thanks, Amit Langote

---

From: Amit Langote <[email-masked]>
Date: Tue, 28 Oct 2025 22:40:48 +0900

> Cool! Now you can do a review of the index prefetch patch ;-)

Would love to and I'm adding that to my list.  :)

> expression evaluation will be optional (which I assume it will be?).

Yes.  The ability to materialize into TupleTableSlots won't be
optional for the table AM's BatchOps.  Converting to other formats
would be.

> representation for multiple operations? No idea.

Constructing the BatchVector does require looping over the batch and
deforming each tuple, typically via getsomeattrs(). So yes, there’s an
up-front cost similar to materialization. But the goal is to amortize
that by enabling expression evaluation to run in a tight loop over
column vectors, avoiding repeated jumps into slot/AM code for each
tuple and each column. That can reduce branching and improve locality.

In its current form, the BatchVector is ephemeral -- it's built just
before expression evaluation and discarded after. But your idea of
reusing the same vector across multiple operations is interesting.
That would let us spread out the construction cost even further and
might be necessary to justify the overhead fully in some cases. I’ll
keep that in mind.

> But it seems like a great area for experimenting ...

Yep.

> "correct" sequence in which to do these improvements, it's always a

Yes, I think we can come back to this later.

> now. That's a pretty big project already.

Right -- batching at the TAM/scan level is already a sizable project,
especially given its interaction with prefetching work (maybe). I
think it's best to focus design effort there and on batched expression
evaluation first, and only revisit batch-creation nodes once that
groundwork is in place.

> details contradict the idea that cost models are a simplified model of

Yeah, totally agreed -- the complexity and unpredictability here are
real, and your point about JIT costing is a good reminder not to
over-index on planner models for now.

> batching doesn't have that.

That’s an interesting scenario. I suspect batching (even with SIMD)
won’t usually flip plan orderings that dramatically -- i.e., turning
the clearly slower plan into the faster one -- though I could be
wrong. But I agree with the conclusion: this supports treating
batching as an executor concern, at least initially. Might be worth
seeing if there’s any relevant guidance in systems literature too.

> expect users to tune this.

Yes.

> Why would there be regression churn, if the option is disabled by default?

executor_batching is on my default in my patch, so a seq scan will
always use batching provided the query features preventing it are not
present, which is true for a huge number of plans appearing in
regression suite output.

> (e.g. maybe perf-stat or perf-diff would be interesting).

That would be nice to see if you have the time, but maybe after I post
a new version.

-- 
Thanks, Amit Langote

---

From: Daniil Davydov <[email-masked]>
Date: Tue, 28 Oct 2025 21:32:19 +0700

Hi,

As far as I understand, this work partially overlaps with what we did in the
thread [1] (in short - we introduce support for batching within the ModifyTable
node). Am I correct?

It's worth saying that the patch in that thread is already quite old -
now I have
an improved implementation and tests for it (as well as performance
measurements). But the basic idea and design remained unchanged.

Maybe we can combine approaches? I haven't reviewed patches in this thread
yet, but I'll try to do it in the near future.

[1] https://www.postgresql.org/message-id/flat/CALj2ACVi9eTRYR%3Dgdca5wxtj3Kk_9q9qVccxsS1hngTGOCjPwQ%40mail.gmail.com

--
Best regards,
Daniil Davydov

---

From: Amit Langote <[email-masked]>
Date: Wed, 29 Oct 2025 11:22:47 +0900

Hi Daniil,

> thread [1] (in short - we introduce support for batching within the ModifyTable

There might be some relation, but not much overlap. The thread you
mention seems to focus on batching in the write path (for INSERT,
etc.), while this work targets batching in the read path via Table AM
scan callbacks. I think they can be developed independently, though
I'm happy to take a look.

-- 
Thanks, Amit Langote

---

From: Amit Langote <[email-masked]>
Date: Wed, 29 Oct 2025 15:37:25 +0900

> That would be nice to see if you have the time, but maybe after I post

I’ve created a CF entry marked WoA for this in the next CF under the
title “Batching in executor, part 1: add batch variant of table AM
scan API.” The idea is to track this piece separately so that later
parts can have their own entries and we don’t end up with a single
long-lived entry that never gets marked done. :-)

-- 
Thanks, Amit Langote

---

From: Daniil Davydov <[email-masked]>
Date: Thu, 30 Oct 2025 19:12:03 +0700

Hi,

> I'm happy to take a look.

Oh, I got it. Thanks!

I looked at 0001-0003 patches and got some comments :
1)
I noticed that some Nodes may set SO_ALLOW_PAGEMODE flag to 'false'
during ExecReScan. heap_getnextslot works carefully with it - checks whether
pagemode is allowed at every call. If not - it just uses tuple-at-a-time mode.
At the same time, heap_getnextbatch always expects that pagemode is enabled.
I didn't find any code paths which can lead to an assertion [1] fail.
If such a code
path is unreachable under any circumstances, maybe we should add a comment
why?

2)
heapgettup_pagemode_batch : Do we really need to compute lineindex variable
in this way? :
***
            lineindex = scan->rs_cindex + dir;
            if (ScanDirectionIsForward(dir))
                linesleft = (lineindex <= (uint32) scan->rs_ntuples) ?
                    (scan->rs_ntuples - lineindex) : 0;
***

As far as I understand, this is enough :
***
        lineindex = scan->rs_cindex + dir;
        if (ScanDirectionIsForward(dir))
            linesleft = scan->rs_ntuples - lineindex;
***

3)
Is this code inside heapgettup_pagemode_batch necessary? :
***
ScanDirectionIsForward(dir) ? 0 : 0
***

4)
heapgettup_pagemode has this change :
HeapTuple    tuple = &(scan->rs_ctup) ---> HeapTuple tuple = &scan->rs_ctup
I guess it was changed accidentally.

5)
I apologize for the tediousness, but these braces are not in the
postgres style :
***
static const TupleBatchOps TupleBatchHeapOps = {
    .materialize_all = heap_materialize_batch_all
};
***

[1] heap_getnextbatch : Assert(sscan->rs_flags & SO_ALLOW_PAGEMODE)

--
Best regards,
Daniil Davydov

---

From: Amit Langote <[email-masked]>
Date: Fri, 5 Dec 2025 00:54:29 +0900

> long-lived entry that never gets marked done. :-)

I intend to continue working on this, so have just moved it into the
next fest.  I will post a new patch version next week that addresses
Daniil's comments and implements a few other things I mentioned I will
in my reply to Tomas on Oct 28; sorry for the delay.

-- 
Thanks, Amit Langote

---

From: Amit Langote <[email-masked]>
Date: Sat, 20 Dec 2025 23:12:03 +0900

> in my reply to Tomas on Oct 28; sorry for the delay.

Before I go on vacation for a couple of weeks, here's an updated patch
set.  I am only including the patches that add TAM interface, add
TupleBatch executor wrapper for TAM batches, and use it in SeqScan as I had
posted before.  There is a new patch to add a BATCHES option to EXPLAIN.  I
renamed the testing GUC to executor_batch_rows (integer) from the boolean
executor_batching.  EXPLAIN (BATCHES) example:

+-- Basic batch stats output
+select explain_filter('explain (analyze, batches, buffers off, costs off)
select * from batch_test');
+                         explain_filter
+----------------------------------------------------------------
+ Seq Scan on batch_test (actual time=N.N..N.N rows=N.N loops=N)
+   Batches: N  Avg Rows: N.N  Max: N  Min: N
+ Planning Time: N.N ms
+ Execution Time: N.N ms
+(4 rows)

What I have not included in this set are the patches that add
ExecProcNodeBatch() so that TupleBatch can be passed from one plan node to
another (parent), ExprEvalOps (EEOPs) for batched expression evaluation
(qual and aggregate transition).  I would like to focus on the patches that
allow reading batches from TAM into Scan nodes (only SeqScan for now).

After I'm back from vacation, I will post patches for batched qual
evaluation in SeqScan filter quals (once bugs are fixed and polished).
Batching in Agg node can wait for now.

In the meantime, what I would like to have someone's thoughts on:

* the shape of the TAM APIs -- should I add a TAMBatch or something that is
created, populated, and destroyed by the TAM instead of the current void
pointer and TupleBatchOps that are initialized in the executor like this
(excerpt from 0002):

+    /* Lazily create the AM batch payload. */
+    if (node->ss.ps.ps_Batch->am_payload == NULL)
+    {
+        const TableAmRoutine *tam PG_USED_FOR_ASSERTS_ONLY =
scandesc->rs_rd->rd_tableam;
+
+        Assert(tam && tam->scan_begin_batch);
+        node->ss.ps.ps_Batch->am_payload =
+            table_scan_begin_batch(scandesc,
node->ss.ps.ps_Batch->maxslots);
+        node->ss.ps.ps_Batch->ops =
table_batch_callbacks(node->ss.ss_currentRelation);
+    }

* the shape of TupleBatch itself -- its contents and operations defined in
execBatch.c/h.

* any other thoughts you might have on the project, patches.

Benchmark:

Scripts attached if you want to try them.

(Negative % = faster than master)

SELECT * FROM table LIMIT 1 OFFSET N:
Rows      Master    batch=0   vs master   batch=64   vs master
--------------------------------------------------------------
1M          11ms       11ms        -0%        8ms       -23%
2M          23ms       22ms        -1%       18ms       -23%
3M          36ms       34ms        -5%       27ms       -25%
4M          51ms       50ms        -2%       38ms       -26%
5M          64ms       64ms        -1%       48ms       -26%
10M        147ms      145ms        -1%      114ms       -22%

SELECT * FROM WHERE a > 0 LIMIT 1 OFFSET N:
Rows      Master    batch=0   vs master   batch=64   vs master
--------------------------------------------------------------
1M          31ms       31ms        +0%       16ms       -48%
2M          64ms       64ms        -0%       34ms       -47%
3M          67ms       66ms        -1%       50ms       -25%
4M          91ms       90ms        -1%       71ms       -22%
5M         119ms      113ms        -5%       88ms       -26%
10M        262ms      261ms        -0%      205ms       -21%

SELECT * FROM table WHERE o > 0 LIMIT 1 OFFSET N (last column -
deform-heavy):
Rows      Master    batch=0   vs master   batch=64   vs master
--------------------------------------------------------------
1M          38ms       37ms        -2%       38ms        +0%
2M          79ms       75ms        -6%       77ms        -4%
3M         182ms      186ms        +2%      160ms       -12%
4M         250ms      252ms        +1%      219ms       -12%
5M         314ms      316ms        +1%      273ms       -13%
10M        647ms      651ms        +1%      604ms        -7%

The smaller improvement with WHERE o > 0 is expected since accessing the
last column requires deforming most of the tuple, which dominates the
execution time. Future work on batched tuple deformation could help here.

Note on regressions with executor_batch_rows = 0 vs master:

I am not seeing the regressions with batch_rows=0 vs master as I did
before.  I think some of it might have to do with my removing some stray
fields from HeapScanData that were accidentally left there in the earlier
patches.  Also, the regressions I was observing earlier seemed more to have
to do with using gcc to compile master tree and clang to compile patched
tree, which resulted in code layout changes that seemed to cause patched
binary to regress.  Would be nice if these numbers can be verified by
others.

-- 
Thanks, Amit Langote

---

From: Amit Langote <[email-masked]>
Date: Sat, 20 Dec 2025 23:36:13 +0900

Hi Daniil,

> [1] heap_getnextbatch : Assert(sscan->rs_flags & SO_ALLOW_PAGEMODE)

Thanks for the review and apologies for getting to them so late.

I think I've addressed your comments in v4 that I just posted.

-- 
Thanks, Amit Langote

---

From: "cca5507" <[email-masked]>
Date: Mon, 22 Dec 2025 19:45:49 +0800

Hi,

Some comments for v4:

0001
====

1) table_scan_getnextbatch()
"Assert(dir == ForwardScanDirection);" -> "Assert(ScanDirectionIsForward(dir));"

2) heapgettup_pagemode_batch()
"TupleDesc	tupdesc = key ? RelationGetDescr(rel) : NULL;" -> "TupleDesc	tupdesc = RelationGetDescr(rel);"
I think the latter is enough.

3) heapgettup_pagemode_batch()
```
			/* Are there more visible tuples left on this page? */
			lineindex = scan->rs_cindex + dir;
			linesleft = (lineindex <= (uint32) scan->rs_ntuples) ?
				(scan->rs_ntuples - lineindex) : 0;
			if (linesleft > 0)
				break;	/* continue on this page */
```
The "scan->rs_ntuples" is already an uint32.

4) heapgettup_pagemode_batch()
```
		Assert(lineindex <= (uint32) scan->rs_ntuples);
```
The "scan->rs_ntuples" is already an uint32. And I think this should be "Assert(lineindex < scan->rs_ntuples);", the related
assert in heapgettup_pagemode() is also wrong.

5) heapgettup_pagemode_batch()
If the scan key filters out all tuples on a page, we may return 0 before reaching the end of scan, right?

6) heap_begin_batch()
```
	hb = palloc(sizeof(HeapBatch));
	hb->tupdata = palloc(sizeof(HeapTupleData) * maxitems);
```
Can we just use one palloc() for cache-friendly?

0002
====

1) heap_materialize_batch_all()
```
		slot->base.tts_flags &= ~(TTS_FLAG_EMPTY | TTS_FLAG_SHOULDFREE);
		slot->base.tts_tid = tuple->t_self;
		slot->base.tts_tableOid = tuple->t_tableOid;
		slot->base.tts_flags &= ~(TTS_FLAG_SHOULDFREE | TTS_FLAG_EMPTY);
```
Redundant of "slot->base.tts_flags &="?

2) TupleBatchCreate()
```
	inslots = palloc(sizeof(TupleTableSlot *) * capacity);
	outslots = palloc(sizeof(TupleTableSlot *) * capacity);
	for (int i = 0; i < capacity; i++)
		inslots[i] = MakeSingleTupleTableSlot(scandesc, &TTSOpsHeapTuple);

	b = (TupleBatch *) palloc(sizeof(TupleBatch));
```
Can we just use one palloc() for cache-friendly?

3) TupleBatchCreate()
```
	b->outslots = outslots;
	b->activeslots = NULL;
	b->outslots = outslots;
```
Redundant of "b->outslots = outslots;"?

4) TupleBatchReset()
```
	if (b == NULL)
		return;
```
This can never happen, convert to a assert or just delete it?

5) SeqNextBatch()
"Assert(direction == ForwardScanDirection);" -> "Assert(ScanDirectionIsForward(direction));"

--
Regards,
ChangAo Chen

---

From: Daniil Davydov <[email-masked]>
Date: Mon, 26 Jan 2026 16:34:44 +0700

Hi,

> Some comments for v4:

Agree with your (1)-(4) comments.

> If the scan key filters out all tuples on a page, we may return 0 before reaching the end of scan, right?

Yes. I think that we should advance to the next page if "nout == 0"
at the end of walking through the rs_vistuples.

> Can we just use one palloc() for cache-friendly?

Actually, we are using memory context when calling the palloc function.
I.e. in the general case it will not cause memory allocation. But of course
there is no guarantee for it. I saw a lot of places in the code where we
are calling the palloc function several times in a row, so I guess that
this is OK.

If you will decide to leave these palloc calls, I suggest using the
palloc_object/palloc_array functions.

A few other comments on 0001 patch:

1)
+    void       *(*scan_begin_batch)(TableScanDesc sscan, int maxitems);
Is it syntactically correct?

2)
    /* Initialize static fields of HeapTupleData. Row bodies remain on page. */
    relid = RelationGetRelid(sscan->rs_rd);
    for (int i = 0; i < maxitems; i++)
        hb->tupdata[i].t_tableOid = relid;

Is it really necessary? I see that we are setting this field inside the
heapgettup_pagemode_batch function.

A few comment on 0002 patch:

1)
I guess that you should rebase your patches on the current master, because
the second patch doesn't apply.

2)
Maybe we can use tuplestore for tuples stored in TupleBatch? It is just a
proposal - I didn't check this idea carefully.

--
Best regards,
Daniil Davydov

---

From: Amit Langote <[email-masked]>
Date: Tue, 27 Jan 2026 12:00:13 +0900

Hi,

> at the end of walking through the rs_vistuples.

Next version (v5) does it like that.

> palloc_object/palloc_array functions.

I think combining those individual pallocs into one is a good idea, so
v5 does it like that.

> Is it syntactically correct?

Yes, it compiles fine. Though I'm considering changing the return type
to a struct with common fields (like nitems) so callers can access
them directly without callback indirection.  Maybe call it TAMBatch or
something.

> heapgettup_pagemode_batch function.

It's intentional -- by initializing t_tableOid once in
heap_begin_batch, we can avoid setting it repeatedly for every tuple
in heapgettup_pagemode_batch().  Though you are correct to point out
the redundant assignment in heapgettup_pagemode_batch(); I'll change
it to an Assert instead. The relid doesn't change during the scan.

> the second patch doesn't apply.

Yep, will do.

> proposal - I didn't check this idea carefully.

TupleBatch is designed to be lightweight -- it holds an array of
TupleTableSlot pointers, not the tuple data itself. The slots
reference tuples that remain in the AM's buffer (no copy). Using
tuplestore would require materializing tuples, adding overhead we're
trying to avoid.

-- 
Thanks, Amit Langote

---

From: Amit Langote <[email-masked]>
Date: Thu, 29 Jan 2026 08:35:13 +0100

Hi,

Here is v5 of the patch series.

Patches 0001-0003 add the core batching infrastructure. 0001 adds the
batch table AM API with heapam implementation, 0002 wires up SeqScan
to use it (still returning one slot at a time), and 0003 adds EXPLAIN
(BATCHES). I'd love to hear people's thoughts around TupleBatch
structure added in 0002. I thought about making it a separate patch so
that 0002 will still populate the single ScanState.ss_scanTupleSlot,
but that means we'd still have to call the TAM callback to populate
the tuple in the TAM's batch struct into the slot, defeating the whole
point. With TupleBatch, you have executor_batch_rows number of slots
which are filled in one TAM callback (materialize_all) call. So I
decided to keep the TupleBatch and related things in 0002.

For scans without quals, batching shows 20-30% improvement with no
visible regressions when batching is disabled (batch_rows=0):

SELECT * FROM t LIMIT n (no qual)

  Rows      Master    batch=0   %diff    batch=64  %diff
  ------   --------   -------   -----    --------  -----
  1M        12.42 ms  11.96 ms   3.7%     8.56 ms  31.0%
  3M        38.95 ms  38.92 ms   0.1%    28.59 ms  26.6%
  10M      153.64 ms 150.28 ms   2.2%   112.95 ms  26.5%

(%diff: positive = faster than master, negative = slower)

Patches 0004-0005 add batched qual evaluation and are more
experimental (see below on why 0005 exists). For quals referencing
early columns, the improvement is significant:

  SELECT * FROM t WHERE a = 0 ... OFFSET n (qual on 1st col)

  Rows      Master    batch=64  %diff
  ------   --------   --------  -----
  1M        30.19 ms  15.55 ms  48.5%
  3M        92.47 ms  50.01 ms  45.9%
  10M      325.58 ms 211.83 ms  34.9%

However, for quals on later columns (e.g., 15th), batching provides no
benefit - deformation dominates and batching doesn't help:

  SELECT * FROM t WHERE o = 0 ... OFFSET n (qual on 15th col)

  Rows      Master    batch=64  %diff
  ------   --------   --------  -----
  1M        44.14 ms  44.56 ms  -0.9%
  3M       133.89 ms 137.77 ms  -2.9%
  10M      503.33 ms 528.88 ms  -5.1%

I don't have a satisfactory explanation for why batching doesn't help
the deform-heavy case at all. One would expect at least some benefit
from reduced per-tuple overhead, but that's not materializing.

I've also been struggling to understand why 0004 affects the per-tuple
path even when batch_rows=0. For quals with 0% selectivity (all rows
fail the qual), perf shows ExecInterpExpr is noticeably hotter with
the patched code compared to master, even though batching is disabled:

  SELECT * FROM t WHERE a = 0 ... OFFSET n (0% selectivity)

  Rows      Master    batch=0   %diff    batch=64  %diff
  ------   --------   -------   -----    --------  -----
  1M        24.37 ms  28.67 ms -17.6%    12.46 ms  48.9%
  3M        73.95 ms  85.07 ms -15.0%    41.64 ms  43.7%
  10M      287.63 ms 316.81 ms -10.1%   188.01 ms  34.6%

Compare that to 100% selectivity (all rows pass), where there's no regression:

  SELECT * FROM t WHERE a > 0 ... OFFSET n (100% selectivity)

  Rows      Master    batch=0   %diff    batch=64  %diff
  ------   --------   -------   -----    --------  -----
  1M        29.44 ms  29.10 ms   1.2%    16.61 ms  43.6%
  3M        91.22 ms  90.28 ms   1.0%    54.10 ms  40.7%
  10M      360.77 ms 331.25 ms   8.2%   224.00 ms  37.9%

I tried moving batch opcodes to a separate interpreter (0005) thinking
it might be register pressure or jump table effects from adding cases
to ExecInterpExpr's switch. With 0005, the generated assembly for
ExecInterpExpr looks identical to master (same stack frame size, same
epilogue), yet the performance still differs. Specifically, the ldp
instruction in the function epilogue shows 53% hotness in patched vs
35% in master. We still need placeholder entries in the dispatch
table, so it's unclear if this fully isolates the per-tuple path. I'll
continue looking at perf, but I feel like at a bit of a loss here and
would appreciate any insights.

Other changes worth noting:

- I removed the BatchVector intermediate representation that copied
Datums into columnar arrays before qual evaluation (it used to be in
the batched qual patch 0004). Now quals access batch slots' tts_values
directly. This simplifies the code and the copy overhead wasn't paying
off. If we pursue serious vectorization later, this may need to be
revisited, but removing it doesn't degrade performance.

--
Thanks, Amit Langote

---

From: Amit Langote <[email-masked]>
Date: Thu, 29 Jan 2026 11:04:17 +0100

>   Rows      Master    batch=0   %diff    batch=64  %diff

Oops, I meant SELECT * FROM t LIMIT 1 OFFSET n (no qual).

-- 
Thanks, Amit Langote

---

From: Junwang Zhao <[email-masked]>
Date: Sun, 1 Feb 2026 22:49:31 +0800

Hi Amit,

>   Rows      Master    batch=0   %diff    batch=64  %diff

Here are some comments for v5:

0001:

+/*
+ * heap_scan_begin_batch
+ *
+ * Allocate a HeapBatch with space for 'maxitems' tuple headers. No pin is
+ * taken here. Memory is allocated under the scan's memory context.
+ */
+void *
+heap_begin_batch(TableScanDesc sscan, int maxitems)

+/*
+ * heap_scan_end_batch
+ *
+ * Release any outstanding pin and free the batch allocations. Caller will
+ * not use 'am_batch' after this point.
+ */
+void
+heap_end_batch(TableScanDesc sscan, void *am_batch)

These function names are not consistent with comments.

0002:

+/*
+ * heap_scan_materialize_all
+ *
+ * Bind all tuples of the current batch into 'slots'. We bind the
+ * HeapTupleData header that points into the pinned page. No per-row copy.
+ */
+void
+heap_materialize_batch_all(void *am_batch, TupleTableSlot **slots, int n)

ditto.

+const TupleBatchOps *
+table_batch_callbacks(Relation relation)
+{
+ if (relation->rd_tableam)
+ return relation->rd_tableam->batch_callbacks(relation);
+ elog(ERROR, "relation does not support TupleBatch operations");
+}

Is there any chance this batch_callbacks can be NULL? In that case it
can cause a segfault. I felt changing to
if (relation->rd_tableam && relation->rd_tableam->batch_callbacks)
should be more robust, but then I found table_slot_callbacks follow
the same pattern, so this shouldn't be a problem.

0003:

+++ b/src/include/executor/execBatch.h
@@ -13,6 +13,8 @@
#ifndef EXECBATCH_H
#define EXECBATCH_H
+#include <limits.h>

I guess the reason for including this header is because of the use
of INT_MAX, so maybe put that line into execBatch.c?

-- 
Regards
Junwang Zhao

---

From: "cca5507" <[email-masked]>
Date: Tue, 3 Feb 2026 21:30:15 +0800

Hi,

Some comments for v5:

0001
====

1) heap_begin_batch()

```
	/* Single allocation for HeapBatch header + tupdata array */
	alloc_size = sizeof(HeapBatch) + sizeof(HeapTupleData) * maxitems;
	hb = palloc(alloc_size);
	hb->tupdata = (HeapTupleData *) ((char *) hb + sizeof(HeapBatch));
```

Do we need a MAXALIGN() here to avoid unaligned access? Something like this:

```
	/* Single allocation for HeapBatch header + tupdata array */
	alloc_size = MAXALIGN(sizeof(HeapBatch)) + sizeof(HeapTupleData) * maxitems;
	hb = palloc(alloc_size);
	hb->tupdata = (HeapTupleData *) ((char *) hb + MAXALIGN(sizeof(HeapBatch)));
```

Or how about just using zero-length array:

```
typedef struct HeapBatch
{
	Buffer			buf;
	int				maxitems;
	int				nitems;
	HeapTupleData	tupdata[FLEXIBLE_ARRAY_MEMBER];
} HeapBatch;

// and
hb = palloc(offsetof(HeapBatch, tupdata) + sizeof(HeapTupleData) * maxitems);
```

2) pgstat_count_heap_getnext_batch()

```
#define pgstat_count_heap_getnext_batch(rel, n)						\
	do {															\
		if (pgstat_should_count_relation(rel))						\
			(rel)->pgstat_info->counts.tuples_returned += n;		\
	} while (0)
```

"+= n" -> "+= (n)", just like pgstat_count_index_tuples().

0002
====

1) TupleBatchCreate()

```
	/* Single allocation for TupleBatch + inslots + outslots arrays */
	alloc_size = sizeof(TupleBatch) + 2 * sizeof(TupleTableSlot *) * capacity;
	b = palloc(alloc_size);
	inslots = (TupleTableSlot **) ((char *) b + sizeof(TupleBatch));
	outslots = (TupleTableSlot **) ((char *) b + sizeof(TupleBatch) +
		sizeof(TupleTableSlot *) * capacity);
```

Do we need a MAXALIGN() here to avoid unaligned access?

2) TupleBatchReset()

```
	for (int i = 0; i < b->maxslots; i++)
	{
		ExecClearTuple(b->inslots[i]);
		if (drop_slots)
			ExecDropSingleTupleTableSlot(b->inslots[i]);
	}
```

ExecDropSingleTupleTableSlot() will call ExecClearTuple(), so ExecClearTuple() will be
called twice if drop_slots is true, I think we can avoid this.

3) ScanCanUseBatching()

In heap_beginscan(), we may disable page-at-a-time mode:

```
	/*
	 * Disable page-at-a-time mode if it's not a MVCC-safe snapshot.
	 */
	if (!(snapshot && IsMVCCSnapshot(snapshot)))
		scan->rs_base.rs_flags &= ~SO_ALLOW_PAGEMODE;
```

It seems that ScanCanUseBatching() didn't consider this.

4) struct TupleBatch

```
	struct TupleTableSlot **inslots; /* slots for tuples read "into" batch */
	struct TupleTableSlot **outslots; /* slots for tuples going "out of"
									   * batch */
	struct TupleTableSlot **activeslots;
```

I think we can remove the word "struct".

5) ExecScanExtendedBatchSlot()

```
		/* Get next input slot from current batch, or refill */
		if (!TupleBatchHasMore(b))
		{
			if (!accessBatchMtd(node))
				return NULL;
		}
```

I think we cannot just return NULL here, see comments in ExecScanExtended():

```
		/*
		 * if the slot returned by the accessMtd contains NULL, then it means
		 * there is nothing more to scan so we just return an empty slot,
		 * being careful to use the projection result slot so it has correct
		 * tupleDesc.
		 */
		if (TupIsNull(slot))
		{
			if (projInfo)
				return ExecClearTuple(projInfo->pi_state.resultslot);
			else
				return slot;
		}
```

And why not just write this function like ExecScanExtended() and ExecScanFetch()?

--
Regards,
ChangAo Chen

---

From: Junwang Zhao <[email-masked]>
Date: Tue, 3 Feb 2026 23:54:48 +0800

> Do we need a MAXALIGN() here to avoid unaligned access? Something like this:

TBH I don't think this single allocation helps too much, it's not on
the hot path,
but makes the code harder to read ;(

-- 
Regards
Junwang Zhao

---

From: Amit Langote <[email-masked]>
Date: Tue, 24 Mar 2026 09:59:33 +0900

Hi,

Here is a significantly revised version of the patch series. A lot has
changed since the January submission, so I want to summarize the
design changes before getting into the patches.  I think it does
address the points in the two reviews that landed since v5 but maybe a
bunch of points became moot after my rewrite of the relevant portions
(thanks Junwang and ChangAo for the review in any case).

At this point it might be better to think of this as targeting v20,
except that if there is review bandwidth in the remaining two weeks
before the v19 feature freeze, the rs_vistuples[] change described
below as a standalone improvement to the existing pagemode scan path
could be considered for v19, though that too is an optimistic
scenario.

It is also worth noting that Andres identified a number of
inefficiencies in the existing scan path in:

Re: unnecessary executor overheads around seqscans
https://postgr.es/m/xzflwwjtwxin3dxziyblrnygy3gfygo5dsuw6ltcoha73ecmnf%40nh6nonzta7kw

that are worth fixing independently of batching. Some of those fixes
may be better pursued first, both because they benefit all scan paths
and because they would make batching's gains more honest.

Separately, after looking at the previous version, Andres pointed out
offlist two fundamental issues with the patch's design:

* The heapam implementation (in a version of the patch I didn't post
to the thread) duplicated heap_prepare_pagescan() logic in a separate
batch-specific code path, which is not acceptable as changes should
benefit the existing slot interface too.  Code duplication is not good
either from a future maintainability aspect. The v5 version of that
code is not great in that respect either; it instead duplicated
heapggettup_pagemode() to slap batching on it.

* Allocating executor_batch_rows slots on the executor side to receive
rows from the AM adds significant overhead for slot initialization and
management, and for non-row-organized AMs that do not produce
individual rows at all, those slots would never be meaningfully
populated.

In any case, he just wasn't a fan of the slot-array approach the
moment I mentioned it. The previous version had two slot arrays,
inslots and outslots, of TTSOpsHeapTuple type (not
TTSOpsBufferHeapTuple because buffer pins were managed by the batch
code, which has its own modularity/correctness issues), populated via
a materialize_all callback. A batch qual evaluator would copy
qualifying tuples into outslots, with an activeslots pointer switching
between the two depending on whether batch qual evaluation was used.

The new design addresses both issues and differs from the previous
version in several other ways:

 * Single slot instead of slot arrays: there is a single
TupleTableSlot, reusing the scan node's ss_ScanTupleSlot whose type
was already determined by the AM via table_slot_callbacks().  The slot
is re-pointed to each HeapTuple in the current buffer page via a new
repoint_slot AM callback, with no materialization or copying.  Tuples
are returned one by one from the executor's perspective, but the AM
serves them in page-sized batches from pre-built HeapTupleData
descriptors in rs_vistuples[], avoiding repeated descent into heapam
per tuple.  This is heapam's implementation of the batch interface;
there is no intention to force other AMs into the same row-oriented
model.

 * Batch qual evaluator not included: with the single-slot model,
quals are evaluated per tuple via the existing ExecQual path after
each repoint_slot call.  A natural next step would be a new opcode
(EEOP) that calls repoint_slot() internally within expression
evaluation, allowing ExecQual to advance through multiple tuples from
the same batch without returning to the scan node each time, with qual
results accumulated in a bitmask in ExprState.  The details of that
will be worked out in a follow-on series.

* heapgettup_pagemode_batch() gone: patch 0001 (described below) makes
HeapScanDesc store full HeapTupleData entries in rs_vistuples[], which
allows heap_getnextbatch() to simply advance a slice pointer into that
array without any additional copying or re-entering heap code, making
a separate batch-specific scan function unnecessary.

 * TupleBatch renamed to RowBatch: "row batch" is more natural
terminology for this concept and also consistent with how similar
abstractions are named in columnar and OLAP systems.

 * AM callbacks now take RowBatch directly: previously
heap_getnextbatch() returned a void pointer that the executor would
store into RowBatch.am_payload, because only the executor knew the
internals of RowBatch.  Now the AM receives RowBatch directly as a
parameter and can populate it without the executor acting as an
intermediary.  This is also why RowBatch is introduced in its own
patch ahead of the AM API addition, so the struct definition is
available to both sides.

Patch 0001 changes rs_vistuples[] to store full HeapTupleData entries
instead of OffsetNumbers, as a standalone improvement to the existing
pagemode scan path. Measured on a pg_prewarm'd  (also vaccum freeze'd
in the all-visible case) table with 1M/5M/10M rows:

  query                           all-visible      not-all-visible
  count(*)                        -0.2% to +0.9%   -0.4% to +0.5%
  count(*) WHERE id % 10 = 0     -1.1% to +3.4%   +0.2% to +1.5%
  SELECT * LIMIT 1 OFFSET N      -2.2% to -0.6%   -0.9% to +6.6%
  SELECT * WHERE id%10=0 LIMIT   -0.8% to +3.9%   +0.9% to +9.6%

No significant regression on either page type. The structural
improvement is most visible on not-all-visible pages where
HeapTupleSatisfiesMVCCBatch() already reads every tuple header during
visibility checks, so persisting the result into rs_vistuples[]
eliminates the downstream re-read (in heapgettupe_pagemode()) with no
measurable overhead.  That said, these numbers are somewhat noisy on
my machine.  Results on other machines would be welcome.

Patches 0002-0005 add the RowBatch infrastructure, the batch AM API
and heapam implementation including seqscan variants that use the new
scan_getnextbatch() API, and EXPLAIN (ANALYZE, BATCHES) support,
respectively. With batching enabled (executor_batch_rows=300,
~MaxHeapTuplesPerPage):

  query                           all-visible    not-all-visible
  count(*)                        +11 to +15%    +9 to +13%
  count(*) WHERE id % 10 = 0     +6 to +11%     +10 to +14%
  SELECT * LIMIT 1 OFFSET N      +16 to +19%    +16 to +22%
  SELECT * WHERE id%10=0 LIMIT   +8 to +10%     +8 to +13%

With executor_batch_rows=0, results are within noise of master across
all query types and sizes, confirming no regression from the
infrastructure changes themselves.  The not-all-visible results tend
to show slightly higher gains than the all-visible case. This is
likely because the existing heapam code is more optimized for the
all-visible path, so the not-all-visible path, which goes through
HeapTupleSatisfiesMVCCBatch() for per-tuple visibility checks, has
more headroom that batching can exploit.

Setting aside the current series for a moment, there are some broader
design questions worth raising while we have attention on this area.
Some of these echo points Tomas raised in his first reply on this
thread, and I am reiterating them deliberately since I have not
managed to fully address them on my own or I simply didn't need to for
the TAM-to-scan-node batching and think they would benefit from wider
input rather than just my own iteration.

We should also start thinking about other ways the executor can
consume batch rows, not always assuming they are presented as
HeapTupleData. For instance, an AM could expose decoded column arrays
directly to operators that can consume them, bypassing slot-based
deform entirely, or a columnar AM could implement scan_getnextbatch by
decoding column strips directly into the batch without going through
per-tuple HeapTupleData at all. Feedback on whether the current
RowBatch design and the choices made in the scan_getnextbatch and
RowBatchOps API make that sort of thing harder than it needs to be
would be appreciated. For example, heapam's implementation of
scan_getnextbatch uses a single TTSOpsBufferHeapTuple slot re-pointed
to HeapTupleData entries one at a time via repoint_slot in
RowBatchHeapOps. That works for heapam but a columnar AM could
implement scan_getnextbatch to decode column strips directly into
arrays in the batch, with no per-row repoint step needed at all. Any
adjustments that would make RowBatch more AM-agnostic are worth
discussing now before the design hardens.

There are also broader open questions about how far the batch model
can extend beyond the scan node. Qual pushdown into the AM has been
discussed in nearby threads and would be one way to allow expression
evaluation to happen before data reaches the executor proper, though
that is a separate effort. For the purposes of this series, expression
evaluation still happens in the executor after scan_getnextbatch
returns. If the scan node does not project, the buffer heap slot is
passed directly to the parent node, which calls slot callbacks to
deform as needed. But once a node above projects, aggregates, or
joins, the notion of a page-sized batch from a single AM loses its
meaning and virtual slots take over. Whether RowBatch is usable or
meaningful beyond the scan/TAM boundary in any form, and whether the
core executor will ever have non-HeapTupleData batch consumption paths
or leave that entirely to extensions, are open questions worth
discussing.

For RowBatch to eventually play the role that TupleTableSlot plays for
row-at-a-time execution, something inside it would need to serve as
the common currency for batch data, analogous to TupleTableSlot's
datum/isnull arrays. Column arrays are the obvious direction, but even
that leaves open the question of representation. PostgreSQL's Datum is
a pointer-sized abstraction that boxes everything, whereas vectorized
systems use typed packed arrays of native types with validity
bitmasks, which is a significant part of why tight vectorized loops
are fast there. Whether column arrays of Datum would be good enough,
or whether going further toward typed packed arrays would be necessary
to get meaningful vectorization, is a deeper design question that this
series deliberately does not try to answer.

Even though the focus is on getting batching working at the scan/TAM
boundary first, thoughts on any of these points would be welcome.

--
Thanks, Amit Langote

---

From: Amit Langote <[email-masked]>
Date: Mon, 6 Apr 2026 21:02:35 +0900

> boundary first, thoughts on any of these points would be welcome.

Rebased.

-- 
Thanks, Amit Langote