Re: pgsql 10: hash indexes testing

On Wed, Jul 05, 2017 at 08:10:10AM +0530, Amit Kapila wrote:
> On Tue, Jul 4, 2017 at 4:27 PM, AP <ap(at)zip(dot)com(dot)au> wrote:
> > There is one index that caused an issue. Towards the end of an import
> > I got the following error:
> >
> > out of overflow pages in hash index
> >
> > The data being indexed is BYTEA, (quasi)random and 64 bytes in size.
> > The table has over 2 billion entries. The data is not unique. There's
> > an average of 10 duplicates for every unique value.
> >
> > Is this a valid error message or have I spotted a bug?
>
> It is difficult to say at this stage, but I think we can figure out.
> We can get such a message if we consume the maximum overflow pages
> that hash index can support which is limited by a number of
> bitmappages. Can you try to use pgstattuple extension and get us the
> results of Select * from pgstathashindex('index_name');? If the
> number of bitmappages is 128 and total overflow pages are 128 * 4096,
> then that would mean that all the pages are used. Then maybe we can

Hmm. Unless I misunderstood that'd mean that overflow_pages/4096 should
result in a number <= 128 at the moment, right? If so then something is
amiss:

# select * from pgstathashindex('link_datum_id_hash_idx');
version | bucket_pages | overflow_pages | bitmap_pages | unused_pages | live_items | dead_items | free_percent
---------+--------------+----------------+--------------+--------------+------------+------------+------------------
3 | 10485760 | 2131192 | 66 | 0 | 2975444240 | 0 | 1065.19942179026
(1 row)

oldmdstash=# select 2131192/4096;
?column?
----------
520
(1 row)

And I do appear to have an odd percentage of free space. :)

This index was created yesterday so it has been around for maybe 18 hours.
Autovac is likely to have hit it by now.

> > I'll see if I can get the table reloaded a-fresh to see if that's what
> > it takes to trigger the error.
>
> Thanks. I suggest when an error occurs, don't throw away that index
> because we can get some useful information from it to diagnose the
> reason of error.

I'll try and set this up now.

AP