>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
