--- In firebird-support@yahoogroups.com, "starasoris"
<starasoris@y...> wrote:

> > In some cases FB 1.5 does this. I'd like to see selectivities for
> all
> > indices involved. And of course I need a confirmation that the
> statistics is
> > up-to-date.
> >
> >
> > Dmitry
>
> When I get a chance I will try to create a very small database with
> just the problem tables and some records to create the problem. I
> will then forward this script on to the experts.
>
> Thanks guys.

Here is a link to the database backup file where there is data to
test it. It is 4 meg but unfortunatly removing a lot of the data made
the problem stop happening.

http://www.lubero.net/querytest.zip

Problem: There is an index on n_unitsordered in the Requisition
Table. if it is active the following query frezes. if it is inactive
it works Ok. The index should not be needed by the query.

select s.s_finedept, sum(I.D_Price * I.n_unitsordered) totalval
from gen_tabl_requisitionitem I
inner join gen_tabl_stockitem S on S.PKS_STOCKITEMID =
I.FKS_STOCKITEMID
where FKN_PICKSLIPID = 256916 or (fkn_requisitionid = 226026
and n_unitsordered > 0 and d_datepicked is null and fkn_parentid = 0)
group by s.s_finedept
order by s.s_finedept, pkn_requisitionitemid

It is not the query that is at fault even though it could be
optimised. Adding the index just caused it to die.

Thanks for any help given as we are having major problems optimising
because we are not able to trust the adding of new indexes.