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