Re: [Firebird-devel] Firebird feature requests

[Alex K. <al_...@uk...>]

""Ann W. Harrison"" <aha...@ib...> ???????/???????? ? ????????

>> Excuse me, but I don't understand who said that indexes should not be 
>> used for inequalities or why he or she would say that.  Unless 100% of 
>> the records meet the selection critereon, the indexed version will always 
>> be faster, and even if 100% of the records are selected, the cost of the 
>> index scan is small compared to fetching the actual records.  Am I 
>> missing something here?
    Problem a bit different and Dmitry already gave answer on it -- existing 
versions of Firebird not optimally handle "greater than" and "less than" 
index lookups - actually they performs "greater or equal" and "less or 
equal" index lookup and eliminate "equal" rule at the end of query 
execution. As a result with non-uniform key distribution we have performance 
penalty like in the demonstrated case (i.e. process ~450030 records instead 
of 30).

> Yes... the process of building the data for the query, includes this step:
> 4) Simulate some past table activity (important! do not skip this step)
> update mytable set status = 3 where status = 1;
> update mytable set status = 1 where status = 2;
> update mytable set status = 2 where status = 3;
> commit;
> Those updates create back versions and index entries.  The result is that 
> every record has a version that has the value 1.
    Please do not repeat mistakes of Vlad Horsun!!!
First of all there are 30 records in table which have key value "0" and 
NEVER change it. And we're interested in those records in my case and not in 
the records which we did shuffle on.
At second, all old versions should be discarded at commit and may stay as 
garbage but must not be used and should not affect performance so 
dramatically (it degrades from 1 second to about several minutes on Xeon 
2.8GHz with 1Gb of RAM). Indexes also must be updated to reflect latest 
committed changes.

> Most of them are  old versions and the garbage collection may also affect 
> the query timings.
    I agree that garbage collection may affect but this is not the case.

SY,
Alex Kotov