>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
