Indexes and MVCC

From: Rakesh Kumar <rakeshkumar464(at)outlook(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Indexes and MVCC
Date: 2017-02-19 16:52:29
Message-ID: MWHPR2201MB1565371C36A8D0AF734BFCFF8C5F0@MWHPR2201MB1565.namprd22.prod.outlook.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


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. Only HOT updates will end up in situation
described in (3).

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Gavan Schneider 2017-02-19 20:41:17 Re: Ho w tö select a column?
Previous Message Melvin Davidson 2017-02-19 15:22:37 Re: No space left on device