Re: pgsql 10: hash indexes testing

On Fri, Aug 04, 2017 at 08:21:01AM +0530, Amit Kapila wrote:
> Note - AP has off list shared the data dump and we (Ashutosh Sharma
> and me) are able to reproduce the problem and we could see that if we
> force vacuum via the debugger, then it is able to free overflow pages.
> The exact numbers are not available at this stage as the test is not
> complete.

I've another if you would like it. I COPYed with FILLFACTOR of 10 and
it eventually failed but I could not recreate the index (via CREATE INDEX
CONCURRENTLY) with the data that made it using a fillfactor of 100. If
I created the index again (again with the same data) with fillfactor 10
then it completed.

I may be completely misunderstanding fillfactor but I always thought it was
a performance optimisation rather than something that may allow you to store
more (or less) index entries.

The stats for the various indexes are:

After COPYs started failing:
fmmdstash=# select overflow_pages/bitmap_pages/8,* from  pgstathashindex('link_datum_id_idx');
 ?column? | version | bucket_pages | overflow_pages | bitmap_pages | unused_pages | live_items | dead_items |   free_percent
----------+---------+--------------+----------------+--------------+--------------+------------+------------+------------------
     4095 |       3 |    103782169 |        4194176 |          128 |     13658343 | 5 085 570 007 |          0 | 21014.6558371539
(1 row)

Time: 6146310.494 ms (01:42:26.310)

After the CREATE INDEX CONCURRENTLY with FILLFACTOR 100 failed:
fmmdstash=# select overflow_pages/bitmap_pages/8,* from  pgstathashindex('link_datum_id_idx1');
 ?column? | version | bucket_pages | overflow_pages | bitmap_pages | unused_pages | live_items | dead_items |  free_percent
----------+---------+--------------+----------------+--------------+--------------+------------+------------+-----------------
     4095 |       3 |      6205234 |        4194176 |          128 |        86222 | 3080760746 |          0 | 615.91682922039
(1 row)

Time: 19128.527 ms (00:19.129)

After the CREATE INDEX CONCURRENTLY with FILLFACTOR 10 succeeded:
fmmdstash=# select overflow_pages/bitmap_pages/8,* from  pgstathashindex('link_datum_id_idx2');
 ?column? | version | bucket_pages | overflow_pages | bitmap_pages | unused_pages | live_items | dead_items |   free_percent
----------+---------+--------------+----------------+--------------+--------------+------------+------------+------------------
     3062 |       3 |     79677471 |        2572565 |          105 |      5074888 | 3187098806 |          0 | 19027.2399324415
(1 row)

Time: 1557509.940 ms (25:57.510)

The DB in question is now gone but I took a copy of the column as per
before so if you'd like it I can make it available via the same means.

AP