Re: 8.2.4 serious slowdown

Hello

On 11/01/2008, Sim Zacks <sim(at)compulab(dot)co(dot)il> wrote:
> I changed it to "where f.commited is not true" and the query now takes 1 second as opposed to 60.
> (much faster then the 3 seconds it took on 8.0.1, which could also be because of the coalesce there)
> Is it considered better practice (or more efficient) to always use (x is not or x=value)
> instead of coalesce? Or does it make more sense to turn on the option "transform_null_equals"?
>

You can use without coalesce() = some operator IS DISTINCT FROM ... .
Use coalesce only if you need some NON NULL value.

for you sample

where f.commited IS DISTINCT FROM true;

operator IS DISTINCT FROM is NULL insensitive

Regards
Pavel Stehule

> Thank you much
> Sim
>
> > I assume that the original query is something along the lines of
> >
> > d left join f on (...) where coalesce(f.commited, false) = false
> >
> >
> > In the meantime, Sim would probably have better luck if he restructured
> > this particular clause in some other way, say
> >
> > where f.commited is not true
> > or
> > where f.commited = false or f.commited is null
> >
> > Note also that he really ought to move up to 8.2.6, as 8.2.4 is not
> > very sane about what IS NULL means for a left join's result.
> >
> > regards, tom lane
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: explain analyze is your friend
> >
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>