Subject Re: [firebird-support] Query plan insists on NOT USING an index
Author Dmitry Yemanov
Svein Erling Tysvaer wrote:
> If this is still true for Fb 2, that is actually one of the things I do
> not understand about Firebird. I would understand if the INNER JOIN was
> joined to a LEFT JOINed table or group, but when it is JOINed to one of
> the other tables, I do not understand why the optimizer doesn't treat
> this the same regardless of whether it is before or after LEFT JOINs in
> the query. It's a long time since I started putting LEFT JOINs at the
> end of my queries and haven't experienced practical problems with this,
> but your example of inner joining a view that contains left joins is
> certainly a good example of why this may be of greater importance than I
> thought.

Yes, it's a known issue that still exists. If two inner joins are
separated by an outer one, the engine will optimize them as separate
rivers with a pre-defined join order and hence the last inner join would
have no chances to be considered as the first stream in the access path.
This is a side effect of the way outer joins are processed. Sigh.