cf should be actually t1, I rewrote the query before posting here,
and overlooked one place.

The plan ..., well, actually I have managed to avoid dealing with the
query plans so far, but it seems that I still need to learn this
stuff :)

Aivar



--- In firebird-support@yahoogroups.com, Svein Erling Tysvær
<svein.erling.tysvaer@k...> wrote:

> Where is the cf coming from? And what is the chosen plan for each

of

> these queries? I don't know of any general way to arrange queries,

but

> looking at the plan often suggests how things can be improved.
>
> Set
>
> --- In firebird-support@yahoogroups.com, "kimon_the_athenian2"

wrote:

> > Hi!
> >
> > I noticed a huge difference of query speed when i switched table2
> > and table3 joins in FROM clause from:
> >
> > select t1.stuff, t2.stuff, t3.stuff
> > from table1 t1
> > join table2 t2 on t2.id = t1.field4
> > left join table3 t3 on t3.field1 = cf.field1 and t3.field2 = cf.
> field2
> > where t1.field1 = :p1
> > and t1.field2 = :p2
> > and t1.field3 = :p3
> >
> > to:
> >
> > select t1.stuff, t2.stuff, t3.stuff
> > from table1 t1
> > left join table3 t3 on t3.field1 = cf.field1 and t3.field2 = cf.
> field2
> > join table2 t2 on t2.id = t1.field4
> > where t1.field1 = :p1
> > and t1.field2 = :p2
> > and t1.field3 = :p3
> >
> > First query run for 10 mins, the second for 2 secs (FB 1.5.2).
> >
> > As far as I understand swithcing these joins should always give
> > equivivalent result set, is it so?
> >
> > Somehow it feels more logical to me to put all inner joins before
> > left ones, but seems that I need to reconsider this :)
> >
> > Is there some text describing how should I arrange joins?
> >
> > thanks in advance!
> >
> > Aivar