Svein, the selectivity of source_id is 0.000446 and commit_number
is 0.000001. So, it is not clear why FB is not using index on commit_number
to filter AND sort on.

Thanks,

Alec

On Mon, Jun 4, 2012 at 1:11 AM, Svein Erling Tysv�r <
svein.erling.tysvaer@...> wrote:

> **
>
>
> >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
>
>
>

[Non-text portions of this message have been removed]