I would guess

JOIN RPL_POBILL_ITM pbi
ON pbi.INVOICE+0 = pb.ID

would make Fb 2.0 reconsider its plan. At first I thought disabling
FK_POBILL_PURCHORDER, but since RPL_POBILL only have 4982 rows, that
may not be enough to make Firebird change it's plan. But it will
certainly not consider going through the biggest table in natural
order to be better than using an index (well, I still don't use Fb
2.0, but the optimizer cannot have changed that dramatically).

HTH,
Set

--- In firebird-support@yahoogroups.com, "Rick Debay" wrote:
> RPL_PO 4029 rows
> RPL_POBILL 4982 rows
> RPL_POBILL_ITM 142282 rows
> MDDB_DRUG 135897 rows
>
> FB 2 plan takes 0.141 seconds, FB 1.5 plan takes 0.046 seconds.
>
> The idea of disabling the index was to force it to generate a plan
> similar to 1.5. I've already listed most of the plans and index
> statistics in the thread titled 'Good plan with FB 1.5, bad plan
> with FB 2.0'
> >
> > FB 2.0, not using primary key
> > PLAN JOIN (PO NATURAL, PB INDEX (FK_POBILL_PURCHORDER), PBI INDEX
> > (FK_POBILLITM_POBILL), D INDEX (PK_MDDB_DRUG))
> >
> > FB 1.5, using primary key
> > PLAN JOIN (D INDEX (I_MDDBDRUG_GPI),PBI INDEX
> > (U_RPL_POBILL_ITM),PB INDEX (PK_RPL_POBILL),PO INDEX (PK_RPL_PO))
>
>
> > SELECT
> > pbi.ID, pbi.QTY
> > FROM
> > RPL_PO po
> > JOIN RPL_POBILL pb
> > ON pb.PO = po.ID
> > JOIN RPL_POBILL_ITM pbi
> > ON pbi.INVOICE = pb.ID
> > JOIN MDDB_DRUG d
> > ON d.PRODUCTID = pbi.NDC
> > WHERE
> > /* disable index under FB 2.0 */
> > po.REPLENISH_GRP || '' = '01E' AND
> > d.MULTISRC = 'Y' AND
> > d.GPI = '78512015000322' AND
> > d.TEE = 'NR' AND
> > d.UNIT_DOSE_USE = 'U' AND
> > (d.ATOM <> 'Y' OR d.PKG_SZ = 100)