public inbox for [email protected]
help / color / mirror / Atom feedFrom: Laurenz Albe <[email protected]>
To: prankware <[email protected]>
To: [email protected]
Subject: Re: COALESCE patch
Date: Fri, 10 Jul 2026 11:26:58 +0200
Message-ID: <[email protected]> (raw)
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>
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) = $1, or a join on
> COALESCE(t1.a, t1.b) = 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 only way
> around this today is an expression index or extended statistics on that exact
> 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 expressions
> are common in joins and filters over nullable or fallback columns, the default
> 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 when
> arg_1 .. arg_{i-1} are all NULL, so the chance of reaching branch i is the
> product of stanullfrac over the earlier branches. Selectivity of
> COALESCE(l_1..l_M) = COALESCE(r_1..r_N) is then the sum over branch pairs of
> P(reach l_i) * P(reach r_j) * sel(l_i = r_j), and each sel(l_i = r_j) is a
> recursive call back into eqsel()/eqjoinsel(). A non-COALESCE side is treated as
> a one-branch list, so scalar COALESCE(a, b) = const falls out of the same code,
> and the same decomposition feeds estimate_hash_bucket_stats(). If any branch 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 extremely
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() >= 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 = '512MB';
SET enable_mergejoin = off;
SET enable_nestloop = off;
EXPLAIN (ANALYZE, SUMMARY OFF, BUFFERS OFF)
SELECT *
FROM b AS b1
JOIN b AS b2 ON coalesce(b1.col1, 0) = coalesce(b2.col1, 0);
Hash Join (... rows=500000 ...) (actual ... rows=55125006.00 ...)
Hash Cond: (COALESCE(b1.col1, 0) = COALESCE(b2.col1, 0))
-> Seq Scan on b b1 (... rows=10000 ...) (actual ... rows=10000.00 ..)
-> Hash (... rows=10000 ...) (actual ... rows=10000.00 ...)
Buckets: 16384 Batches: 1 Memory Usage: 451kB
-> Seq Scan on b b2 (... rows=10000 ...) (actual ... rows=10000.00 ...)
EXPLAIN (ANALYZE, SUMMARY OFF, BUFFERS OFF)
SELECT *
FROM b AS b1
JOIN b AS b2 ON coalesce(b1.col1, 0) = coalesce(b2.col1, 1);
Hash Join (... rows=500000 ...) (actual ... rows=16654.00 ...)
Hash Cond: (COALESCE(b1.col1, 0) = COALESCE(b2.col1, 1))
-> Seq Scan on b b1 (... rows=10000 ...) (actual ... rows=10000.00 ...)
-> Hash (... rows=10000 ...) (actual ... rows=10000.00 ...)
Buckets: 16384 Batches: 1 Memory Usage: 451kB
-> Seq Scan on b b2 (... rows=10000 ...) (actual ... rows=10000.00 ...)
EXPLAIN (ANALYZE, SUMMARY OFF, BUFFERS OFF)
SELECT * FROM b WHERE coalesce(col1, 0) = 0;
Seq Scan on b (... rows=40 ...) (actual ... rows=7424.00 ...)
Filter: (COALESCE(col1, 0) = 0)
Rows Removed by Filter: 2576
EXPLAIN (ANALYZE, SUMMARY OFF, BUFFERS OFF)
SELECT * FROM b WHERE coalesce(col1, 1) = 0;
Seq Scan on b (... rows=40 ...) (actual ... rows=0.00 ...)
Filter: (COALESCE(col1, 1) = 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
view thread (42+ messages)
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: COALESCE patch
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox