After long discussion about how to optimize complex query Franz J. Fortuny wrote:
So, today's lesson, for me, is simply that any JOIN after you have placed LEFT OUTER JOINS will not use the proper indexes, and if you need such thing, then you must place the JOIN BEFORE the "left outer" JOINS.
Thanks for the lesson, group!
Svein Erling Tysvaer replied:
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.
Dmitry Yemanov answers:
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.