Re: Next Steps with Hash Indexes

On Wed, Aug 11, 2021 at 8:47 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> As far as the specific point at hand is concerned, I think storing
> a hash value per index column, while using only the first column's
> hash for bucket selection, is what to do for multicol indexes.
> We still couldn't set amoptionalkey=true for hash indexes, because
> without a hash for the first column we don't know which bucket to
> look in. But storing hashes for the additional columns would allow
> us to check additional conditions in the index, and usually save
> trips to the heap on queries that provide additional column
> conditions. You could also imagine sorting the contents of a bucket
> on all the hashes, which would ease uniqueness checks.

Earlier, I was thinking that we have two hashes, one for the first key
column that is for identifying the bucket, and one for the remaining
key columns which will further help with heap lookup and ordering for
uniqueness checking. But yeah if we have a hash value for each column
then it will make it really flexible.

I was also looking into other databases that how they support hash
indexes, then I see at least in MySQL[1] the multiple column index has
a limitation that you have to give all key columns in search for
selecting the index scan. IMHO, that limitation might be there
because they are storing just one hash value based on all key columns
and also selecting the bucket based on the same hash value.

[1] https://dev.mysql.com/doc/refman/8.0/en/index-btree-hash.html

--
Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com