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