Subject Re: [firebird-support] Left join plans
Author Dmitry Yemanov
Rick Debay wrote:

> I would expect the first query to be the best, as within the parenthesis
> I explicitly state that the three tables can use an inner join.
> Instead, the looser second query where all items use a left join
> performs much better, and the plan reflects this by only having one
> NATURAL scan. The first query has a NATURAL scan for each of the two
> table groupings.
> Does the optimizer know how to handle parenthesized groupings, or is it
> not a special case?

It's a known issue with the mixed inner/left join handling. If you have
two inner join sets separated by a left join, you shouldn't expect the
query (actually, its right part) to be optimized really well. Once
you've explicitly made the problematic streams linked via left joins,
the problem disappears.