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]
