psantosl wrote:

>
> SELECT
> ci.iobjid, ci.fiditem, ci.fidrevision,
> ci.sname, ci.fitemfsprotection
> FROM
> A1 r, A2 mr, A3 ci
> WHERE
> r.iobjid = mr.fidrevision AND
> ci.fidrevision = r.iobjid AND
> mr.fidmarker = 3358447
>
> Table A1 has about 50K registers
> Table A2 about 250K (but only 3 fields, integers)
> Table A3 has about 90K registers
>
> A3 is indexed by the field fidrevision.

What's the difference between indices MKRREAL_FIDREVISION and
A3_FIDREVISION?

> The proposed plan made by firebird (1.5, but the same is having
> problems with 2.0) is:

Did you try v2.0 against the same (ODS10) database or against an
upgraded (ODS11) one? Is your index statistics up-to-date?

> PLAN JOIN (CI NATURAL,R INDEX (PK_REVISION),MR INDEX
> (MKRREAL_FIDREVISION,MKRREAL_FIDMARKER))

Index MKRREAL_FIDMARKER looks unnecessary here.

> The problem is that each time I try to specify an index in the PLAN
> I get the following error
>
> index A3_FIDREVISION cannot be used in the specified plan

You don't show us how do you write the plan with a new index.

> If I change to the following plan
>
> PLAN JOIN(MR NATURAL, R INDEX(PK_REVISION), CI INDEX
> (A3_FIDREVISION) )

An easier workaround would be:
mr.fidmarker + 0 = 3358447

> The query executes in 400ms!!!

Not a surprise at all.

> But my question is: shouldn't be Firebird able to do it itself??

Yes it should.


Dmitry