MySQL Forums
Forum List  »  Falcon

Re: Index only scans
Posted by: Ann Harrison
Date: May 04, 2007 11:05AM

Falcon, like PostgreSQL, has multi-generational indexes. What that means
is that there will be several entries in the index for a single record if
the key value has changed during the lifetime of running transactions.
As a result, this query

select unique_value from this_table where unique_value = 1;

has to look at the record(s) in the index to determine which - if
any - is appropriate for the current transaction.

It would be possible to keep transaction information in the index
and resolve the visibility there. However, in a bad case, that
more than doubles the fixed size of an index node. To avoid bad
cases, index nodes would need to be created with one transaction id,
expanded to include a second transaction id when the value changes,
or shrunk when the record is fully mature and visible to all
transactions. That's a lot of bloat and a lot of extra manipulation
in a shared data structure.

The question of whether the rows that must be fetched are guaranteed
to be in memory is very interesting. Right now, all entries are
validated, though it appears that there may be an unexploited
optimization there, along the lines of "if the record isn't in
memory, then it's guaranteed to be part of the result set". We
don't currently make that assumption and will need to think it
through, but that might well work.



Options: ReplyQuote

Written By
April 25, 2007 09:03PM
Re: Index only scans
May 04, 2007 11:05AM
May 04, 2007 04:47PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.