>> Running SET STATISTICS on this particular index
>> Query RDB$INDICES.RDB$STATISTICS for this index
>
> No Change: 0.0000049277592
>
>> 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.
>
> Neat trick, but not sure what I should be looking at exactly. Returns about 200 000 records.

RDB$STATISTICS and this results shows, that the index content is made up
by a lot of distinct combinations, which is good for an index.

> The SQL I am testing with, returns no records.
>
> Interestingly though, this histogram query DOES use my index ;-)
>
> Which gave me an idea:
>
> I changed my original query to:
> select t.SalesOrderNo from stk_stocktransaction t
> where (t.SourceType = 34)
> and (t.TransactionType = 'EXTCST')
> and (t.RefNo = 'I00001')
> order by t.SourceType, t.TransactionType, t.RefNo
>
> This DOES use the index.

What's the exact plan here? I guess the index is used for the sorting part?

> BUT, it's on average 50% SLOWER than the NATURAL plan the Firebird chose to use.

As Kjell has pointed out, the full table and index DDL would be useful.


--
With regards,
Thomas Steinmaurer
http://www.upscene.com/