--- In firebird-support@yahoogroups.com, "Helmut Hartl" wrote:

> Hello Svein !
>
> Your trick with a.id+0 speeds up my App from 60 Seconds to nearly
> zero. I am really happy with your answer !! :-)
>
> Can you give me a hint, why this is so ?
>
> I just experimented a bit and adding 0 to other ID's in the JOIN
> will make the behaviour even worse by tripling and doubling the
> time. But in the right place it works like magic.

Hi again, Helmut, glad to hear that it worked. I put it there because
looking at your original plan

PLAN JOIN (C NATURAL,B INDEX (PK_T2),A INDEX (T1_IDX1,PK_T1))

I notice that it uses two indexes for A and none for C. The plan you
reported as quicker, uses similar indexes, but not PK_T1.

PLAN JOIN (A INDEX (T1_IDX1),B INDEX (FK_T2),C INDEX (FK_T3))

So we want to prevent the query from using PK_T1, just to see if it
then chooses the quicker plan. Since the optimizer doesn't know that
A.ID = A.ID+0 and has no index for A.ID+0, that trick works in
preventing certain indexes from being used. Of course, adding +0
everywhere simply forces NATURAL all over the place, something that
generally is no good idea, but as you say used in the right place it
can be useful.

Set