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