Rick Debay wrote:

> Firebird 2.0 chooses a plan with NATURAL in it, when there is a
> perfectly good primary key available. Firebird 1.5 doesn't make this
> mistake.
>
> 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))

The problem is not a primary key vs natural per se. FB 2.0 has chosen
completely different join order, starting with table RPL_PO. As it
doesn't have any indexed filtering conditions, the access method is NATURAL.

How many records are in table RPL_PO? The optimizer seem to think it's
small enough.

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

Why do you disable that index for column REPLENISH_GRP? How many records
remain after applying this condition to RPL_PO?


Dmitry