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
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? |