Hi all,

I have a simple query like the following


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

Well in a 2GB database it takes about 28 seconds to execute.

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.

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

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

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

If I change the order, I continue gettint the same error for the new
index specified.

Why I can specify an index in the first position of the plan?

If I change to the following plan

PLAN JOIN(MR NATURAL, R INDEX(PK_REVISION), CI INDEX
(A3_FIDREVISION) )

The query executes in 400ms!!!

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

Thanks

pablo