Svein Erling Tysvaer wrote:

>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 test and modify queries till they become acceptable for end-user
application. It is my rule.

>
>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)'
>

Almost all records of table Docs have DocDate < Current_Timestamp
I even tried to remove that selection criteria, but nothing changes.

>
>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)
>

Index table field statistics
-----------------------------------------------------
IDX_DOCDATE DOCS DOCDATE 0.000030019211408
RDB$FOREIGN38 DOCS DEPID 0.500000000000000
RDB$FOREIGN40 DOCS CUSTOMERID 0.000289268151391
RDB$FOREIGN248 XFERS DOCID 0.000037572797737
RDB$FOREIGN246 XFERS DSTID 0.000353356881533
RDB$PRIMARY41 OPTYPES ID 0.050000000745058

OpTypes indexed reads 3186
xfers indexed reads 3186
Docs indexed reads 5794

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

Table T (optypes) contains about 20 records

>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
>

This version was tried already.

>
>and then modified things until the plan seemed OK.
>
>Have a nice weekend,
>

Tomorrow is going to be a hard day for me. I will deploy structural and
logic changes to production database.

Thank you very much! And many thanks to all Firebird community for
making things possible.

Andrew Guts
Senior software developer
Future Electronics^(TM)
http://www.fe.ua
http://price.fe.ua