Re: Indexes and MVCC

On Sun, Feb 19, 2017 at 8:52 AM, Rakesh Kumar <rakeshkumar464(at)outlook(dot)com>
wrote:

>
> https://www.youtube.com/watch?v=8mKpfutwD0U&t=1741s
>
> Somewhere around 13th minute, Chris Tavers mentions this:
>
> 1 - In the queuing table, the application deletes lot of rows (typical for
> a queuing table).
> 2 - Query trying to find out rows to be picked by the queue, accesses them
> via indexes.
> 3 - Vacuum took lot of time to clean up dead rows.
> 4 - Indexes do not reach out to the latest visible tuple and has to
> traverse lot of dead
> rows before hitting the row required by the sql. This is because of
> (3).
>
> My Question:
> Is the (4) true for all updates. AFAIK, if an update occurs on even one
> index col, the
> index itself creates a new version in MVCC.
>

The index doesn't create a new version, but there is created a new pointer
in the index to the new version in the table. But it doesn't remove the
pointer to the old version at the time the new pointer is created.

> Only HOT updates will end up in situation
> described in (3).
>

No, quite the opposite. Anyone can clean up a HOT update, once the
previous version is old enough that it can't be of interest to anyone
anymore. Only vacuum can completely remove the dead tuple from a non-HOT
update.

However, btree indexes have a feature where if they find a tuple which is
old enough that it can't be interesting to anyone, they remember that and
when they get back to the index they clean up the index entry, so the next
process doesn't need to follow it. But the key here is that the tuple has
to be old enough that it is not interesting to *anyone*. If there is a
long-lived transaction, even if it is not interested in this particular
table, it will inhibit this mechanism from working (as well as inhibiting
vacuum itself from cleaning them up)

Cheers,

Jeff