On Wed, Sep 23, 2026 at 4:07 AM John Naylor <[email protected]> wrote: > > On Wed, Sep 23, 2026 at 9:20 AM Khoa Nguyen <[email protected]> wrote: > > I measured the fits-in-RAM case that you are questioning and my result > > shows that sorting is not free. > > > > Result: sorting costs about 8-9ms: > > unlogged 84.36ms sorted -> 74.91ms unsorted -11.2% > > logged 118.10ms sorted -> 110.24ms unsorted -6.7% > > > > For this experiment, the server is patched with a test GUC to force > > sorting on or off, bypassing the questioned gated logic (num_buckets > > >= sort_threshold). > > > > The test ran 7200 (18 configurations × 2 modes × 200 reps) times. The > > following are the configurations: > > Logged and unlogged > > Column type int, bigint, text > > m_w_m: 4, 32, 128MB > > > > shared_buffers is kept constant 128MB through server configuration. > > Row count=100k, 512 buckets and ~4MB indexes measured using > > pgstathashindex on a separate untimed build. > > Thanks for testing! 4MB is awfully tiny -- does this finding scale if > m_w_m is 64MB or 1GB, and the index is 95% of that? (it'd be easier to > restrict it to only unlogged and ints for that).
I wanted to target an index size that is indisputably cache-friendly to see whether presorting still wins. However, I do agree with you that the result from a 4MB index is not usable in determining whether the presort gate is needed. I had to move to another machine so I reran the 4MB test for reference. Run configurations: 200 reps, force sort GUC, unlogged int. Fixed shared_buffers=64GB rows=100K mwm=4|32|128MB sorted/unsorted=1.243 Idx size at ~95% of mwm=64MB|1GB rows=1.8M idx_size=62MB mwm=64MB sorted/unsorted=1.21 rows=28.5M idx_size=979MB mwm=1GB sorted/unsorted=0.49 -Khoa
