VACUUM-related issue that I heard recently was; long transaction prevents lots of dead tuples generated during that from being vacuumed and also prevents index entries from being marked as lp_dead, which could decrease the performance of range search query using index.
Conversation
Replying to
I wonder if the Postgres 14 index deletion stuff would help. The basic rules about which tuples we can remove don't change, so you might wonder how it ever could. New code is "more forgiving" if there are only a few LP_DEAD bits set when leaf pages cannot fit additional versions.
Replying to
You mean that stuff would help reducing the number of leaf pages by avoiding "unnecessary" split in that case, and which may improve the query performance? Maybe yes, but I'm guessing it can improve only a little.
1
Because in the case, the issue was that the range search query needs to fetch lots of index entries that were deleted but can neither be vacuumed nor marked as LP_DEAD bits because of long transaction, even if leaf pages are already cached.
2
Show replies