Re: cluster index on a table

On Thu, Jul 16, 2009 at 8:18 PM, Scott Carey<scott(at)richrelevance(dot)com> wrote:
> " Each index row in the nonclustered index contains the nonclustered key
> value and a row locator. This locator points to the data row in the
> clustered index or heap having the key value."
>
> That sort of model should work with MVCC and even HOT with the same
> restrictions that HOT has now.

The problem with this is that btree indexes need to be able to split
pages. In which case your tuple's tid changes and all hell breaks
loose. One of the fundamental design assumptions in our MVCC design is
that you can trust a tuple to stay where it is as long as it's visible
to your transaction.

For example you may want to go back and check the discussion on
getting vacuum to do a sequential scan of indexes. The solution we
found for that only works because only a single vacuum can be scanning
the index at a time.

Another scenario to think about, picture yourself in the middle of a
nested loop processing all the matches for a tuple in the outer
relation. Now someone else comes along and wants to insert a new tuple
on the same page as that outer tuple and has to split the page. How do
you do that without messing up the nested loop which may not come back
to that page for many minutes?

--
greg
http://mit.edu/~gsstark/resume.pdf