COALESCE patch

Hi everyone,

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.

A minimal example:

CREATE TABLE t (a int, b int);
INSERT INTO t
SELECT CASE WHEN i % 5 < 2 THEN NULL ELSE i END, i
FROM generate_series(1, 1000) i;
ANALYZE t;

EXPLAIN SELECT * FROM t WHERE COALESCE(a, b) = 42;

After ANALYZE, a is NULL in 400 of the 1000 rows (stanullfrac 0.4, ndistinct
600) and b has no NULLs (stanullfrac 0, ndistinct 1000). COALESCE(a, b) is
unique, so exactly one row matches. Each branch is weighted by the probability
of reaching it, the product of stanullfrac over the branches before it:

branch a: reach 1.0, sel(a = 42) = (1 - 0.4) / 600 = 0.001
branch b: reach 0.4, sel(b = 42) = (1 - 0.0) / 1000 = 0.001

selectivity = 1.0 * 0.001 + 0.4 * 0.001 = 0.0014 -> ~1 row out of 1000

The patch lands on ~1 row, matching reality. The 0.005 default (5 rows) is not
derived from the table at all: it is the 1/DEFAULT_NUM_DISTINCT constant the
planner falls back to with no statistics on the expression, so it stays 5 rows
regardless of the null fraction or the ndistinct of a and b.

Feedback is welcome.

Regards,
Egor Savelev,
Tantor Labs LLC,
https://tantorlabs.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