Re: HASH INDEX builds seems confused

On Wed, Sep 23, 2026 at 4:07 AM John Naylor <johncnaylorls(at)gmail(dot)com> wrote:
>
> On Wed, Sep 23, 2026 at 9:20 AM Khoa Nguyen <kdnguyen9(dot)oss(at)gmail(dot)com> 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