At 03:45 AM 17/09/2003 +0000, you wrote:

>Sorry about the Chrystal Ball. I come from a long line of Gypsies.
>
>The version of the database is FireBird 1.5
>
>The index is on integer Gen_tabl_RequisitionItem.n_orderedunits
>it is caled GEN_TABL_REQUISITION_IDX5
>
>Here is 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 = 260874
>or (fkn_requisitionid = 222824and n_unitsordered > 0
> and d_datepicked is null and fkn_parentid = 0)
>group by s.s_finedept order by s.s_finedept, pkn_requisitionitemid
>
>
>Query Plan with index disabled
>
>Plan
>PLAN SORT (SORT (JOIN (S NATURAL,I INDEX
>(GEN_TABL_REQUISITIONITEM_IDX3,GEN_TABL_REQUISITIONITEM_IDX2,RDB$PRIMA
>RY90))))
>Adapted Plan
>PLAN SORT (SORT (JOIN (S NATURAL,I INDEX
>(GEN_TABL_REQUISITIONITEM_IDX3,GEN_TABL_REQUISITIONITEM_IDX2,INTEG_297
>))))
>
>Query Plan with index enabled
>
>Plan
>PLAN SORT (SORT (JOIN (S NATURAL,I INDEX
>(GEN_TABL_REQUISITIONITEM_IDX3,GEN_TABL_REQUISITIONITEM_IDX2,RDB$PRIMA
>RY90,GEN_TABL_REQUISITIONITEM_IDX5))))
>Adapted Plan
>PLAN SORT (SORT (JOIN (S NATURAL,I INDEX
>(GEN_TABL_REQUISITIONITEM_IDX3,GEN_TABL_REQUISITIONITEM_IDX2,INTEG_297
>,GEN_TABL_REQUISITIONITEM_IDX5))))
>
>
>If I take out the first part of the "or" out it runs fine
>
>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_requisitionid = 222824 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
>
>Plan
>PLAN SORT (SORT (JOIN (I INDEX
>(RDB$PRIMARY90,GEN_TABL_REQUISITIONITEM_IDX5),S INDEX
>(GEN_TABL_STOCKITEM_IDX2))))
>Adapted Plan
>PLAN SORT (SORT (JOIN (I INDEX
>(INTEG_297,GEN_TABL_REQUISITIONITEM_IDX5),S INDEX
>(GEN_TABL_STOCKITEM_IDX2))))
>
>I hope that this is the info you seek

Right, start off by getting the syntax into good order:

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 i.FKN_PICKSLIPID = 260874
or (i.fkn_requisitionid = 222824and i.n_unitsordered > 0
and i.d_datepicked is null and i.fkn_parentid = 0)
group by s.s_finedept
order by /* s.s_finedept, */ i.pkn_requisitionitemid

Do watch the identifiers on join specs. Firebird 1.5 rejects ambiguous
join specs but, even if they don't resolve (eventually) to ambiguities,
you're potentially adding expense to the optimization process by forcing
the optzr to calculate something that you can tell it.

You don't need the first ORDER BY criterion, because this sort is done
later by the GROUP BY criterion. That ORDER sort should be much faster
without that redundant criterion. You are going to get all of the
finedepts in requisition order anyway.

Next, the other indexes are probably just as interesting to this query's
performance as the one you think is causing problems - and the same thing
will affect other queries involving either of these tables.

From the plans, I'm guessing that you have defined indexes on some columns
that already have integrity indexes (supporting foreign and primary
keys). Remove any composite indexes that step on those and "add them back"
as required, as single-column indexes.

It would be interesting to learn which of those indexes (if any) is driving
the GROUP BY. If you have an index on s.s_finedept, and there are only a
few possible values, then this index has to be avoided.

That's about the best I can offer for now, since your index naming scheme
is also in the "crystal ball" style. Stay tuned (if you can stay awake
<g>) for others who love these puzzles to come on line.

heLen