> First, you do have at least one irrelevant index, there is no need for a
> separate index on any field that is the first field of a composite index
> (at least, I've never heard anyone claim that such an index did any
> good). So just delete I_TO6PCID_A and all other indexes that you may
> have that is only the first field(s) of another index (i.e. delete the
> index on (a) if you also have an index on (a, b), and delete the index
> on (a, b) if you have an index on (a, b, c). (please, someone correct me
> if I'm wrong).
>
> Then check the plan again in both Firebird 1.5 and Firebird 2.0. I don't
> think it changes anything, but it is worth trying.

You are right, nothing changes.

>
> Then, let's look at the ON and WHERE clause of your subselect to try
> making it use the same plan as Firebird 1.5 chose (I think the outer
> select is identical, just a matter of Firebird 2.0 being more 'verbose'
> than Firebird 1.5 in displaying the plan). I'll add aliases to the
> tables to make it easier for myself (B, C and TPC):
>
> on TPC.TO6CSID=C.CSID where C.CSUSID1=56 and C.CSGPID3=65 and
> B.BLPCID=TPC.TO6PCID
>
> Hmm, we have to do something to make CUSTOMER more attractive than
> TURNOVER_PC_CS. If TO6CSID is pretty selective and you do have an index
> which starts with this field, then you can of course just try
> B.BLPCID=TPC.TO6PCID+0. If not, well, I've never tried, but what happens
> if you do AND (B.BLPCID=TPC.TO6PCID or C.CSUSID1=-10) //-10 has to be a
> value that doesn't exist and CSUSID1 has to be the first field of an
> index.

Again you are right, when I try
AND (B.BLPCID=TPC.TO6PCID or C.CSUSID1=-10)
I get less indexed reads on CUSTOMERS, but a lot of more indexed reads
on TURNOVER_PC_CS.
Now:
BACKLOG 216 IR
CUSTOMERS 2290 IR
TURNOVER_PC_CS 185.564 IR

Before:
BACKLOG 216 IR
CUSTOMERS 11992 IR
TURNOVER_PC_CS 11992 IR

> I'm a bit too tired to come up with something better. I recommend trying
> to stick with the NOT EXISTS.

The correct query is
select BLPCID,sum(BLGROSS_SALES),sum(BLRETURNS),sum(BLNETSALES),
sum(BLDELIVERY_QUANTITY),sum(BLBACKLOG)
from BACKLOG join CUSTOMERS on BLCSID=CSID and CSUSID1=56 and CSGPID3=65
left join TURNOVER_PC_CS on BLPCID=TO6PCID
where TO6PCID is null
group by BLPCID

but the performance is terrible, so I agree with you to use NOT EXISTS.

Regards and thanks for your help

Guido