Re: Hash Indexes

On Sat, Dec 3, 2016 at 12:13 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Fri, Dec 2, 2016 at 1:54 AM, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
>>> I want to split when the average bucket
>>> contains 10 pages worth of tuples.
>>
>> oh, I think what you mean to say is hack the code to bump fill factor
>> and then test it. I was confused that how can user can do that from
>> SQL command.
>
> Yes, that's why I said "hacking the fill factor up to 1000" when I
> originally mentioned it.
>
> Actually, for hash indexes, there's no reason why we couldn't allow
> fillfactor settings greater than 100, and it might be useful.
>

Yeah, I agree with that, but as of now, it might be tricky to support
the different range of fill factor for one of the indexes. Another
idea could be to have an additional storage parameter like
split_bucket_length or something like that for hash indexes which
indicate that split will occur after the average bucket contains
"split_bucket_length * page" worth of tuples. We do have additional
storage parameters for other types of indexes, so having one for the
hash index should not be a problem.

I think this is important because split immediately increases the hash
index space by approximately 2 times. We might want to change that
algorithm someday, but the above idea will prevent that in many cases.

--
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com