On Tue, Sep 22, 2026 at 7:16 PM Amit Kapila <[email protected]> wrote: > > On Tue, Nov 11, 2025 at 11:27 AM John Naylor <[email protected]> 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
vondra_bench_sortmode.sh
Description: Bourne shell script
0001-hash_build_sort_mode.patch
Description: Binary data
persistence,type,rows,bucket_pages,overflow_pages,bitmap_pages,bytes,pretty unlogged,int,100000,512,0,1,4210688,4112 kB unlogged,bigint,100000,512,0,1,4210688,4112 kB unlogged,text,100000,512,0,1,4210688,4112 kB logged,int,100000,512,0,1,4210688,4112 kB logged,bigint,100000,512,0,1,4210688,4112 kB logged,text,100000,512,0,1,4210688,4112 kB
results_100k_r200.csv.gz
Description: GNU Zip compressed data
