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; 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?

(Right now, there are no null values in the table. There will frequently only be
one or two at a time, visible to the specific transaction, and fixed before
committing.)

I'd prefer not to have to switch to using some other value (-1) instead of null,
just to get the benefits of the index, but I will if I have to.

Statistics below, from FlameRobin.

Thanks!

-Philip


select 1 from rdb$database where exists(select bt_flag_party.rdb$db_key from
bt_flag_party where is_unkheir is null);

Prepare time: 0.032s
Field #01: .CONSTANT Alias:CONSTANT Type:INTEGER
PLAN (BT_FLAG_PARTY INDEX (BT_IX_FLAG_PARTY_IS_UNKHEIR))
PLAN (RDB$DATABASE NATURAL)
200582 fetches, 0 marks, 0 reads, 0 writes.
0 inserts, 0 updates, 0 deletes, 67336 index, 1 seq.
Delta memory: 29060 bytes.
Total execution time: 0.188s

vs.

select 1 from rdb$database where exists(select bt_flag_party.rdb$db_key from
bt_flag_party where is_unkheir is not null);

Prepare time: 0.000s
Field #01: .CONSTANT Alias:CONSTANT Type:INTEGER
PLAN (BT_FLAG_PARTY NATURAL)
PLAN (RDB$DATABASE NATURAL)
13 fetches, 0 marks, 0 reads, 0 writes.
0 inserts, 0 updates, 0 deletes, 0 index, 2 seq.
Delta memory: -25092 bytes.
Total execution time: 0.032s

vs.

select 1 from rdb$database where exists(select bt_flag_party.rdb$db_key from
bt_flag_party where is_unkheir = -1);

Prepare time: 0.015s
Field #01: .CONSTANT Alias:CONSTANT Type:INTEGER
PLAN (BT_FLAG_PARTY INDEX (BT_IX_FLAG_PARTY_IS_UNKHEIR))
PLAN (RDB$DATABASE NATURAL)
196 fetches, 0 marks, 0 reads, 0 writes.
0 inserts, 0 updates, 0 deletes, 86 index, 1 seq.
Delta memory: -28 bytes.
Total execution time: 0.031s