Re: HASH INDEX builds seems confused

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).

--
John Naylor
Amazon Web Services