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
I attached a patch proposition.
explain analyze
select *
from people p
where coalesce(firstname, lastname) = 'Louis'
-- before: Seq Scan on people p (cost=0.00..4015.04 rows=732
width=321) (actual time=0.019..11.217 rows=3856.00 loops=1)
-- after: Seq Scan on people p (cost=0.00..4015.04 rows=3872
width=177) (actual time=0.026..13.730 rows=3856.00 loops=1)
explain analyze
select *
from people p
where firstname = 'Louis' or (firstname is null and lastname = 'Louis')
-- Seq Scan on people p (cost=0.00..4381.24 rows=3872 width=177)
(actual time=0.016..14.899 rows=3856.00 loops=1)
Nicolas
Attachments:
v1-0001-Estimate-coalesce-returned-rows-as-done-for-or-claus.patchtext/x-patch; charset=US-ASCII; name=v1-0001-Estimate-coalesce-returned-rows-as-done-for-or-claus.patchDownload+169-1
I did a version 2
while it think we should avoid it if the coalesce is not composed of
simple elements.
Nicolas
Attachments:
v2-0001-Estimate-coalesce-returned-rows-as-done-for-or-claus.patchtext/x-patch; charset=US-ASCII; name=v2-0001-Estimate-coalesce-returned-rows-as-done-for-or-claus.patchDownload+182-1
Hi,
I didn't get a feedback on this patch.
I can just remove it if thats not a wanted optimisation for any reason.
Just to be sure it was not missed or if i should upload it for the
commit fest or something else.
Thanks.
On Wed, 2026-02-18 at 10:54 +0100, Nicolas Adenis-Lamarre wrote:
I didn't get a feedback on this patch.
I can just remove it if thats not a wanted optimisation for any reason.
Just to be sure it was not missed or if i should upload it for the
commit fest or something else.
The approach looks right, but I didn't scrutinize the code.
It is always good to get better estimates!
It's nice if you quote relevant parts of the thread so that people
don't have to search the archives to know what you are talking
about (the subject no longer matches what your patch does).
Yes, you should add it to the commitfest - but you'd have to wait
for the next commitfest to be created. There is an agreement that
you don't add new patches to the final commitfest of a release.
So yours would be considered for v20.
Yours,
Laurenz Albe