>Actually, the problem isn't with FK_dettbl, but with the A Natural. In a Master detail relationship with 450K+ rows in the master and 800K+ rows in the detail, that "natural" is a killer.

The problem is that Firebird thinks that

PLAN SORT (SORT (JOIN (A NATURAL, B INDEX (FK_dettbl))))

is a better choice than

PLAN SORT (SORT (JOIN (B INDEX (IXColDetSmIntFlag),A INDEX (PK_msttbl))))

The optimizer doesn't think NATURAL is better than using an index, it thinks that the selectivity of FK_dettbl is so much better than IXColDetSmIntFlag and PK_msttbl combined that it outweights the cost of going NATURAL on A.

Normally, it isn't all too bad to go natural on the first table of a plan, but b.ColDetSmIntFlag = 1 may be more selective than the optimizer thinks and then preventing a particular plan from being used can help speed up queries like yours. The +0 should prevent FK_dettbl from being used, and I'm pretty sure the optimizer will not try to go natural on both tables. Hence the optimizer should suggest another plan - hopefully the one you want.

HTH,
Set