>Ok, used the +0 and worked.
>
>On v1.56 I was used with setting up a high granularity data column (col04Int - part of the primary key) with a True/false (0/1) type of column (ColDetSmIntFlag) to boost the selectivity of the index

>IXColDetSmIntFlag. I kept the index with that configuration for a "just in case". (The stat of the index is 0,000001407...)
>
>Set, don't get me wrong, I am very gratefull for your help and for Firebird, but saying that a Natural on a big table seems better than an index doesn't compute for me, and I've been using Firebird

>since Interbase and Oracle since v6 (as DBA BTW). At a least case scenario it should use the PK when there is a declared join using the PK. For me, the new optimizer is wierd and highly illogical.

Glad to hear that +0 worked.

We used Fb 1.5 for a long time (switched from 1.5 to 2.5 a year or two ago) and I’m happy to say that my impression generally is that the optimizer in 2.5 is better than it was in 1.5 and that the need for tricks like +0 is less than it used to be. Though there will of course be differences, and it is not a surprise that you notice some drawbacks rather than benefits when upgrading a particular database (I assume you have optimized poorly performing queries with Fb 1.5 long ago, rather than been waiting for the upgrade). If you compare new, unoptimized queries between 1.5 and 2.5, odds are that you would conclude that 2.5 are better.

I guess the reason for the optimizer preferring the NATURAL plan is a combination of msttbl having about half the number of records of dettbl and the index on ColDetSmIntFlag having lousy selectivity (the optimizer doesn’t have histograms yet (I am uncertain whether histograms are part of Firebird 3 or if it will be a later release), and doesn’t know that 1 is a less frequent value than 0).

Set