Hello Ann, >Firebird 1.5 considered the index a good candidate, even if you only matched >the first part. In later, smarter versions, it recognizes that the first part >alone is not very good.
I did change the index to only the boolean 1/0 field... then I changed back adding a part of the primary key to boost a high graularity on the index trying to make things work as I needed. After removing the compuond part and running some tests with and without the +0 on the query I got: With the +0 PLAN SORT (SORT (JOIN (B INDEX (IXColDetSmIntFlag),A INDEX (PK_msttbl)))) Prepare time = 47ms Execute time = 2s 968ms Avg fetch time = 102,34 ms Current memory = 2.129.816 Max memory = 2.201.604 Memory buffers = 75 Reads from disk to cache = 302 Writes from cache to disk = 0 Fetches from cache = 1.555 without the +0 PLAN SORT (SORT (JOIN (A NATURAL, B INDEX (FK_dettbl)))) Prepare time = 16ms Execute time = 1m 20s 562ms Avg fetch time = 2.778,00 ms Current memory = 2.124.564 Max memory = 2.201.604 Memory buffers = 75 Reads from disk to cache = 157.563 Writes from cache to disk = 0 Fetches from cache = 2.909.887 Apparently I made it slightly better using some of v2.5x improvements. After more than a decade of Firebird I'm more than used to using the +0 to get the results desired from the engine but still, I find it illogical that scanning a table with "NATURAL" doesn't have a much higher cost than any other index to the engine. Well... new engine, new lessons. Thanks. Andrew
