Re: [HACKERS] pgsql 10: hash indexes testing - Mailing list pgsql-hackers

From AP
Subject Re: [HACKERS] pgsql 10: hash indexes testing
Date
Msg-id 20170705042323.loesq74grji2aj35@zip.com.au
Whole thread Raw
In response to Re: [HACKERS] pgsql 10: hash indexes testing  (Amit Kapila <amit.kapila16@gmail.com>)
Responses Re: [HACKERS] pgsql 10: hash indexes testing
List pgsql-hackers
On Wed, Jul 05, 2017 at 08:10:10AM +0530, Amit Kapila wrote:
> On Tue, Jul 4, 2017 at 4:27 PM, AP <ap@zip.com.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



pgsql-hackers by date:

Previous
From: Mark Dilger
Date:
Subject: Re: [HACKERS] Request more documentation for incompatibility of parallelism and plpgsql exec_run_select
Next
From: Amit Kapila
Date:
Subject: Re: [HACKERS] pgsql 10: hash indexes testing