Hi!

The problem is obviously that Firebird thinks it can do a better job
if it uses this new index for this particular query. I can think of
two ways to solve your problem, the best being to create a small
program which prepare all your queries and reports if any of them have
changed plan since last time you ran the program. That way you would
at least know which queries to examine rather than have to manually
check all of them.

Thinking about it, that could be a nice addition to Database WorkBench
(to tell which queries will change plan with the addition/removal of
indexes). Or maybe you've got something like that already, Martijn (or
is it beyond the purpose of Workbench)?

Set

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

> wrote:
> 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
>
> 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
>
> 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.