Re: pgsql 10: hash indexes testing

On Wed, Jul 05, 2017 at 10:29:09AM +0530, Amit Kapila wrote:
> >> 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?
> 
> No, sorry, I think my calculation above has something missing.  It
> should be 128 * 4096 * 8.  How we can compute this number is
> no_bitmap_pages * no_bits_used_to_represent_overflow_pages.

AHA! Ok. Then that appears to match. I get 65.041.

> >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)
> 
> You need to divide 520 by 8 to get the bitmap page.  Is this the index
> in which you get the error or is this the one on which you have done
> REINDEX?

Post REINDEX.

> > And I do appear to have an odd percentage of free space. :)
> >
> 
> It looks like Vacuum hasn't been triggered.

:(

> > This index was created yesterday so it has been around for maybe 18 hours.
> > Autovac is likely to have hit it by now.
> 
> Do you have any deletes?  How have you verified whether autovacuum has

No DELETEs. Just the initial COPY, then SELECTs, then a DB rename to get it
out of the way of other testing, then the REINDEX.

> been triggered or not?

I just checked pg_stat_user_tables (which I hope is the right place for
this info :)

   relid   | schemaname | relname | seq_scan | seq_tup_read | idx_scan | idx_tup_fetch | n_tup_ins  | n_tup_upd | n_tup_del | n_tup_hot_upd | n_live_tup | n_dead_tup | n_mod_since_analyze | last_vacuum | last_autovacuum |         last_analyze          |       last_autoanalyze        | vacuum_count | autovacuum_count | analyze_count | autoanalyze_count
-----------+------------+---------+----------+--------------+----------+---------------+------------+-----------+-----------+---------------+------------+------------+---------------------+-------------+-----------------+-------------------------------+-------------------------------+--------------+------------------+---------------+-------------------
 129311803 | public     | link    |       70 |  15085880072 |     5779 |        465623 | 2975444240 |         0 |         0 |             0 |  928658178 |          0 |                   0 |             |                 |                               | 2017-06-28 10:43:51.273241+10 |            0 |                0 |             0 |                 2

So it appears not.

# show autovacuum;
 autovacuum 
------------
 on
(1 row)

All autovacuum parameters are as per default. The autovacuum launcher process
exists.

:(

AP