Re: Hash index build performance tweak from sorting

Hi,

I did some simple benchmark with v2 and v3, using the attached script,
which essentially just builds hash index on random data, with different
data types and maintenance_work_mem values. And what I see is this
(median of 10 runs):

machine data type m_w_m master v2 v3
------------------------------------------------------------
i5 bigint 128MB 9652 9402 9669
32MB 9545 9291 9535
4MB 9599 9371 9741
int 128MB 9666 9475 9676
32MB 9530 9347 9528
4MB 9595 9394 9624
text 128MB 9755 9596 9897
32MB 9711 9547 9846
4MB 9808 9744 10024
xeon bigint 128MB 10790 10555 10812
32MB 10690 10373 10579
4MB 10682 10351 10650
int 128MB 11258 10550 10712
32MB 10963 10272 10410
4MB 11152 10366 10589
text 128MB 10935 10694 10930
32MB 10822 10672 10861
4MB 10835 10684 10895

Or, relative to master:

machine data type memory v2 v3
----------------------------------------------------------
i5 bigint 128MB 97.40% 100.17%
32MB 97.34% 99.90%
4MB 97.62% 101.48%
int 128MB 98.03% 100.11%
32MB 98.08% 99.98%
4MB 97.91% 100.31%
text 128MB 98.37% 101.46%
32MB 98.32% 101.40%
4MB 99.35% 102.20%
xeon bigint 128MB 97.82% 100.20%
32MB 97.03% 98.95%
4MB 96.89% 99.70%
int 128MB 93.71% 95.15%
32MB 93.70% 94.95%
4MB 92.95% 94.95%
text 128MB 97.80% 99.96%
32MB 98.62% 100.36%
4MB 98.61% 100.55%

So to me it seems v2 performs demonstrably better, v3 is consistently
slower - not only compared to v2, but often also to master.

Attached is the script I used and the raw results - this includes also
results for logged tables - the improvement is smaller, but the
conclusions are otherwise similar.

regards

--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company