>>Set wrote:
>>However, if I specify the plan:
>>
>>Select <whatever>
>>From tablea a
>>Where a.VerySelectiveField between :fromvalue1 and :tovalue1
>> And a.AlmostAsSelectiveField between :fromvalue2 and :tovalue2
>>PLAN (A INDEX(IndexVerySelectiveField, IndexAlmostAsSelectiveField))
>>
>>then the performance is decent in both cases, although not as excellent
>>as if the best plan had been chosen depending >on the parameters.
>
>Sounds good. To help me understand better, what exactly am I telling FB
>to do here though? Use both indexes simultaneously?

Yes, you're telling it to use both indexes. With one parameter being selective and the other one including all values, one of them will speed up execution and the other one slow it down. I have no idea how selective the selective part has to be for this to be better than going NATURAL, but from my visual inspection it at least seemed to be noticeably better to use both indexes (compared to using none) when one of the criteria selected one out of a million records in Firebird 1.5.4. If the most selective criteria selects a much higher proportion of the records (e.g. 10%), then NATURAL may be better - I simply don't know.

Set