Subject Test case for Firebird indexing problem
Author starasoris
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.

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 =
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 which means that
every time I add an index to speed up one query I would have to test
all the other 100s of queries which is of course not practical.

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