>>
>> This is really weird.
>>
>> I cannot see why a NATURAL plan is being used instead of my index, I am not doing anything fancy.
>>
>> Using: FB2.5.1 32 bit classic server
>>
>> Query:
>>
>> select t.SalesOrderNo from stk_stocktransaction t where (t.SourceType
>> = 34)
>> and (t.TransactionType = 'EXTCST')
>> and (t.RefNo = 'I00001')
>>
>> Plan is:
>> PLAN (T NATURAL)
>>
>> Yet I have the following index: SourceType, TransactionType, RefNo asc
>>
>> With selectivity: 0.0000049....
>>
>> Table has 1.4 million records.
>>
>> Query does 1.4 million non-indexed reads, and takes 11 to 20 seconds
>> to return no records (for the parameters I have selected)
>>
>> My development database DOES use the index.
>>
>> Please help! Is this a bug, or am I missing something!
>>
>> How can I force it to use the index, to see if it is any faster?
>
>>> Is the index by any chance inactive, e.g. due to a restore without indexes etc ...
>
> Wow, Thomas that was fast ;-)

(Fast) service is our success. ;-)

> It is definitely active.
>
> Running gstat through IB expert, tells me it has a depth of 3.
>
> Then, IBExpert has a column for Selectivity and Real Selectivity. The Real selectivity is coloured red, and if I hover over the info, a hint appears telling me that my index is useless, as it has a selectivity of less than 0.01.
>
> Everything I thought I knew about indexes, seems to be wrong ?!

What's the result of:

* Running SET STATISTICS on this particular index
* Query RDB$INDICES.RDB$STATISTICS for this index


Also you can generate a histogram of the used values/permutation with:


select
sourcetype
, transactiontype
, refno
, count(*)
from
stk_stocktransaction
group by
sourcetype
, transactiontype
, refno


This gives you the value distribution across the entire index.


Regards,
Thomas