mivi71dk wrote:

>
>
> ... tables contain some 24 millions records.
>
> Select
> ID, BonNr, Tekst
> from Point_Kvit
> Where BonNr = 2236136
>
> It fetches 31 records in ... 0.062 secs with this plan:
> PLAN (POINT_KVIT INDEX (POINT_KVIT_IDX1))
>
>
> Select
> ID, BonNr, Tekst
> from Point_Kvit
> Where BonNr = 2236136
> Order by ID
>
> It fetches the same 31 records, but now uses 2 seconds with this plan:
> PLAN (POINT_KVIT ORDER RDB$PRIMARY240 INDEX (POINT_KVIT_IDX1))
>

Ah. The optimizer is doing you a favor and avoiding sorting 31 records
by reading 24 million instead.


As Martijn suggested >>

>> Select
>> ID, BonNr, Tekst
>> from Point_Kvit
>> Where BonNr = 2236136
>> Order by ID+0
>>
>>
>> Also works perfect.
>>
>> But I would like to know why.
>
> Because of the expression, it cannot use the index for sorting.
>
> Given that your result returns just a few rows, it should sort those
> rows without the index, as hopping arround the index to find the
> row, put them in sorting order etc takes more time.

In this case, it's not hopping around much because the records are
stored in the same order as the index on ID. But it is reading
every record in the table to return those 31.


Good luck,

Ann