> My guess is that the rows you're added reduces the selectivity of
> SL_CSDTL_ITEMCODE and that this makes Firebird (incorrectly) believe that
> the bad plan becomes better than the good plan. You can prevent Firebird
> from using the bad plan by changing the query a bit (I've also changed it to
> using explicit joins, that is never worse and sometimes better than using
> implicit joins:
>
> SELECT MAX(A.PostDate) MaxPostDate
> FROM SL_CS A
> JOIN SL_CSDTL B ON A.DocKey=B.DocKey||'' --replace +'' with +0 if DocKey is
> a number WHERE A.Code='300-10001'
> AND B.ItemCode='OCC'
> AND B.UOM='UNIT'
> AND A.Cancelled='F'
> AND B.UnitPrice<>0

As usual SET has provided very good advice.

I would just add a couple of things.

1- Have you tested the query after a backup/restore? This would result in the index selectivity being recalculated, and thus the engine might choose a different plan.

2- Look at creating compound indexes on A.Code+A.Cancelled and/or B.DocKey+B.ItemCode+B.UOM -- where the order of the fields in the index would be based on either (a) the fields which have most unique values to those that are more common, (b) the reserve order from (a), or (c) the field which is most commonly queried, followed by the field which is often used in queries with the first field.