Hash index pages are stored in sorted order, but we don't prepare the
data correctly.
We sort the data as the first step of a hash index build, but we
forget to sort the data by hash as well as by hash bucket. This causes
the normal insert path to do extra pushups to put the data in the
correct sort order on each page, which wastes effort.
Adding this patch makes a CREATE INDEX about 8-9% faster, on an unlogged table.
Thoughts?
Aside:
I'm not very sure why tuplesort has private code in it dedicated to
hash indexes, but it does.
Even more strangely, the Tuplesortstate fixes the size of max_buckets
at tuplesort_begin() time rather than tuplesort_performsort(), forcing
us to estimate the number of tuples ahead of time rather than using
the exact number. Next trick would be to alter the APIs to allow exact
values to be used for sorting, which would allow page at a time
builds.
--
Simon Riggs http://www.EnterpriseDB.com/
