On Fri, May 17, 2024 at 12:29 PM Peter Geoghegan <pg@bowt.ie> wrote:
On Thu, May 16, 2024 at 11:36 PM Bowen Shi <zxwsbg12138@gmail.com> wrote: > From the current situation, I feel that the scenario I encountered is different from yours because it doesn't seem to be the first page of the vacuum scanning.
Really? I think it looks like the same issue.
What I saw was that the heap page that lazy_scan_prune locked up on was the first page scanned *after* the first round of index vacuuming -- *not* the first heap page scanned overall.
I misunderstood earlier.
I also saw "num_index_scans == 1", "tuples_deleted == lpdead_items", and "scanned_pages == lpdead_item_pages" when I looked at the locked up Postgres 15 production instance (or close to it). I can't tell if your dead_items is shown as empty, too, but if it is then that indicates that the heap page that your lazy_scan_prune trace relates to is the first page scanned after a round of index vacuuming (the first round).
If you can show more about dead_items, then it will either confirm or disprove my theory about it being the same issue.