Обсуждение: Convert coalesce to or/and

I noticed that in the code coalesce is optimized by removing values
after non null variables.
Before seeing that, i would think that functions would not have been
optimized because in a sens, it hardcodes its behavior (i guess
coalesce could be overwritten).

So my question is :
would it be accepted to do a patch to replace coalesce by and/or,
mainly to fix related estimations.
This is an a mistake i fix from times to times on developments.
It could be restricted to coalesce containing only simple columns variables.

example:

explain analyze
select *
from people p
where coalesce(firstname, lastname) = 'Louis'
-- Seq Scan on people p (rows=732) (actual rows=3856.00 loops=1)
-- always computed to 732 for any value

explain analyze
select *
from people p
where firstname = 'Louis' or (firstname is null and lastname = 'Louis')
-- Seq Scan on people p (rows=3862) (actual rows=3856.00 loops=1)

Nicolas Adenis-Lamarre <nicolas.adenis.lamarre@gmail.com> writes:
> would it be accepted to do a patch to replace coalesce by and/or,
> mainly to fix related estimations.

Almost certainly not.  It'd be very hard to do that while preserving
the expected semantics of COALESCE: no argument is to be evaluated
more than once, and people sometimes expect strict left-to-right
evaluation.  I've even seen it used as an intentional optimization
fence.

If you think you can improve the estimation around it, I'd suggest
tackling that directly.

            regards, tom lane