>Query:
>
>SELECT MAX(A.PostDate) MaxPostDate
>FROM SL_CS A, SL_CSDTL B
>WHERE A.DocKey=B.DocKey
> AND A.Code='300-10001'
> AND B.ItemCode='OCC'
> AND B.UOM='UNIT'
> AND A.Cancelled='F'
> AND B.UnitPrice<>0
>
>Good plan:
>PLAN JOIN (B INDEX (SL_CSDTL_ITEMCODE), A INDEX (RDB$PRIMARY212))
>
>Bad plan:
>PLAN JOIN (A INDEX (SL_CS_CODE), B INDEX (SL_CSDTL_DOCKEY,

SL_CSDTL_ITEMCODE))

>
>Is this an issue related to Windows file system? Firebird service or

my query?

>
>It is strange why the firebird choose wtong plan when database file

size grow beyond 8GB. I thought it should be consistent for same query.

I'd say this is an issue mainly related to your query and the Firebird
optimizer, though I doubt it has anything to do with the file size. I am
also very happy that the plan changes depending on index selectivity and
not static. Think of a table PERSON that only contains a pair of twins:

PK Gender BirthDate
1 Female 15.1.1980
2 Male 15.1.1980

In this particular case, an index on GENDER would be equally selective
as PK and more selective than BIRTHDATE. Add 10 other persons, and in
most cases BIRTHDATE would become more selective than GENDER.

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

HTH,
Set