Thanks Ann,

I make no excuses for the query. It was poorly written, but hindsight
is an unfair advantage. As I have said before, the query is generated
at runtime, which means we don't actually know what table will be
joined to what other table (ok, we do know it will be one of four),
but it can make things a bit tricky to figure out. :) I actually do
not blame the optimiser at all. If the query was written correctly,
then there would have been no problems, and if the query was analysed
using IBPlanalyzer or IBAdmin or something, then it would have been
painfully obvious that it was poorly designed.

Adam






--- In firebird-support@yahoogroups.com, "Ann W. Harrison"
<aharrison@i...> wrote:

> Adam wrote:
>
> >
> > Nope, switching join order for the sake of switching join order
> > doesn't help your cause but we needed left joins rather than

inner

> > joins.
>
> Firebird will select an optimum join order for inner joins,

regardless

> of the order of elements in the FROM clause. However, outer joins

can't

> be reordered in general - one good reason to avoid them if possible.
>
> Your example:
>
> select *
> from A
> left join B (on something)
> where B.testfield = 1
>
> I guess the optimizer should recognize that if the entire result

set is

> dependent on a non-null value for a field in B, the join isn't

actually

> an outer join - rows in A that do not have a matching B are not
> retained. It could then make an intelligent decision about the

order of

> A and B.
>
> Or possibly your query generator could do the same... That's

probably

> easier than teaching it about table cardinality and index

selectivity.

>
>
> Regards,
>
>
> Ann