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/
hash_sort_by_hash.v1.patch
Description: Binary data
hashtest.sql
Description: Binary data