Re: COALESCE patch

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