Am 18.02.2013 08:46, schrieb Svein Erling Tysvær:

>
> >I have this simple select
> >select sum(apos.f_gewicht) as Gewicht
> >from t_apos apos
> >where COALESCE(apos.f_dat_anlieferung, apos.f_dat_erstellung)
> >between '2013-01-15' and '2013-01-16'
> >
> >which does an natural scan on table t_apos although an index is on
> >both fields.
> >
> >select sum(apos.f_gewicht) as Gewicht
> >from t_apos apos
> >where apos.f_dat_anlieferung between '2013-01-15' and '2013-01-16'
> >
> >and
> >
> >select sum(apos.f_gewicht) as Gewicht
> >from t_apos apos
> >where apos.f_dat_erstellung between '2013-01-15' and '2013-01-16'
> >
> >both uses the index .
> >Why is the index not used when coalesce is used?
> >I use Firebird 2.5.2 64bit on Windows 7.
>
> That's three very different queries, Carsten! A fairer comparison
> would have been:
>
> select sum(apos.f_gewicht) as Gewicht
> from t_apos apos
> where apos.f_dat_anlieferung between '2013-01-15' and '2013-01-16'
> or apos.f_dat_erstellung between '2013-01-15' and '2013-01-16'
>
> Actually, I don't know whether Firebird treats coalesce as if OR had
> been used. In some cases, I suspect it in theory could, but
> COALESCE(a, b) IS NULL would have to translate to AND rather than OR.
> Don't know whether this is implemented, or even if it would be
> beneficial if implemented - whenever I use coalesce in a WHERE clause,
> it is very rarely referring to different fields within the same tuple.
>

The comparison with your OR-example gives the same result.
The OR uses both indexes and coalesce is not using the indexes.
That's the problem.

Carsten





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