Hi again,

I've created a new index on A2

CREATE INDEX A2_FIDMARKREV ON A2 (fidmarker, fidrevision);

Which gets even performance results and doesn't need the (+0) fix.

What I don't understand is why is why performance is improved so
much (from 28s in the original to 10ms this one) if it doesn't play
a role in the plan... Using IbManager's performance analysis I check
that the number of fetches is much, much lower, and hence
performance much better.

The new plan is like the following

PLAN JOIN (MR INDEX (A2_FIDMARKER),R INDEX (PK_A1),CI INDEX
(A3_FIDREVISION))

And the new index is not there.

Anyway, the new performance rocks!

What I see now is that maybe all the other queries can be greatly
improved choosing better indexes, which sounds obvious but I see
finding the right indexing mechanism is quite hard.. isn't it?

Thanks,

pablo

--- In firebird-support@yahoogroups.com, Svein Erling Tysvaer
<svein.erling.tysvaer@...> wrote:

>
> 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
>