On 6/24/06, Mark Woodward wrote:


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:

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:

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?


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Reply via email to