--- In firebird-support@yahoogroups.com, "Rick Debay" <rdebay@...> wrote:

>
> Any idea why the optimizer (on both FB 1.5.3 and 2.0rc1) chooses a
> natural plan and ignores the index on PBM_CLAIM.DATESBM?

The index has bad (but not as terrible as I have seen selectivity,
around 600 if my math is right). The optimiser has judged it as not
overly useful. To put it another way, the optimiser has come up with
different ways it can solve the query, and either because there is a
faster way or because the indices are not accurate, the optimiser
estimates that a natural read on MDDB_DRUG is less expensive.

Remember that the ideal plan is data dependent and should vary from
one database to another. If you are using a test database, I recommend
where possible using a large 'real' database for testing, as test data
can often mislead as to which plan would be used in real life.

> The duplicates in the index shouldn't be a problem, there number of
> transactions a day vary from less than a hundred to just over a
> thousand.
>
> select
> p.ACCOUNTID, p.SRVPROVID, p.PRODUCTID, d.GPI
> from
> pbm_claim p
> JOIN MDDB_DRUG d
> ON d.PRODUCTID = p.PRODUCTID
> where
> p.datesbm < ?
>
> PLAN SORT (JOIN (D NATURAL, P INDEX (I_PBM_CLAIM_NDC)))
>
> ALTER TABLE MDDB_DRUG ADD CONSTRAINT PK_MDDB_DRUG PRIMARY KEY
> (PRODUCTID);
> CREATE DESC INDEX I_PBM_CLAIM_DATESBM_DESC ON PBM_CLAIM (DATESBM);
> CREATE ASC INDEX I_PBMCLAIM_DATESBM ON PBM_CLAIM (DATESBM);
> CREATE ASC INDEX I_PBM_CLAIM_NDC ON PBM_CLAIM (PRODUCTID);
> CREATE ASC INDEX I_PBMCLAIM_DECIMALQTY ON PBM_CLAIM (DECIMALQTY);

May I ask why you have defined the index 'I_PBM_CLAIM_NDC' when it
looks to me like it is a foreign key? In Firebird, foreign keys
automatically create a supporting index, and there are known quirks
when the same index is defined twice. It may be that simple.

What you can do if you are sure the in the real world the plan it
generates is suboptimal is to prevent it from using an index. This
will artifically increase the cost of the join to the suboptimal plan,
which makes it less desirable to the optimiser which should hopefully
then choose the optimal plan.

select
p.ACCOUNTID, p.SRVPROVID, p.PRODUCTID, d.GPI
from
pbm_claim p
JOIN MDDB_DRUG d
ON d.PRODUCTID = p.PRODUCTID+0
where
p.datesbm < ?

Adding 0 should prevent I_PBM_CLAIM_NDC from being available to the
suboptimal plan. (I usually get the +0 mixed up so if it doesn't
change it, move it to the other side of the = sign).

Adam