Combining the two indices gives:

Index I_MDDBDRUG_CORE9_GPI (3)
Depth: 3, leaf buckets: 349, nodes: 135897
Average data length: 35.76, total dup: 10359, max dup: 48
Fill distribution:
0 - 19% = 1
20 - 39% = 0
40 - 59% = 0
60 - 79% = 0
80 - 99% = 348

But the optimizer still chooses the old plan. Is this a case where it
isn't possible for the optimzer to make the correct choice, or is it a
problem with the optimizer?

-----Original Message-----
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Rick Debay
Sent: Friday, November 17, 2006 5:30 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Good plan with FB 1.5, bad plan with FB 2.0

This query is over seven time slower under FB 2.0. If I set the plan as
the one generated by 1.5, it is equivalent.
The question is why, and how I can apply that knowledge against the rest
of my database without profiling every query.

I think 2.0 sees the number of duplicates in I_MDDBDRUG_GPI, and decides
to approach it by starting with RPL_PO. FB 1.5 starts from the other
end at MDDB_DRUG. I don't know if the statistics below look bad, but I
dumped the data and used group by and count to show that except for a
small number of items, the selectivity is extremely good.

Starting at RPL_PO will also explode the number of results when it
reaches the link table (wrapped in a view) V_MDDB_NDC_CHANGE. Going
backwards from MDDB_DRUG (wrapped in V_MDDB_NDC_CHANGE) increases the
results by only a few items at most.

SELECT
pbi.ID, pbi.QTY, d.ATOM, d.PKG_SZ
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
JOIN V_MDDB_NDC_CHANGE nc
ON nc.NEW_PID_CORE9 = d.PID_CORE9 AND
nc.NEW_GPI = d.GPI AND
nc.NEW_TEE = d.TEE AND
nc.NEW_UNIT_DOSE_USE = d.UNIT_DOSE_USE AND
(nc.NEW_ATOM <> 'Y' OR nc.NEW_PKG_SZ = d.PKG_SZ)
WHERE
po.REPLENISH_GRP = ? AND
pbi.QTY+0 > 0 AND
nc.OLD_PID_CORE9 = ? AND
nc.OLD_GPI = ? AND
nc.OLD_TEE = ? AND
nc.OLD_UNIT_DOSE_USE = '' AND
(nc.OLD_ATOM <> 'Y' OR nc.OLD_PKG_SZ = ?)
ORDER BY
ABS(pb.SHIPPED - ?)

FB 1.5
PLAN SORT (JOIN (NC MD_OLD INDEX (I_MDDBDRUG_GPI,I_MDDB_DRUG_CORE9),NC
NC INDEX (I_MDDB_NDC_CHANGE_OLDNDC),NC MD_NEW INDEX (PK_MDDB_DRUG),D
INDEX (I_MDDBDRUG_GPI,I_MDDB_DRUG_CORE9),PBI INDEX (U_RPL_POBILL_ITM),PB
INDEX (PK_RPL_POBILL),PO INDEX (PK_RPL_PO))) FB 2.0 PLAN SORT (JOIN (PO
INDEX (FK_RPLPO_REPLGRP), PB INDEX (FK_POBILL_PURCHORDER), PBI INDEX
(FK_POBILLITM_POBILL), D INDEX (PK_MDDB_DRUG), NC MD_NEW INDEX
(I_MDDB_DRUG_CORE9, I_MDDBDRUG_GPI), NC NC INDEX
(I_MDDB_NDC_CHANGE_NEWNDC), NC MD_OLD INDEX (PK_MDDB_DRUG)))

Index I_MDDBDRUG_GPI (1)
Depth: 2, leaf buckets: 78, nodes: 135897
Average data length: 4.09, total dup: 89618, max dup: 802
Fill distribution: 0 - 19% = 0 20 - 39% = 1 40 - 59% = 0 60 -
79% = 0 80 - 99% = 77

Index I_MDDB_DRUG_CORE9 (2)
Depth: 2, leaf buckets: 45, nodes: 135897
Average data length: 0.62, total dup: 68089, max dup: 89
Fill distribution: 0 - 19% = 0 20 - 39% = 0 40 - 59% = 0 60 -
79% = 1 80 - 99% = 44