On Wed, 2025-02-12 at 17:01 -0800, Jeff Davis wrote:
> In any case, it seems like we have agreement to switch to the Bump
> context, so I'll do another round of tests to see if there are any
> downsides, then clean it up and commit v7-0001.
Results for v7-0001 (switch to Bump Allocator for table entries).
Setup:
SET work_mem='10GB';
SET max_parallel_workers=0;
SET max_parallel_workers_per_gather=0;
-- T1: group size avg ~ 1.5
create table t1(i int8, j numeric);
insert into t1 select random(1,10000000),sqrt(g)
from generate_series(1,10000000) g;
vacuum freeze analyze t1; checkpoint;
-- T100: group size avg ~100
create table t100(i int8, j numeric);
insert into t100 select random(1,100000),sqrt(g)
from generate_series(1,10000000) g;
vacuum freeze analyze t100; checkpoint;
Q1: explain analyze select i, count(j) from THETABLE group by i;
t1 t100
master: 776MB / 4085ms 14MB / 1375ms
patch: 632MB / 4192ms 10MB / 1375ms
Q2: explain analyze select i, count(j), max(j), sum(j)
from THETABLE group by i;
t1 t100
master: 3280MB / 7817ms 54MB / 4194ms
patch: 3048MB / 8103ms 54MB / 4492ms
While it's a memory reduction in all cases, and a ~20% memory reduction
for by-value types; there appears to be some slowdown for by-reference
types -- 7% for Q2 when the group size is 100.
I profiled it and it seems to be spending more time in
advance_aggregates. Since I didn't change that code, the only
explanation I have for that is that with everything in one memory
context, the by-reference values end up near enough to the group key
and pergroup state to benefit from caching effects.
I'm not sure that explanation makes sense though, because even in the
old code where they are both in the same memory context, why would we
expect the transition values to be close to the grouping key or
pergroup state?
Regards,
Jeff Davis
