Re: Next Steps with Hash Indexes

On Fri, 13 Aug 2021 at 05:01, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
>
> On Thu, Aug 12, 2021 at 8:30 PM Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> >
> > On Thu, Aug 12, 2021 at 12:22 AM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
> > > The design of the patch has changed since the initial proposal. It
> > > tries to perform unique inserts by holding a write lock on the bucket
> > > page to avoid duplicate inserts.
> >
> > Do you mean that you're holding a buffer lwlock while you search the
> > whole bucket? If so, that's surely not OK.
> >
>
> I think here you are worried that after holding lwlock we might
> perform reads of overflow pages which is not a good idea. I think
> there are fewer chances of having overflow pages for unique indexes so
> we don't expect such cases in common and as far as I can understand
> this can happen in btree as well during uniqueness check. Now, I think
> the other possibility could be that we do some sort of lock chaining
> where we grab the lock of the next bucket before releasing the lock of
> the current bucket as we do during bucket clean up but not sure about
> the same.
>
> I haven't studied the patch in detail so it is better for Simon to
> pitch in here to avoid any incorrect information or if he has a
> different understanding/idea.

That is correct. After analysis of their behavior, I think further
simple work on hash indexes is worthwhile.

With unique data, starting at 1 and monotonically ascending, hash
indexes will grow very nicely from 0 to 10E7 rows without causing >1
overflow block to be allocated for any bucket. This keeps the search
time for such data to just 2 blocks (bucket plus, if present, 1
overflow block). The small number of overflow blocks is because of the
regular and smooth way that splits occur, which works very nicely
without significant extra latency.

The probability of bucket collision while we hold the lock is fairly
low. This is because even with adjacent data values the hash values
would be spread across multiple buckets, so we would expect the
contention to be less than we would get on a monotonically increasing
btree.

So I don't now see any problem from holding the buffer lwlock on the
bucket while we do multi-buffer operations.

--
Simon Riggs http://www.EnterpriseDB.com/