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