You're a genius. That worked, and now the query times are the same.
I think I'll add that elsewhere, instead of changing the where clause to remove the use of the FK on RPL_PO like I was doing in previous emails.

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Svein Erling Tysvær
Sent: Tuesday, November 21, 2006 12:56 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Re: Plan ignores PK and goes natural

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)




++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Yahoo! Groups Links