From: Tom Lane <tgl@sss.pgh.pa.us>
To: Markus Schaber <schabi@logix-tt.com>
Cc: PostgreSQL Performance List <pgsql-performance@postgresql.org>
Subject: Re: Index on two columns not used
Date: Mon, 23 Oct 2006 12:01:41 -0400
Message-ID: <24757.1161619301@sss.pgh.pa.us> (raw)
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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, schabi@logix-tt.com
Subject: Re: Index on two columns not used
In-Reply-To: <24757.1161619301@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox