Hello Andrew!

I love plans, it is fun to try to understand what is going on! I will try
not to just solve your problem, but give you a guide as to how you in
general may try to optimise them. Daniel has already given you one
important hint when things are to slow: try to change your query.

I'll start by what I think will be minor improvements:

>PLAN JOIN (
> D INDEX (IDX_DOCDATE,RDB$FOREIGN38,RDB$FOREIGN40),
> X INDEX (RDB$FOREIGN248,RDB$FOREIGN246),
> T INDEX (RDB$PRIMARY41)

I assume IDX_DOCDATE is referring to 'and d.DocDate < CURRENT_TIMESTAMP',
something which in many cases will refer to most records. Such an index is
futile to use, get rid of it in your plan by changing to 'and (d.DocDate <
CURRENT_TIMESTAMP or 2=0)'

And then I wonder what the two other indexes for table D are. One of them
must be for d.DepID, and the other one is probably for d.customerid? If so,
am I right in guessing that d.customerid is far more selective than
d.DepID? If I am still right, it could be worth trying to use the "or 2=0"
trick on the d.DepID (it doesn't matter what numbers you choose, just
something that always evaluates to false - we're just trying to limit the
optimiser in choosing indexes).

Moving on to table X we have one index for x.dstid and another one for
x.docid. Again, if one of the indexes is far more selective than the other
one, eliminate the least selective through the trick above.

Table T seems to have a good index, but depending on its size and ability
to limit the number of interesting rows from either of the two tables
above, you may prefer to have it higher up in the plan. If so you may have
to specify the plan manually. Generally, however, I only resort to that if
it is strictly required, since you have no guarantee that the number of the
foreign indexes will remain the same forever.

Lastly, I think your select is a bit confusing putting everything in the
join clause. I would have started with

select sum(x.Price*x.Qty/d.Rate) from xfers x
join docs d on d.id = x.docid and d.customerid = x.dstid
join optypes t on x.typeid=t.id
where x.dstid = :id
and d.DepID = :dep
and d.DocDate < CURRENT_TIMESTAMP
and t.factor <> 0

(Helen told me a few days ago that <> is allowed. And I have used NOT
(t.factor = 0) for a long time...)

and then modified things until the plan seemed OK.

Have a nice weekend,

Set

- I support Firebird, I am a FirebirdSQL Foundation member.
- Join today at http://www.firebirdsql.org/ff/foundation