Content-Type: text/plain;
	charset=utf-8
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3826.700.81\))
Subject: Re: Making jsonb_agg() faster
From: Chao Li <li.evan.chao@gmail.com>
In-Reply-To: <354608.1762195230@sss.pgh.pa.us>
Date: Wed, 5 Nov 2025 17:14:40 +0800
Cc: jian he <jian.universality@gmail.com>,
 pgsql-hackers@lists.postgresql.org
Content-Transfer-Encoding: quoted-printable
Message-Id: <8A521EB7-2716-4E01-8505-98755B78D8EF@gmail.com>
References: <1060917.1753202222@sss.pgh.pa.us>
 <CACJufxHuE-P0s1PxiRja26ZMjKUZiCAgWa1jedswo94SFzjXMg@mail.gmail.com>
 <617666.1755277022@sss.pgh.pa.us>
 <CACJufxGHLYtvOAB7uA59C9Lz+rDPta5Egiii-B5opsZbw6eDDQ@mail.gmail.com>
 <654628.1755297629@sss.pgh.pa.us> <2129790.1755889894@sss.pgh.pa.us>
 <68A1061C-81A5-46AA-AFA9-48294908254C@gmail.com>
 <2613D418-67E0-4DD8-BDA6-AB1BB04DB1A2@gmail.com>
 <2466173.1756498180@sss.pgh.pa.us> <163563.1762117009@sss.pgh.pa.us>
 <AAFBCC62-1295-4084-8CBC-2026C9075A3C@gmail.com>
 <354608.1762195230@sss.pgh.pa.us>
To: Tom Lane <tgl@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/8A521EB7-2716-4E01-8505-98755B78D8EF%40gmail.com>
Precedence: bulk


> On Nov 4, 2025, at 02:40, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>=20
> Chao Li <li.evan.chao@gmail.com> writes:
>>> On Nov 3, 2025, at 04:56, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>> PFA v3, rebased over 8a27d418f, no substantive changes whatever.
>=20
>> I am on vacation this week, I only find a hour in the evening and did =
an eyeball review without actually tracing and testing this patch set.
>=20
> Thanks for looking at it!
>=20
>> In elsewhere of the patch, you all use =E2=80=9CJsonbInState ps =3D =
{0}=E2=80=9D to do the initialization, only this place uses memset(). =
Can we keep consistent and use {0} here also. I see there is a =
=E2=80=9Ccontinue=E2=80=9D and a =E2=80=9Cbreak=E2=80=9D before the =
memset(), maybe you want to avoid unnecessary initialization. I guess =
that is a tiny saving, but if you think that saving is worthwhile, I=E2=80=
=99m fine with keeping the current code.
>=20
> I intentionally made the 0001 patch as mechanical as possible,
> and in particular did not second-guess existing decisions about
> whether to initialize a variable in the declaration or later.
> So I'm not excited about doing it differently in this one place.
> There are some other memsets in the same area that could also
> be replaced by "=3D {0}" if we cared to, but that seems like
> material for a separate cleanup patch.
>=20
>> Instead of hard-coding 12, can we use "sizeof(TimeTzADT)=E2=80=9D for =
better readability?
>=20
> No, because that's not the same :-(.  sizeof() would produce 16,
> on most machines anyway, thanks to alignment.  But 12 is the
> type's size according to pg_type.
>=20
>> You left two =E2=80=9Cugly hack=E2=80=9D comments. Maybe add a short =
description for why they are hack and what can be improved in the =
future.
>=20
> It's the same ugly hack as before, I just formatted the code more
> legibly ... I didn't stop to look at whether there's a better way to
> do it.  Again, that seems like material for a different patch.
>=20
> regards, tom lane

I got some time today, so I ran some performance tests, which show me a =
significant performance improvement from this patch.

To run the test, I disabled asserts and built with -O2. The tests ran on =
a MacBook M4.

To prepare for some data:
```
DROP TABLE IF EXISTS jtest;
CREATE TABLE jtest AS SELECT g AS id, (g % 1000) AS grp, g AS ival, =
(g::text || '_str') AS sval, now() + (g || ' seconds')::interval AS =
tsval FROM generate_series(1, 5_000_000) g;
VACUUM ANALYZE jtest;
```

Then I ran the following tests against both master and a patched branch:

1. Jsonb_agg

Patched:
```
evantest=3D# SELECT grp, jsonb_agg(jsonb_build_object('id', id, 'ival', =
ival, 'sval', sval, 'tsval', tsval)) FROM jtest GROUP BY grp;
Time: 5810.921 ms (00:05.811)
evantest=3D# SELECT grp, jsonb_agg(jsonb_build_object('id', id, 'ival', =
ival, 'sval', sval, 'tsval', tsval)) FROM jtest GROUP BY grp;
Time: 5904.465 ms (00:05.904)
evantest=3D# SELECT grp, jsonb_agg(jsonb_build_object('id', id, 'ival', =
ival, 'sval', sval, 'tsval', tsval)) FROM jtest GROUP BY grp;
Time: 5857.314 ms (00:05.857)
```

Master:
```
evantest=3D# SELECT grp, jsonb_agg(jsonb_build_object('id', id, 'ival', =
ival, 'sval', sval, 'tsval', tsval)) FROM jtest GROUP BY grp;
Time: 7310.648 ms (00:07.311)
evantest=3D# SELECT grp, jsonb_agg(jsonb_build_object('id', id, 'ival', =
ival, 'sval', sval, 'tsval', tsval)) FROM jtest GROUP BY grp;
Time: 7333.428 ms (00:07.333)
evantest=3D# SELECT grp, jsonb_agg(jsonb_build_object('id', id, 'ival', =
ival, 'sval', sval, 'tsval', tsval)) FROM jtest GROUP BY grp;
Time: 7257.666 ms (00:07.258)
```

Roughly 20% improvement.

2. Jsonb_object_agg

Patched:
```
evantest=3D# SELECT jsonb_object_agg(id, sval) FROM jtest;
Time: 767.258 ms
evantest=3D# SELECT jsonb_object_agg(id, sval) FROM jtest;
Time: 760.665 ms
evantest=3D# SELECT jsonb_object_agg(id, sval) FROM jtest;
Time: 772.326 ms
```

Master:
```
evantest=3D# SELECT jsonb_object_agg(id, sval) FROM jtest;
Time: 1298.433 ms (00:01.298)
evantest=3D# SELECT jsonb_object_agg(id, sval) FROM jtest;
Time: 1286.029 ms (00:01.286)
evantest=3D# SELECT jsonb_object_agg(id, sval) FROM jtest;
Time: 1283.624 ms (00:01.284)
```

Roughly 40% improvement.

3. To_jsonb

Patched:
```
evantest=3D# SELECT to_jsonb(ival), to_jsonb(sval), to_jsonb(tsval) FROM =
jtest;
Time: 2270.958 ms (00:02.271)
evantest=3D# SELECT to_jsonb(ival), to_jsonb(sval), to_jsonb(tsval) FROM =
jtest;
Time: 2313.483 ms (00:02.313)
evantest=3D# SELECT to_jsonb(ival), to_jsonb(sval), to_jsonb(tsval) FROM =
jtest;
Time: 2298.716 ms (00:02.299)
```

Master:
```
evantest=3D# SELECT to_jsonb(ival), to_jsonb(sval), to_jsonb(tsval) FROM =
jtest;
Time: 2490.771 ms (00:02.491)
evantest=3D# SELECT to_jsonb(ival), to_jsonb(sval), to_jsonb(tsval) FROM =
jtest;
Time: 2514.326 ms (00:02.514)
evantest=3D# SELECT to_jsonb(ival), to_jsonb(sval), to_jsonb(tsval) FROM =
jtest;
Time: 2509.924 ms (00:02.510)
```

Roughly 8% improvement.

So, overall, this patch has done a great performance improvement.

Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/