At 10:08 AM 20/10/2009, you wrote:

>I have a table with a nullable integer field, used as a boolean, with an index
>on it. Searching for rows with a specific value is fast; checking for non-null
>values is fast; checking for the presence of null values is slow, despite an
>identical plan (tries to use the index.) Index statistics have been
>recalculated. It's an ascending index. FB 2.1.1 SS Win32.
>
> From the descriptions of the index page format in ODS11, it *looks* like indices know about null values, and store them up-front;

Not quite....the default SORT order for *ordered sets* is nulls last for ascending indices; nulls first for descending. I'm not totally sure how this translates into the index selection for searches but I'd guess it would make a null case search based on the ascending index pretty horrible, especially one that combines the ultimate low selectivity with such a strong bias.

>why would it decide to use the index, but still scan essentially every page of the index (67336?) to decide if I have any null records or not?

Try creating a descending index on that column, then run set stats on both it and the ascending index; after that, test for the null case. Note both the plan and the performance. The optimizer should choose the new index (if it thinks it would be useful), use an all-natural plan (middle way) or (worst) stick with the (bad) ascending index.

./heLen