|Subject||Re: [firebird-support] V1.56 query killing my V2.54 app|
|Author||Svein Erling Tysvær|
>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.