Re: Hash index build performance tweak from sorting - Mailing list pgsql-hackers

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