Row versions and indexes
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
On Thursday 28 September 2006 14:58, "Jack Orenstein"
<jack.orenstein@gmail.com> wrote:
How does versioning work for the index?
- The update above does not update the index key. Does the index get
updated at all?
Yes, it does.
- 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.)
Yes, it certainly will.
--
"A government that robs Peter to pay Paul can always depend upon the support
of Paul." - George Bernard Shaw
"Jack Orenstein" <jack.orenstein@gmail.com> writes:
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?
Each row version has its own index entry pointing to it. So an update
is not a lot different from an insert --- they both generate a full set
of index entries.
regards, tom lane