On 18/02/2013 12:46, Mark Rotteveel wrote:

>
> On Mon, 18 Feb 2013 12:10:00 +0100, Carsten Schäfer ca_schaefer@...
> <mailto:ca_schaefer%40gmx.de>>
> wrote:
> >> 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.
>
> The optimizer in Firebird simply can't do this for a COALESCE. It might
> work if you have an expression index on COALESCE(apos.f_dat_anlieferung,
> apos.f_dat_erstellung) (BTW: I am not 100% sure about this).
>

IIRC, there is some enhancement in 3.0 which will use an index on
COALESCE, but I can't find the tracker item at the moment.

Werner


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