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 ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
