Обсуждение: 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