> Hi again, Guido!

Hi Svein,

I have been away for a couple of days.

>
> Seems like Fb 2.0 thinks it is better off to consider TURNOVER_PC before
> CUSTOMERS. First, try changing to
>
> select BLPCID,sum(BLGROSS_SALES),sum(BLRETURNS),sum(BLNETSALES),
> sum(BLDELIVERY_QUANTITY),sum(BLBACKLOG) from BACKLOG
> where BLCSSALESREPID=56 and BLCSGPID3=65 and
> not exists (select * from TURNOVER_PC_CS join CUSTOMERS on
> TO6CSID=CSID where CSUSID1=56 and CSGPID3=65 and BACKLOG.BLPCID=CSID)
> group by BLPCID
>
> I hope this doesn't change the plan at all (hopefully, Firebird consider
> the queries identical), but I'd like to know whether it does, so please
> report back the result.

You are right, it doesn't change anything.

>
> Then add +0 to see if that fixes the plan (I think it should):
>
> select BLPCID,sum(BLGROSS_SALES),sum(BLRETURNS),sum(BLNETSALES),
> sum(BLDELIVERY_QUANTITY),sum(BLBACKLOG) from BACKLOG
> where BLCSSALESREPID=56 and BLCSGPID3=65 and
> not exists (select * from TURNOVER_PC_CS join CUSTOMERS on
> TO6CSID=CSID+0 where CSUSID1=56 and CSGPID3=65 and BACKLOG.BLPCID=CSID)
> group by BLPCID

Yes it changes a lot, but it's getting worse.
Here's the plan:
PLAN JOIN (TURNOVER_PC_CS INDEX (I_TO6PCID_A), CUSTOMERS INDEX
(CUSTOMERS_IDX1))
PLAN (BACKLOG ORDER I_BLPCID_BLMCID_A INDEX
(I_BLCSGPID3_BLPCPAID_BLPCMPID_A, BACKLOG_IDX1))

and the reads
BACKLOG 216
CUSTOMERS 11.828.315 IR
TURNOVER_PC_CS 11992 IR

>
> I wouldn't bother about the change in the plan for BACKLOG, I think that
> is due to Fb 2.0 telling us more about what happens in the background,
> something that Fb 1.5 just kept as a secret.
>
> By the way, do you have several indexes that starts with the fields
> TURNOVER_PC_CS.TO6CSID or CUSTOMERS.CSID? If so, maybe that could
> confuse the optimizer?

No

Regards

Guido