Content-Type: text/plain;
	charset=us-ascii
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3826.700.81\))
Subject: Re: tuple radix sort
From: Chao Li <li.evan.chao@gmail.com>
In-Reply-To: 
 <CAApHDvpGd-cHSr5OZ7_JpoTSWDBZAvAkarbkJPd0J3FW9zvFGw@mail.gmail.com>
Date: Thu, 30 Oct 2025 13:43:34 +0800
Cc: John Naylor <johncnaylorls@gmail.com>,
 PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>,
 Peter Geoghegan <pg@bowt.ie>
Content-Transfer-Encoding: quoted-printable
Message-Id: <2E6154C8-CFF8-4144-96AA-30CFB3DCF483@gmail.com>
References: 
 <CANWCAZYzx7a7E9AY16Jt_U3+GVKDADfgApZ-42SYNiig8dTnFA@mail.gmail.com>
 <17F6CDF1-2D93-4C38-B0F2-D52B51F4D167@gmail.com>
 <CANWCAZbrKH-sDvzqb5z8BEofpXyXuN42UCRzoQ8wT3CiqPATFg@mail.gmail.com>
 <98232211-F000-45E6-AEE7-718834DDE277@gmail.com>
 <CAApHDvpGd-cHSr5OZ7_JpoTSWDBZAvAkarbkJPd0J3FW9zvFGw@mail.gmail.com>
To: David Rowley <dgrowleyml@gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/2E6154C8-CFF8-4144-96AA-30CFB3DCF483%40gmail.com>
Precedence: bulk


> On Oct 30, 2025, at 13:01, David Rowley <dgrowleyml@gmail.com> wrote:
>=20
> On Thu, 30 Oct 2025 at 16:46, Chao Li <li.evan.chao@gmail.com> wrote:
>>> On Oct 30, 2025, at 11:40, John Naylor <johncnaylorls@gmail.com> =
wrote:
>>> Are you by chance running with asserts on? It's happened before, so =
I
>>> have to make sure. That makes a big difference here because I =
disabled
>>> diversion thresholds in assert builds so that regression tests (few
>>> cases with large inputs) cover the paths I want, in addition to my
>>> running a standalone stress test.
>>=20
>> Yes, assert is always enabled in my sandbox. I can disable assert and =
rerun the test later.
>=20
> Never expect anything meaningful to come from running performance
> tests on Assert builds. You should always be rebuilding without
> Asserts before doing performance tests.
>=20

Sure, good to learn. Actually I am very new to PG development, so any =
guidance is greatly appreciated.

I just made a distclean, then configure without any parameter. Now, the =
overall execution time reduced ~10% than with asserts. With the low =
cardinality data, off and on are very close:

```
evantest=3D# set wip_radix_sort =3D 'off';
Time: 0.206 ms
evantest=3D# select * from test_multi order by category, name;
Time: 5070.277 ms (00:05.070)
evantest=3D# select * from test_multi order by category, name;
Time: 5158.748 ms (00:05.159)
evantest=3D# select * from test_multi order by category, name;
Time: 5072.708 ms (00:05.073)

evantest=3D# set wip_radix_sort =3D 'on';
Time: 0.177 ms
evantest=3D# select * from test_multi order by category, name;
Time: 4992.516 ms (00:04.993)
evantest=3D# select * from test_multi order by category, name;
Time: 5145.361 ms (00:05.145)
evantest=3D# select * from test_multi order by category, name;
Time: 5101.800 ms (00:05.102)

evantest=3D# \o
evantest=3D# show work_mem;
 work_mem
----------
 1GB
(1 row)

Time: 0.186 ms
evantest=3D# explain select * from test_multi order by category, name;
                                QUERY PLAN
=
--------------------------------------------------------------------------=
-
 Sort  (cost=3D122003.84..124503.84 rows=3D1000000 width=3D69)
   Sort Key: category, name
   ->  Seq Scan on test_multi  (cost=3D0.00..22346.00 rows=3D1000000 =
width=3D69)
(3 rows)
```

And with high cardinality test data, on has a big win:
```
evantest=3D# set wip_radix_sort =3D 'off';
Time: 0.174 ms
evantest=3D# select * from test_multi order by category, name;
Time: 353.702 ms
evantest=3D# select * from test_multi order by category, name;
Time: 375.549 ms
evantest=3D# select * from test_multi order by category, name;
Time: 367.967 ms
evantest=3D# set wip_radix_sort =3D 'on';
Time: 0.147 ms
evantest=3D# select * from test_multi order by category, name;
Time: 279.537 ms
evantest=3D# select * from test_multi order by category, name;
Time: 278.114 ms
evantest=3D# select * from test_multi order by category, name;
Time: 284.273 ms
```

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