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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers