Hi all,

I'm trying to upgrade an app to v2.54 from v1.56 but there are some queries that aren't planning the way they should.

I have this query:

Select Distinct a.col01Int, a.col02SmInt, a.col03SmInt, a.col04VarCh45
from  dettbl b
   Inner Join msttbl a on (a.col01Int = i.col01Int and
                           a.col02SmInt = i.col02SmInt and
                           a.col03SmInt = i.col03SmInt)
where
   b.ColDetSmIntFlag = 1
Order by a.col04VarCh45

on v1.56 I get this plan:

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

and on v2.54 I get

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

that is killing my app. The tables in question have 450K+ and 800K+ rows.

The DDL for the tables on both versions of Firebird are:

CREATE TABLE msttbl (
    col01Int            DM_INTEGNNULL NOT NULL /* DM_INTEGNNULL = INTEGER NOT NULL CHECK (value > 0) */,
    col02SmInt          DM_FLAGNNULL NOT NULL /* DM_FLAGNNULL = SMALLINT NOT NULL CHECK (value > 0) */,
    col03SMInt          DM_FLAGNNULL NOT NULL /* DM_FLAGNNULL = SMALLINT NOT NULL CHECK (value > 0) */,
    col04VarCh45        DM_NOMESNNULL /* DM_NOMESNNULL = VARCHAR(45) NOT NULL */
);

ALTER TABLE msttbl ADD CONSTRAINT PK_msttbl PRIMARY KEY (col01Int, col02Int, col03Int);
CREATE INDEX IXmsttbl_col04VarCh45 ON msttbl (col04VarCh45);


CREATE TABLE dettbl (
    col01Int            DM_INTEGNNULL NOT NULL /* DM_INTEGNNULL = INTEGER NOT NULL CHECK (value > 0) */,
    col02SmInt          DM_FLAGNNULL NOT NULL /* DM_FLAGNNULL = SMALLINT NOT NULL CHECK (value > 0) */,
    col03SMInt          DM_FLAGNNULL NOT NULL /* DM_FLAGNNULL = SMALLINT NOT NULL CHECK (value > 0) */,
    col04Int            DM_INTEGNNULL NOT NULL /* DM_INTEGNNULL = INTEGER NOT NULL CHECK (value > 0) */,
    ColDetSmIntFlag     DM_FLAGNNULL NOT NULL /* DM_FLAGNNULL = SMALLINT NOT NULL CHECK (value > 0) */
);

ALTER TABLE dettbl ADD CONSTRAINT PK_dettbl PRIMARY KEY col01Int, col02Int, col03Int, col04Int);
ALTER TABLE dettbl ADD CONSTRAINT FK_dettbl FOREIGN KEY (col01Int, col02Int, col03Int) REFERENCES msttbl (col01Int, col02Int, col03Int);

CREATE INDEX IXColDetSmIntFlag ON dettbl (ColDetSmIntFlag, col04Int);

I've been changing the query and trying to change indexes to no success.

Is there a way to force FB 2.54 to chose the right indexes? 

TIA

Andrew