Convert coalesce to or/and

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)

Responses

Browse pgsql-hackers by date

  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