Kjell,
I am not sure what's your point. Do you think I have explained the
situation accurately or not? If so, and if you agree that Firebird
could use the suggested improvement, then take it to the devels.

KR> Yes, I would expect an index to tell the engine which records are null
KR> and which ones are not (in one or a set of columns).

I believe the index does that.

KR> And yes, I would
KR> expect that information to be used when it would be useful. Wouldn't you?

I would. But I'm not mad about performance of such tests as I don't
use them very often.

KR> The analogy with = 100 vs. <> 100 doesn't seem unquestionable

which means that it seems questionable, right? :-)

KR> because I
KR> would expect the nulls to be sorted at one of the ends of the index.

Correct. <> 0 would have been a better analogy. But that doesn't
change a thing, because AFAIK Fb isn't capable of optimizing away "<>
0" to "> 0" (and it shouldn't, because numbers may be negative) in the
general case. It could in the "is not null" case, that's what I say.

With that in mind, > or < would seem like a better analogy than <>,
and in that case, yes, I would expect the index to be used.

There are two cases here:
- the case with "is not null" doesn't require "> or <", ">" is
enough.
- the general case would use that, but I'm not sure that the optimizer
can always tell if two index scans are better than a natural scan.

Ciao
--
Nando Dessena
http://www.flamerobin.org