Hello IB support,

Please help me to compose a plan for this query :

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

The default plan is:

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

The query works relatively slow (2.6s) against this partial version
(40ms) :

select sum(x.Price*x.Qty) from xfers x
inner join optypes t on x.typeid = t.id and t.factor <> 0 and x.dstid = :id

Default plan is: PLAN JOIN (T NATURAL,X INDEX
(RDB$FOREIGN241,RDB$FOREIGN246))

But I have to consider Rate, DepID and DocDate from table Docs.

First query works slowly because scans table Docs first and then joins
table xfers.
Table xfers contains above 100000 compact rows (FKs and numerics) and
table Docs about 50000 large rows (PK, strings, FKs, timestamps, etc)
I want to write plan manually to enforce using index RDB$FOREIGN246
(xfers.dstid) ahead.

I hope someone can help. Reference to good article explaining plan
syntax would be great too.

Thanks,

Andrew