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

small enough.

RPL_PO 4029 rows (126 records match on constraint)
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.

If I don't disable the index it takes 0.125 seconds with this plan:
PLAN JOIN (PO INDEX (FK_RPLPO_REPLGRP), PB INDEX (FK_POBILL_PURCHORDER),
PBI INDEX (FK_POBILLITM_POBILL), D INDEX (PK_MDDB_DRUG))

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'

Here's the table relationships:
RPL_PO<-RPL_POBILL<-RPL_POBILL_ITM->MDDB_DRUG

From what little I understand about plans, starting at RPL_PO is going
to end up fetching every item in RPL_POBILL_ITM that associated with an
RPL_PO with the value '01E', and then filter based on the conditions
imposed on MDDB_DRUG. What 1.5 does is find all items, based on the two
indexes, that match the constraints on MDDB_DRUG and then go backwards
through the foreign keys to find the record in RPL_PO that match the
constraints.

Going backwards starts with 7 items in MDDB_DRUG, which yields 24
records after linking back to RPL_PO, and of those zero match the
constraint on RPL_PO.
Going forwards, 8779 records in RPL_POBILL_ITM match the constraint on
RPL_PO.

So I'd like to get FB 2 to generate the FB 1.5 plan. The number of
items in MDDB_DRUG will remain more or less constant, but the number of
items in the other tables will increase over time.

-----Original Message-----
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Dmitry Yemanov
Sent: Tuesday, November 21, 2006 1:31 AM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Plan ignores PK and goes natural

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