Hello
On 11/01/2008, Sim Zacks <sim@xxxxxxxxxxxxxx> 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
>
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
