I have a select statement with which I could use some help. I can't seem to get it optimized further to give consistent results running Firebird 1.5 RC 8 SS under Windows 2000 Pro.

The Table:
CREATE TABLE SPREADACTIONTABLE
(
SPREADACTION_ID BIGINT NOT NULL,
SPREAD_ID BIGINT NOT NULL,
ACTION_TYPE SMALLINT NOT NULL,
STATUS SMALLINT,
SPREAD_TIME INTEGER NOT NULL,
ACTION_TIME INTEGER NOT NULL,
CREATION_TIMESTAMP TIME_STAMP ,
CONSTRAINT SPREADACTIONTABLEPRIMARYKEY1 PRIMARY KEY (SPREADACTION_ID)
);

CREATE ASC INDEX SPREADID_INDEX ON SPREADACTIONTABLE
(SPREAD_ID, ACTION_TIME, SPREADACTION_ID);


The select statement:

select first 1 SpreadAction_ID from SpreadActionTable
where Spread_ID = 9007 AND ( Status IS NULL OR Status != 2 )
AND Action_Time >= 1073030400
AND Action_Time <= 1075535999
Order By Action_Time;

The plan used for this was: PLAN SORT ((SPREADACTIONTABLE INDEX (SPREADID_INDEX)))

Why does the execution plan include a sort? Since it has an ascending index on field ACTION_TIME, and I have requested "first 1", should it not simply read the index until it finds the first ACTION_TIME within the specified range, grab the corresponding table row, and be done?

Sometimes this query executes instantly, but many other times it takes several seconds to do: those are apparently the times when there are lots (thousands) of rows with the specified SPREAD_ID. Apparently Firebird is retrieving all the rows, sorting them, and only then delivering the first row to me.

Table Statistics:

SPREADACTIONTABLE (131)
Primary pointer page: 145, Index root page: 146
Data pages: 660343, data page slots: 660343, average fill: 3%
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 1
60 - 79% = 312
80 - 99% = 660030

Index SPREADACTIONTABLEPRIMARYKEY1 (0)
Depth: 3, leaf buckets: 65682, nodes: 38792693
Average data length: 5.00, total dup: 0, max dup: 0
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 24628
60 - 79% = 0
80 - 99% = 41054

Index SPREADID_INDEX (1)
Depth: 4, leaf buckets: 112815, nodes: 38792693
Average data length: 12.00, total dup: 0, max dup: 0
Fill distribution:
0 - 19% = 30
20 - 39% = 16
40 - 59% = 44966
60 - 79% = 2475
80 - 99% = 65328

BTW, why does the "average fill" for this table show as 3%, when the fill distribution shows the great majority of the pages are in the 80-99% fill range?

TIA for any help!

Dan.