Re: vacuum, performance, and MVCC

On 6/24/06, Mark Woodward wrote:
>
> ver001->verN->...->ver003->ver002->|
> ^---------------------------------/
>
> This will speed up almost *all* queries when there are more than two
> version of rows.
>
> OK, here is the behavior of an update:
> (1) Find the latest version of the row
> (2) Duplicate row and modify as per plan
> (3) Set the t_ctid of the new row to the last "latest"
> (4) Set the t_ctid of the first row to that of the new row
> (5) Attempt to index the row
> (6) If the first version of the row is in the index already (ver001) Don't
> modify the index, otherwise, add the new version (just as before)

I am not sure I understand your algorithm. If we take as a starting
point the following situation of a fresh tuple, in very schematic form
it looks like:

Heap:
TID T_CTID XMIN XMAX Col1 Col2
xxx1 xxx1 ttt1 null 1 1

Index on Col1:
1 xxx1

Index on Col2:
1 xxx1

Now, after an update to this tuple changing the Value2 field, in what
state should the heap, index 1 and index 2 be? If I understand you
correctly, you want it to be:

Heap:
TID T_CTID XMIN XMAX Col1 Col2
xxx1 xxx2 ttt1 ttt2 1 1
xxx2 xxx1 ttt2 null 1 2

Index on Col1:
1 xxx2

Index on Col2:
1 xxx1
2 xxx2

Three questions:
1. Do I understand your intention correctly?
2. Could you extend this for an update to increment value2 (because
the T_CTID logic gets a bit unclear for me there).
3. The net benefit of this would be 1 less entry in the index on Col1?

Jochem