| From: | Nicolas Adenis-Lamarre <nicolas(dot)adenis(dot)lamarre(at)gmail(dot)com> |
|---|---|
| To: | pgsql-hackers(at)lists(dot)postgresql(dot)org |
| Subject: | Convert coalesce to or/and |
| Date: | 2026-01-01 22:36:00 |
| Message-ID: | CACPGbcsUBGvtLFp+G6e0xb6G5d_4UZJz-BwnJe8EsPcd713NJA@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
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)
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Tom Lane | 2026-01-01 22:51:06 | Re: Convert coalesce to or/and |
| Previous Message | Nicolas Adenis-Lamarre | 2026-01-01 22:28:01 | Re: Planner : anti-join on left joins |