Message-ID: <3f423bd9038afd4d38d8b916672055ad6c46ed2c.camel@cybertec.at>
Subject: Re: COALESCE patch
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: prankware <esavelievcode@gmail.com>, pgsql-hackers@lists.postgresql.org
Date: Fri, 10 Jul 2026 11:26:58 +0200
In-Reply-To: 
 <CAF=hKRCX9sf5Occjdft51sWwztW_MyRN-bnVvs05yeCbBLMPNQ@mail.gmail.com>
References: 
	<CAF=hKRBhFDzdSomCM5XGFzRBpEAmh-fQyt-6vb4Ji0pZs=--7A@mail.gmail.com>
	 <CAF=hKRCX9sf5Occjdft51sWwztW_MyRN-bnVvs05yeCbBLMPNQ@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.58.3 (3.58.3-1.fc43) 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/3f423bd9038afd4d38d8b916672055ad6c46ed2c.camel%40cybertec.at>
Precedence: bulk

On Tue, 2026-06-30 at 16:48 +0300, prankware wrote:

> The planner ignores column statistics when an equality has a COALESCE
> expression on one side. For a clause like COALESCE(a, b) =3D $1, or a joi=
n on
> COALESCE(t1.a, t1.b) =3D COALESCE(t2.c, t2.d), there are no statistics on=
 the
> COALESCE node itself, so eqsel() and eqjoinsel() return the generic 0.005
> estimate while the per-column stats for a, b, c and d sit unused. The onl=
y way
> around this today is an expression index or extended statistics on that e=
xact
> expression, which doesn't scale across many different COALESCE clauses.
> estimate_hash_bucket_stats() has the same gap: a COALESCE hash key gets a
> default ndistinct and therefore a default bucket size. Since these expres=
sions
> are common in joins and filters over nullable or fallback columns, the de=
fault
> estimate can be far enough off to flip the join order or join method.
>
> The idea is to estimate straight from the existing per-column stats, with=
 no
> extra statistics object. COALESCE(arg_1, ..., arg_n) returns arg_i only w=
hen
> arg_1 .. arg_{i-1} are all NULL, so the chance of reaching branch i is th=
e
> product of stanullfrac over the earlier branches. Selectivity of
> COALESCE(l_1..l_M) =3D COALESCE(r_1..r_N) is then the sum over branch pai=
rs of
> P(reach l_i) * P(reach r_j) * sel(l_i =3D r_j), and each sel(l_i =3D r_j)=
 is a
> recursive call back into eqsel()/eqjoinsel(). A non-COALESCE side is trea=
ted as
> a one-branch list, so scalar COALESCE(a, b) =3D const falls out of the sa=
me code,
> and the same decomposition feeds estimate_hash_bucket_stats(). If any bra=
nch is
> missing stats, the code bails and today's behavior is unchanged.
>
> Feedback is welcome.

I think the idea is good, and the performance cost is incurred only when
coalesce() expressions are present.  I am a bit worried about the execution
time for queries that join two tables over lengthy coalesce clauses, as the
cost is O(n*m) because of the sum.  But I think that such queries are extre=
mely
rare, so I don't worry too much.

I found that the estimates are good if I use expressions like
"coalesce(col1, col2)" in my query, but the estimates are as bad as before
with the common case of "coalesce(col, constant)":

CREATE TABLE b (col1 integer);

/* three quarters NULL, the rest evenly distributed */
INSERT INTO b
   SELECT CASE WHEN random() >=3D 0.75 THEN random() * 1000 + 1 END
   FROM generate_series(1, 10000);

VACUUM (ANALYZE) b;

/* force a hash join regardless of the estimates */
SET work_mem =3D '512MB';
SET enable_mergejoin =3D off;
SET enable_nestloop =3D off;


EXPLAIN (ANALYZE, SUMMARY OFF, BUFFERS OFF)
SELECT *
FROM b AS b1
   JOIN b AS b2 ON coalesce(b1.col1, 0) =3D coalesce(b2.col1, 0);

 Hash Join  (... rows=3D500000 ...) (actual ... rows=3D55125006.00 ...)
   Hash Cond: (COALESCE(b1.col1, 0) =3D COALESCE(b2.col1, 0))
   ->  Seq Scan on b b1  (... rows=3D10000 ...) (actual ... rows=3D10000.00=
 ..)
   ->  Hash  (... rows=3D10000 ...) (actual ... rows=3D10000.00 ...)
         Buckets: 16384  Batches: 1  Memory Usage: 451kB
         ->  Seq Scan on b b2  (... rows=3D10000 ...) (actual ... rows=3D10=
000.00 ...)

EXPLAIN (ANALYZE, SUMMARY OFF, BUFFERS OFF)
SELECT *
FROM b AS b1
   JOIN b AS b2 ON coalesce(b1.col1, 0) =3D coalesce(b2.col1, 1);

 Hash Join  (... rows=3D500000 ...) (actual ... rows=3D16654.00 ...)
   Hash Cond: (COALESCE(b1.col1, 0) =3D COALESCE(b2.col1, 1))
   ->  Seq Scan on b b1  (... rows=3D10000 ...) (actual ... rows=3D10000.00=
 ...)
   ->  Hash  (... rows=3D10000 ...) (actual ... rows=3D10000.00 ...)
         Buckets: 16384  Batches: 1  Memory Usage: 451kB
         ->  Seq Scan on b b2  (... rows=3D10000 ...) (actual ... rows=3D10=
000.00 ...)


EXPLAIN (ANALYZE, SUMMARY OFF, BUFFERS OFF)
SELECT * FROM b WHERE coalesce(col1, 0) =3D 0;

 Seq Scan on b  (... rows=3D40 ...) (actual ... rows=3D7424.00 ...)
   Filter: (COALESCE(col1, 0) =3D 0)
   Rows Removed by Filter: 2576

EXPLAIN (ANALYZE, SUMMARY OFF, BUFFERS OFF)
SELECT * FROM b WHERE coalesce(col1, 1) =3D 0;

 Seq Scan on b  (... rows=3D40 ...) (actual ... rows=3D0.00 ...)
   Filter: (COALESCE(col1, 1) =3D 0)
   Rows Removed by Filter: 10000


I think that the patch would be much more useful if it could improve
such estimates.

Yours,
Laurenz Albe