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)
