Re: HASH INDEX builds seems confused

On Tue, Sep 22, 2026 at 7:16 PM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
>
> On Tue, Nov 11, 2025 at 11:27 AM John Naylor <johncnaylorls(at)gmail(dot)com> wrote:
> >
> > hashbuild() says:
> >
> > * If we just insert the tuples into the index in scan order, then
> > * (assuming their hash codes are pretty random) there will be no locality
> > * of access to the index, and if the index is bigger than available RAM
> > * then we'll thrash horribly. To prevent that scenario, we can sort the
> > * tuples by (expected) bucket number. However, such a sort is useless
> > * overhead when the index does fit in RAM. We choose to sort if the
> > * initial index size exceeds maintenance_work_mem, or the number of
> > * buffers usable for the index, whichever is less. (Limiting by the
> >
> > However, since commit e09d7a126 it's harder to believe sorts are ever
> > useless, since we then decided that sorts should have a more strict
> > sort order for the sake of sequential access. Further, d09dbeb9b built
> > upon that to remove wasteful binary search when inserting into the
> > page. Looking at some of the numbers in the linked threads, I wonder
> > if all test environments were actually hitting the sort path at all,
> > since you'd have to exceed m_w_m or s_b to take advantage. Unless I'm
> > missing something, it seems like we should just sort unconditionally.
> > That would be a nice simplification, and might speed up index builds
> > even when there's plenty of memory. (If I am in fact missing
> > something, maybe comments need updating)
> >
>
> +1. It seems worth pursuing this. We can establish the benefits by
> taking some performance data.
>
> > Now that I'm looking, I'm also wondering how hard it would be to have
> > datum1 contain both the bucket (high bits) and hash (lower bits),
> > since we can now count on Datums being 8 bytes on all platforms. It
> > might be harder in turn to hack things so that the appropriate sort
> > specialization could be applied (it'd need a fake sortKey at least),
> > but that would be a possible future project.
> >
>
> Yeah that also sounds worth exploring but what benefit are you
> expecting out of it?
>

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.

The test is vondra_bench.sh extended to toggle sort mode and use
pgstathashindex for sizing.

Attached:
0001-hash_build_sort_mode.patch - the test GUC force sorting on or off
results_100k_r200.csv.gz - results for 100k rows rep=200
vondra_bench_sortmode.sh - test script
index_sizes.csv - index sizes from results_100k_r200.csv.gz run