On Wed, Jun 4, 2014 at 2:08 AM, Robert Haas <robertmh...@gmail.com> wrote: > On Sun, Jun 1, 2014 at 3:26 AM, Amit Kapila <amit.kapil...@gmail.com> wrote:
> > I also think it's possible to have similar optimization for hash index > > incase it has to spool the tuple for sorting. > > > > In function hashbuildCallback(), when buildstate->spool is true, we > > can avoid to form index tuple. To check for nulls before calling > > > > _h_spool(), we can traverse the isnull array. > > Hmm, that might work. Arguably it's less efficient, but on the other > hand if it avoids forming the tuple sometimes it might be MORE > efficient. And anyway the difference might not be enough to matter. Considering the fact that for hash indexes, this optimization would only get triggered for large indexes (atleast greater than shared buffer's), I agree that it will not be as useful as it will be for btree indexes. The another minor advantage could have been that we can remove tuplesort_putindextuple() from code, if this optimization is done for hash indexes. However we can leave it as it is for now as there doesn't seem to be any gain by doing so. You seem to have removed puttuple_common() call from function tuplesort_putindextuple(), is there any reason for doing so? Apart from that patch looks good to me. Below performance data on various size of indexes shows that this patch can improve performance upto ~7%. The performance difference becomes lesser when the index size is too big and I think that is probably due to the reason that we have to write all the data at end of operation, so if the data is big the improvement is not shown due to large I/O. The performance improvement is shown considering median value of 3 runs and time is taken by enabling \timing option of psql. Performance Data --------------------------- Configuration: IBM POWER-7 16 cores, 64 hardware threads RAM = 64GB shared_buffers=8GB pgbench_accounts No. of Records -10million Index - on integer Operation - Reindex Master 16043.500 ms 16058.723 ms 15941.057 ms Patch 15525.054 ms 15551.935 ms 15492.879 ms Perf Improvement: 3.43% pgbench_accounts No. of Records -30million Index - on integer Operation - Reindex Master 51258.338 ms 50520.328 ms 50562.022 ms Patch 49610.710 ms 49302.571 ms 49301.390 ms Perf Improvement: 2.41% table (c1 int, c2 char(10)) No. of records = 300,000 Index - on char(10) Operation - Reindex Master 443.584 ms 444.798 ms 452.888 ms Patch 421.554 ms 430.528 ms 447.558 ms Performance Improvement: 3.2% table (c1 int, c2 char(10)) No. of records = 500,000 Index - on char(10) Operation - Reindex Master 663.621 ms 661.299 ms 657.754 ms Patch 652.325 ms 644.782 ms 643.218 ms Performance Improvement: 2.5% table (c1 int, c2 char(10)) No. of records = 1000,000 Index - on char(10) Operation - Reindex Master 16554.076 ms 16686.528 ms 16571.129 ms Patch 16556.852 ms 16513.543 ms 16610.615 ms Performance Improvement: less than 1% table (c1 int, c2 char(20)) No. of records = 300,000 Index - on char(20) Operation - Reindex Master 429.670 ms 441.445 ms 411.539 ms Patch 401.801 ms 412.716 ms 395.002 ms Performance Improvement: 6.48% table (c1 int, c2 char(20)) No. of records = 500,000 Index - on char(20) Operation - Reindex Master 724.541 ms 731.582 ms 704.934 ms Patch 686.004 ms 677.361 ms 686.915 ms Performance Improvement: 5.31% table (c1 int, c2 char(20)) No. of records = 1000,000 Index - on char(20) Operation - Reindex Master 20728.758 ms 20665.289 ms 20656.375 ms Patch 20594.022 ms 20617.383 ms 20628.181 ms Performance Improvement: 0.2% Apart from this, I have taken data for unlogged tables and much larger indexes and all the data shows similar results as above. So I think above data is quite good representation of value for this patch, however if you or anybody else still feels that more data is required for any other configuration, please do let me know about the same. With Regards, Amit Kapila. EnterpriseDB: http://www.enterprisedb.com
