Re: Analyze on large changes...

Lincoln Yeoh <lyeoh(at)pop(dot)jaring(dot)my> writes:
> My limited understanding of current behaviour is the search for a valid
> row's tuple goes from older tuples to newer ones via forward links

No. Each tuple is independently indexed and independently visited.
Given the semantics of MVCC I think that's correct --- after all, what's
dead to you is not necessarily dead to someone else.

There's been some speculation about trying to determine whether a dead
tuple is dead to everyone (essentially the same test VACUUM makes), and
if so propagating a marker back to the index tuple so that future
indexscans don't have to make useless visits to the heap tuple.
(I don't think we want to try to actually remove the index tuple; that's
VACUUM's job, and there are locking problems if we try to make it happen
in plain SELECTs. But we could set a marker bit in the index entry.)
Under normal circumstances where all transactions are short, it wouldn't
be very long before a dead tuple could be marked, so this should fix the
performance issue. Doing it in a reasonably clean fashion is the sticky
part.

regards, tom lane