Hi Pablo!

Firebird tries to find the best possible plan, but sometimes it doesn't
succeed. I would not be knowledgeable enough to explain why even if you
had provided enough information about your system, but know that
sometimes it helps modifying your query to prevent the optimizer from
using the plan it suggests.

Change your query to

SELECT
ci.iobjid, ci.fiditem, ci.fidrevision,
ci.sname, ci.fitemfsprotection
FROM
A1 r
JOIN A2 mr on r.iobjid = mr.fidrevision+0
JOIN A3 ci on r.iobjid = ci.fidrevision
WHERE
mr.fidmarker = 3358447

I did change from SQL-89 (implicit joins) to SQL-92 (explicit joins),
but the only important change is to add +0 to mr.fidrevision. I'm pretty
confident this would produce a plan similar to

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

HTH,
Set

psantosl wrote:

> 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