Message-ID: <fbf723d0b61503f2ae302d9ad37f0cb2ab5c1d10.camel@j-davis.com>
Subject: Re: Reduce TupleHashEntryData struct size by half
From: Jeff Davis <pgsql@j-davis.com>
To: David Rowley <dgrowleyml@gmail.com>
Cc: Heikki Linnakangas <hlinnaka@iki.fi>, pgsql-hackers@postgresql.org
Date: Mon, 17 Feb 2025 21:49:51 -0800
In-Reply-To: <ed493e174e269f981b4a0b3109e3dc6db647e06f.camel@j-davis.com>
References: <817d244237878cebdff0bc363718feaf49a1ea7d.camel@j-davis.com>
	 <0f3e7407-fb19-42c0-ad10-46eace6c1399@iki.fi>
	 <7c3224111d9edcc16e8e3c8242de5a82c4ee2a73.camel@j-davis.com>
	 <f05cff9e-c9e6-4452-83aa-9f6dd795f92d@iki.fi>
	 <7530bd8783b1a78d53a3c70383e38d8da0a5ffe5.camel@j-davis.com>
	 <b9cbf0219a9859dc8d240311643ff4362fd9602c.camel@j-davis.com>
	 <CAApHDvppeqw2pNM-+ahBOJwq2QmC0hOAGsmCpC89QVmEoOvsdg@mail.gmail.com>
	 <09b325921e50bc3a3217fb01d8eb512c89ee36f1.camel@j-davis.com>
	 <ed493e174e269f981b4a0b3109e3dc6db647e06f.camel@j-davis.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.44.4-0ubuntu2 
MIME-Version: 1.0
Archived-At: <https://www.postgresql.org/message-id/fbf723d0b61503f2ae302d9ad37f0cb2ab5c1d10.camel%40j-davis.com>
Precedence: bulk

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=3D'10GB';
  SET max_parallel_workers=3D0;
  SET max_parallel_workers_per_gather=3D0;
 =20

  --=C2=A0T1: 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=20

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