Re: pessimal trivial-update performance

On Sun, Jul 4, 2010 at 9:48 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> 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).
[...]
> 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.

It looks like a HOT cleanup happens when pd_prune_xid falls behind
OldestXmin. Normally, we set pd_prune_xid to the xmax of the deleted
tuple, but we could perhaps fudge that here to get the desired
behavior; maybe just set it to FrozenXID. Where it gets sticky is
that the proposed rules for HeapTupleSatisfiesVacuum() give different
answers depending on who does the vacuuming, so if backend A sets a
hint say, hey, there's vacuumable stuff on this page, and then backend
B tries to prune it, nothing will happen. What would be nicer is if
there were a way for the updater to mark the item pointer or tuple in
some way that would make it look vacuumable to everyone, but without
breaking the HOT chain.

> 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.

Yeah, that's a shame. HOT is huge, but it would be great if we had a
way to do partial vacuuming even when the indexed columns are updated.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company