Re: Use BumpContext contexts for TupleHashTables' tablecxt

On Mon, 27 Oct 2025 at 10:46, David Rowley <dgrowleyml(at)gmail(dot)com> wrote:
>
> On Mon, 27 Oct 2025 at 09:55, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> >
> > [ starting a new thread to keep this separate from the estimation
> > issue ]
> >
> > I looked at the callers of BuildTupleHashTable, and realized that
> > (a) every one of them can use a BumpContext for the "tablecxt",
> > and (b) Jeff Davis already noticed that for nodeAgg.c, in commit
> > cc721c459. So we have precedent for the idea working. Here's
> > a fleshed-out patch to fix the remaining callers.
>
> Yeah, this should give a decent performance improvement for larger workloads.

I just did a quick test of this with the best-case I could imagine,
where all rows are filtered, thus reducing the additional overhead of
going into other nodes. Patched came out about 9% faster than master
(without MEMORY_CONTEXT_CHECKING).

Test 1)
Setup 1 million rows:
create table t1 as select generate_Series(1,1_000_000)a;
vacuum freeze analyze t1;
set work_mem = '1GB';
set jit=0;
\timing on

Master:
select * from t1 except select * from t1;
Time: 343.660 ms
Time: 341.049 ms
Time: 352.762 ms

Patched:
select * from t1 except select * from t1;
Time: 312.576 ms
Time: 317.629 ms
Time: 323.980 ms (+8.73%)

Test 2)
Setup 10 million rows:
create table t2 as select generate_Series(1,10_000_000)a;
vacuum freeze analyze t2;
set work_mem = '1GB';
set jit=0;
\timing on

Master:
select * from t2 except select * from t2;
Time: 4737.736 ms
Time: 4660.170 ms
Time: 4749.984 ms

Patched:
select * from t2 except select * from t2;
Time: 4307.161 ms
Time: 4339.134 ms
Time: 4279.902 ms (+9.45%)

David