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