>I would like to note that the fact that LEFT JOIN can generate an optimal plan where INNER JOIN fails indicates that all our
>index statistics are up-to-date and that there is some discrepancy between LEFT and INNER JOIN optimization that has already
>bit us several times. The good thing about LEFT is that it pushes SORT down to the table and uses an index on it whereas
>INNER does SORT after the join.

Firebird doesn't have histograms yet, and logically speaking (I do not know the internals of the optimizer, nor the selectivity of your indexes, so this is how I would think if I should do things manually):

COPY."SOURCE_ID" = '123431234' seems fairly selective, whereas

PHYSICAL_COPY."COMMIT_NUMBER" >= 10000000 seems like something that would match 50% (a bit less if considering NULLs) of the records.

Naturally, if I had to choose how to do this, I would think using the index for COPY would be far superior to using the index for PHYSICAL_COPY and INNER JOIN Firebird makes the same conclusion as I would have done (and as a second table, it is also natural to prefer the index for PHYSICAL_COPY.COPY_ID over PHYSICAL_COPY.COMMIT_NUMBER).

Adding LEFT, however, reduces the options for the optimizer. That word basically commands Firebird to consider PHYSICAL_COPY before COPY, and hence, it has to find an alternative plan (as a first table in the plan, PHYSICAL_COPY.COPY_ID is not an option, and PHYSICAL_COPY.COMMIT_NUMBER is the only choice besides NATURAL). You've shown us that this alternative plan in your situation is a lot better than the other, to me that basically says that 'COMMIT_NUMBER >= 10000000' actually is quite selective. Change that to 'COMMIT_NUMBER" >= -10000000' and it might be that LEFT JOIN will be as time consuming (or more) as the INNER JOIN. At prepare time, Firebird has no way of knowing that for values below a certain value it should use one plan, whereas for another value it should use a completely different plan (that might change with histograms).

HTH,
Set