Re: pessimal trivial-update performance

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Sun, Jul 4, 2010 at 12:11 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> I believe that none of the dead row versions can be vacuumed during this
>> test.

> Yep, you seem to be right. The table grows to 802 pages. But why is
> it that we can't vacuum them as we go along?

Sure. What you'd need is for HeapTupleSatisfiesVacuum to observe that
(a) the tuple's xmin and xmax are equal,
(b) they're equal to my own transaction's XID,
(c) none of the live snapshots in my backend can see cmin but not cmax,
(d) cmax < currentCommandId, ensuring that every future snapshot will
see cmax too (not quite convinced this is certain to hold).

Now that we have a centralized list of all live snapshots, it's at least
possible in principle to do (c).

(I'm ignoring the possibility that the xmin and xmax are from different
subtransactions of my own XID --- that seems to complicate matters
greatly in order to handle even-more-cornerish cases.)

Of course, you'd also need to get to HeapTupleSatisfiesVacuum in the
first place. The complained-of case lacks any VACUUM call. Maybe a HOT
cleanup would happen at the right time but I'm not sure. If it doesn't,
adding one would represent a significant expenditure that would usually
not be repaid.

Another issue here is that since xmin is certainly within the GlobalXmin
horizon, it would be essential to preserve the update chain ctid links,
ie, make the tuple's update predecessor point to its successor. That
seems workable for the case of cleaning out an intermediate entry in a
HOT chain, but not otherwise.

Details left as an exercise for the student.

regards, tom lane