>The query is
>
>select [some columns]
> from MYTABLE
> where FIELDONE= 2 and FIELDTWO< 2
> order by [some columns]
>
>Most of the time most records have FIELDONE = 2 and FIELDTWO = 2, so this query is looking
>for what are normally a small number of exceptions, and one of my objectives is to have it
>run as fast as possible in the normal case which is that it finds no matching records.
>
>INDEXONE is on FIELDONE, FIELDTWO ASCending.
>
>In the presence of INDEXONE this query uses this index and is fast, doing, as one might have
>hoped, very little work in the normal case of finding no records.
>
>If I add (for other purposes, to support some other query)
>
>INDEXTWO is on FIELDTWO DESCending
>
>then the plan for the above query switches to using INDEXTWO instead of INDEXONE and does,
>of the order as measured by the "Fetches" count, a similar amount of work to a table scan,
>burning up a significant number of CPU cycles and amount of wall clock time in the process.
>
>Why does it switch from using a perfectly good index which takes it straight to the answer
>to using one that is, in effect, almost as useless as not using an index at all?
>
>In this particular case the query is hand written (rather than generated from any sort of
>ORM layer or anything) so I do, unusually, actually have the option of sticking in an
>explicit PLAN clause as a desperate last resort if I really have to. But if possible I'd
>like to understand what's going on first?

Hi Tim!

Firebird doesn't have histograms yet (at least not in Firebird 2.5, I think it was planned for Firebird 3), so it knows only the selectivity of (parts of) each index. I don't think an ascending index can be used for 'less than' comparisons (it will be useful for greater than comparisons), so I assume only the first field of INDEXONE is used. Probably the selectivity of FIELDTWO is considerably better than the selectivity of FIELDONE, hence Firebird (incorrectly) guesses this index to be better.

If performance is vital for this query, the ideal index for your query might be:

INDEXTHREE on FIELDONE ASC, FIELDTWO DESC

Alternatively, you may get the same performance as before (preventing INDEXTWO and any other index on FIELDTWO from being used) by adding 0:

select [some columns]
from MYTABLE
where FIELDONE= 2 and FIELDTWO+0< 2
order by [some columns]

HTH,
Set