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