Re: Index on two columns not used - Mailing list pgsql-performance

From Tom Lane
Subject Re: Index on two columns not used
Date
Msg-id 24757.1161619301@sss.pgh.pa.us
Whole thread Raw
In response to Re: Index on two columns not used  (Markus Schaber <schabi@logix-tt.com>)
Responses Re: Index on two columns not used
List pgsql-performance
Markus Schaber <schabi@logix-tt.com> writes:
> Alvaro Herrera wrote:
>> Actually, when the UPDATE puts a new row version in the same heap page,
>> the index must be updated anyway.

> AFAICS only, when the index covers (directly or via function) a column
> that's actually changed.
> Changing columns the index does not depend on should not need any write
> access to that index.
> Correct me if I'm wrong.

You're wrong.  An UPDATE always writes a new version of the row (if it
overwrote the row in-place, it wouldn't be rollback-able).  The new
version has a different TID and therefore the index entry must change.
To support MVCC, our approach is to always insert a new index entry
pointing at the new TID --- the old one remains in place so that the old
version can still be found by transactions that need it.  Once the old
row version is entirely dead, VACUUM is responsible for removing both it
and the index entry pointing at it.

Other DBMSes use other approaches that shift the overhead to other
places, but that's how Postgres does it.

            regards, tom lane

pgsql-performance by date:

Previous
From: "Worky Workerson"
Date:
Subject: Re: Best COPY Performance
Next
From: "Bucky Jordan"
Date:
Subject: Re: New hardware thoughts