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