Row versions and indexes

From: "Jack Orenstein" <jack(dot)orenstein(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Row versions and indexes
Date: 2006-09-28 21:58:44
Message-ID: 7ecd811f0609281458q794cb1c1ybcfdd38ad1039250@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Suppose I have a table and index:

create table t(x int, y varchar, primary key(x));

and that the only updates are "update t set y = ... where x = ?".

I understand that updating a row of t generates a new row version, and
that different transactions may see different versions of the same
row.

How does versioning work for the index?

- The update above does not update the index key. Does the index get
updated at all?

- If not, then how can an index lookup return the correct version of
selected rows?

This aspect of versioning has never been clear to me. Now there's a cost
issue involved, as I need to update every row in a large table, never
updating the index key. Will this run faster if I drop the index?
(Yes, I can run the experiment, but I'd like to understand the
fundamentals better.)

Jack Orenstein

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Alan Hodgson 2006-09-28 22:06:49 Re: Row versions and indexes
Previous Message Ben 2006-09-28 21:24:43 Re: contrib/levenshtein() has a bug?