Hello Tom,
Tuesday, July 06, 1999 you wrote:
T> No, you are not right. Tuple updates can *never* be done without
T> moving the tuple, because the old tuple value must not be overwritten
T> until and unless the transaction is committed. (Under MVCC, it may
T> need to stick around even longer than that, I believe.) Thus, a tuple
T> update would require an update (and move) of every referencing tuple,
T> which could cascade into updates of tuples that reference those tuples,
T> etc.
This problem can be solved. An offhand solution is to have
an additional system field which will point to new tuple left after
update. It is filled at the same time as the original tuple is
marked invalid. So the scenario is as follows: we follow the link,
and if we find that in the tuple where we arrived this system field
is not NULL, we go to (the same table of course) where it is pointing
to. Sure VACUUM will eliminate these. Performance penalty is small.
T>> Finally, I'm not convinced that the results would be materially faster
T>> than a standard mergejoin (assuming that you have indexes on both the
T>> fields being joined) or hashjoin (in the case that one table is small
T>> enough to be loaded into memory).
>> Consider this: no indices,
T> You'd still need indices --- see above
Only time when we will need to look who is referencing us is
during VACUUM. So no real need of indices.
>> no optimizer thinking,
T> You'd still need to run the optimizer to decide whether you wanted to
T> use this technique or some more-conventional one (unless your proposal
T> is to remove all other join mechanisms? Rather inflexible, that...)
No. I am not an evil itself which tries to eliminate everything :)
I said when optimizer sees join made through such field it has
the only option - to follow link. It simply has no choice.
T> If you think it takes three instructions to access a tuple that's out
T> on disk somewhere, I'm afraid you're sadly mistaken.
No. I meant a tuple which is in memory somewhere :)
Best regards, Leon