To: Markus Schaber <schabi@logix-tt.com>
cc: PostgreSQL Performance List <pgsql-performance@postgresql.org>
Subject: Re: Index on two columns not used 
In-reply-to: <453CDCF2.6090208@logix-tt.com> 
References: <45360E25.9010403@freesurf.fr> <45361295.9050504@enterprisedb.com>
	<4539F4E1.7000003@chemaxon.hu> <453CC2DD.4070608@logix-tt.com>
	<20061023135011.GG7961@alvh.no-ip.org>
	<453CDCF2.6090208@logix-tt.com>
Comments: In-reply-to Markus Schaber <schabi@logix-tt.com>
	message dated "Mon, 23 Oct 2006 17:17:06 +0200"
Date: Mon, 23 Oct 2006 12:01:41 -0400
Message-ID: <24757.1161619301@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

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