I am using Firebird 2.5.5 x64 on Windows 8.1 x64.  I wrote an application access a Firebird database.  So far so good.

One day, I notice a function running query accessing the database become extremely slow.  After I debug, I found the query is the cause:

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

Further investigation and I found an interesting problem.  If the database file size is below 8GB, the query works normal.  Once the database file size grow beyond 8GB, the query become very slow.

Here is the plan before 8GB:

PLAN JOIN (B INDEX (SL_CSDTL_ITEMCODE), A INDEX (RDB$PRIMARY212))

Adapted Plan

PLAN JOIN (B INDEX (SL_CSDTL_ITEMCODE), A INDEX (INTEG_2872))

------ Performance info ------

Prepare time = 0ms

Execute time = 312ms

Avg fetch time = 312.00 ms

Current memory = 14,858,352

Max memory = 14,972,720

Memory buffers = 2,048

Reads from disk to cache = 42,429

Writes from cache to disk = 0

Fetches from cache = 360,726

And the plan after grow to 8GB:

PLAN JOIN (A INDEX (SL_CS_CODE), B INDEX (SL_CSDTL_DOCKEY, SL_CSDTL_ITEMCODE))

------ Performance info ------

Prepare time = 0ms

Execute time = 2m 0s 875ms

Avg fetch time = 120,875.00 ms

Current memory = 14,943,040

Max memory = 15,005,768

Memory buffers = 2,048

Reads from disk to cache = 44,818

Writes from cache to disk = 0

Fetches from cache = 11,454,178

Both plan works on same tables with same row count.  Increasing of database file size is due to insertion on other tables.  I believe the performance issue was due to "Fetches from cache" increasing dramatically on 2nd plan.

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