COALESCE patch
Hackorum builds and tests every patch posted to the lists, not only commitfest submissions. This is Hackorum's own CI rather than the PostgreSQL project's, and it is still under testing - please report anything that looks wrong.
You can run a PostgreSQL built from this patch straight from Docker, with no checkout and no build:
docker run --rm -p 5432:5432 ghcr.io/hackorum-dev/postgres-patch:t248684psql -h localhost -U postgresBuilt from patchset v14 (message #14), September 20, 2026 at 02:11 PM.
Every patchset is also pushed to a branch of our PostgreSQL fork, so you can check out the same tree CI built. Without a PostgreSQL checkout:
git clone --branch t248684_14 https://github.com/hackorum-dev/postgres.gitIn a checkout you already have, add the fork once:
git remote add hackorum https://github.com/hackorum-dev/postgres.gitthen, for this patchset and every later one:
git fetch hackorum t248684_14 && git checkout t248684_14Patchset v14 (message #14) is on t248684_14
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/
Attachments:
coalesce.patchtext/x-patch; charset=US-ASCII; name=coalesce.patchDownload+434-30
rebase and refactor format for cfbot
вт, 30 июн. 2026 г. в 14:26, prankware <esavelievcode@gmail.com>:
Show quoted text
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.001selectivity = 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/
Attachments:
v1-0001-Coalesce-eqsel-eqjoinsel.patchtext/x-patch; charset=US-ASCII; name=v1-0001-Coalesce-eqsel-eqjoinsel.patchDownload+434-31
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
Thanks for the review — the test cases were very helpful.
You're right that v1 didn't improve the coalesce(col, const) case. The
reason is that a comparison of two constants got the default 0.005
instead of its real result, and joins with a constant on both sides
were skipped entirely.
v2 (attached) fixes both, and these four examples now estimate close
to the actual row counts.
Regards,
Egor Savelev,
Tantor Labs LLC,
https://tantorlabs.com
пт, 10 июл. 2026 г. в 12:27, Laurenz Albe <laurenz.albe@cybertec.at>:
Show quoted text
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: 2576EXPLAIN (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: 10000I think that the patch would be much more useful if it could improve
such estimates.Yours,
Laurenz Albe
On Fri, 2026-07-10 at 15:54 +0300, prankware wrote:
Thanks for the review — the test cases were very helpful.
You're right that v1 didn't improve the coalesce(col, const) case. The
reason is that a comparison of two constants got the default 0.005
instead of its real result, and joins with a constant on both sides
were skipped entirely.
v2 (attached) fixes both, and these four examples now estimate close
to the actual row counts.
This version works fine.
It passes the regression tests. It adds none of its own, but I
can't think of a good way to have stable regression tests for
anything that depends on optimizer statistics.
My biggest criticism at this point is the readability of the
code. The function comments are alright, but try_coalesce_eq()
is tricky and could do with some comments that explain what is
going on and what the invariants are.
- Why is there a special treatment of a CoalesceExpr that
match_coalesce_join_side() rejected?
- Why is it fine to assign a "bool" to a floating point variable?
(An explicit type cast might be a good idea too.)
There are more places that could do with some illumination.
Also, why do you explicitly check for CoalesceExpr with less than
two arguments in match_coalesce_join_side()?
Yours,
Laurenz Albe
Thanks for the review.
v3 (attached) adds comments at the two spots you asked about, and
answers the rest below.
The special case for a rejected CoalesceExpr:
match_coalesce_join_side() returns false both when a side is not a
COALESCE and when it is a COALESCE we chose not to decompose. In the
second case we must not treat the whole COALESCE as a single branch,
so we stop and let the caller estimate the clause the usual way. I
added a comment that says this.
The bool assigned to a float: I reworked it so the boolean result of
the operator maps explicitly to a selectivity of 1.0 or 0.0, with a
comment.
The check for fewer than two arguments: you're right that it's
redundant — a single-argument COALESCE is handled correctly without
it, so I removed it.
Feedback is welcome.
Regards,
Egor Savelev,
Tantor Labs LLC,
https://tantorlabs.com
чт, 16 июл. 2026 г. в 17:13, Laurenz Albe <laurenz.albe@cybertec.at>:
Show quoted text
On Fri, 2026-07-10 at 15:54 +0300, prankware wrote:
Thanks for the review — the test cases were very helpful.
You're right that v1 didn't improve the coalesce(col, const) case. The
reason is that a comparison of two constants got the default 0.005
instead of its real result, and joins with a constant on both sides
were skipped entirely.
v2 (attached) fixes both, and these four examples now estimate close
to the actual row counts.This version works fine.
It passes the regression tests. It adds none of its own, but I
can't think of a good way to have stable regression tests for
anything that depends on optimizer statistics.My biggest criticism at this point is the readability of the
code. The function comments are alright, but try_coalesce_eq()
is tricky and could do with some comments that explain what is
going on and what the invariants are.- Why is there a special treatment of a CoalesceExpr that
match_coalesce_join_side() rejected?- Why is it fine to assign a "bool" to a floating point variable?
(An explicit type cast might be a good idea too.)There are more places that could do with some illumination.
Also, why do you explicitly check for CoalesceExpr with less than
two arguments in match_coalesce_join_side()?Yours,
Laurenz Albe
Hi Egor,
+ foreach(lc, c->args)
+ {
+ Node *arg = (Node *) lfirst(lc);
...
+ /* leading Const makes COALESCE itself constant */
+ if (arg == NULL || (lc == list_head(c->args) && IsA(arg, Const)))Wouldn't it make sense to check for leading const before starting the loop?
You have
+ if (left_prefix[li] < 1.0e-12)
and
+ if (right_prefix[ri] < 1.0e-12)
but the contribution of each term is
+ acc_selec += left_prefix[li] * right_prefix[ri] * contrib;
What about using if(left_prefix[li] * right_prefix[ri] < 1e-12)?
Isn't the relative contribution more relevant than the absolute
e.g. ignore terms that would add no more than 0.1% to the
selectivity one could use
if(left_prefix[li] * right_prefix[ri] < 1e-3 * acc_selec)
Regards,
Alexandre
On Fri, Aug 7, 2026 at 1:57 PM prankware <esavelievcode@gmail.com> wrote:
Show quoted text
Thanks for the review.
v3 (attached) adds comments at the two spots you asked about, and
answers the rest below.The special case for a rejected CoalesceExpr:
match_coalesce_join_side() returns false both when a side is not a
COALESCE and when it is a COALESCE we chose not to decompose. In the
second case we must not treat the whole COALESCE as a single branch,
so we stop and let the caller estimate the clause the usual way. I
added a comment that says this.The bool assigned to a float: I reworked it so the boolean result of
the operator maps explicitly to a selectivity of 1.0 or 0.0, with a
comment.The check for fewer than two arguments: you're right that it's
redundant — a single-argument COALESCE is handled correctly without
it, so I removed it.Feedback is welcome.
Regards,
Egor Savelev,
Tantor Labs LLC,
https://tantorlabs.comчт, 16 июл. 2026 г. в 17:13, Laurenz Albe <laurenz.albe@cybertec.at>:
On Fri, 2026-07-10 at 15:54 +0300, prankware wrote:
Thanks for the review — the test cases were very helpful.
You're right that v1 didn't improve the coalesce(col, const) case. The
reason is that a comparison of two constants got the default 0.005
instead of its real result, and joins with a constant on both sides
were skipped entirely.
v2 (attached) fixes both, and these four examples now estimate close
to the actual row counts.This version works fine.
It passes the regression tests. It adds none of its own, but I
can't think of a good way to have stable regression tests for
anything that depends on optimizer statistics.My biggest criticism at this point is the readability of the
code. The function comments are alright, but try_coalesce_eq()
is tricky and could do with some comments that explain what is
going on and what the invariants are.- Why is there a special treatment of a CoalesceExpr that
match_coalesce_join_side() rejected?- Why is it fine to assign a "bool" to a floating point variable?
(An explicit type cast might be a good idea too.)There are more places that could do with some illumination.
Also, why do you explicitly check for CoalesceExpr with less than
two arguments in match_coalesce_join_side()?Yours,
Laurenz Albe
Thanks for the review.
Leading Const: good point, I moved that check before the loop, so it runs
once instead of on every iteration.
Early-stop threshold: you're right that the weight of a term is the product
of both probabilities, so I changed the inner check to left_prefix[li] *
right_prefix[ri] < 1e-12.
Relative threshold: I decided against it. In practice a COALESCE has two or
three arguments, so the double loop is tiny and the break almost never
fires. The absolute guard is only there to skip the useless work when a
column is almost entirely NULL. A relative cutoff would drop terms and
change the estimate for very little gain, so I kept the sum exact.
v4 is attached. It passes the regression tests and gives the same estimates
as before.
Feedback is welcome.
Regards,
Egor Savelev,
Tantor Labs LLC,
https://tantorlabs.com
пт, 7 авг. 2026 г. в 22:23, Alexandre Felipe <o.alexandre.felipe@gmail.com>:
Show quoted text
Hi Egor,
+ foreach(lc, c->args) + { + Node *arg = (Node *) lfirst(lc); ... + /* leading Const makes COALESCE itself constant */ + if (arg == NULL || (lc == list_head(c->args) && IsA(arg, Const)))Wouldn't it make sense to check for leading const before starting the loop?
You have
+ if (left_prefix[li] < 1.0e-12)
and
+ if (right_prefix[ri] < 1.0e-12)but the contribution of each term is
+ acc_selec += left_prefix[li] * right_prefix[ri] * contrib;What about using if(left_prefix[li] * right_prefix[ri] < 1e-12)?
Isn't the relative contribution more relevant than the absolute
e.g. ignore terms that would add no more than 0.1% to the
selectivity one could use
if(left_prefix[li] * right_prefix[ri] < 1e-3 * acc_selec)Regards,
AlexandreOn Fri, Aug 7, 2026 at 1:57 PM prankware <esavelievcode@gmail.com> wrote:
Thanks for the review.
v3 (attached) adds comments at the two spots you asked about, and
answers the rest below.The special case for a rejected CoalesceExpr:
match_coalesce_join_side() returns false both when a side is not a
COALESCE and when it is a COALESCE we chose not to decompose. In the
second case we must not treat the whole COALESCE as a single branch,
so we stop and let the caller estimate the clause the usual way. I
added a comment that says this.The bool assigned to a float: I reworked it so the boolean result of
the operator maps explicitly to a selectivity of 1.0 or 0.0, with a
comment.The check for fewer than two arguments: you're right that it's
redundant — a single-argument COALESCE is handled correctly without
it, so I removed it.Feedback is welcome.
Regards,
Egor Savelev,
Tantor Labs LLC,
https://tantorlabs.comчт, 16 июл. 2026 г. в 17:13, Laurenz Albe <laurenz.albe@cybertec.at>:
On Fri, 2026-07-10 at 15:54 +0300, prankware wrote:
Thanks for the review — the test cases were very helpful.
You're right that v1 didn't improve the coalesce(col, const) case. The
reason is that a comparison of two constants got the default 0.005
instead of its real result, and joins with a constant on both sides
were skipped entirely.
v2 (attached) fixes both, and these four examples now estimate close
to the actual row counts.This version works fine.
It passes the regression tests. It adds none of its own, but I
can't think of a good way to have stable regression tests for
anything that depends on optimizer statistics.My biggest criticism at this point is the readability of the
code. The function comments are alright, but try_coalesce_eq()
is tricky and could do with some comments that explain what is
going on and what the invariants are.- Why is there a special treatment of a CoalesceExpr that
match_coalesce_join_side() rejected?- Why is it fine to assign a "bool" to a floating point variable?
(An explicit type cast might be a good idea too.)There are more places that could do with some illumination.
Also, why do you explicitly check for CoalesceExpr with less than
two arguments in match_coalesce_join_side()?Yours,
Laurenz Albe
Hi everyone,
Thank you for working on it.
On 8/17/26 13:37, prankware wrote:
v4 is attached. It passes the regression tests and gives the same
estimates as before.
Testing v4 with a `COALESCE(...) <> const` clause, I found a case where
the estimate should be exactly recoverable but is not.
```
CREATE TABLE t (a INT);
INSERT INTO t SELECT (i % 10) FROM generate_series(1, 100000) i;
ANALYZE t;
EXPLAIN ANALYZE SELECT * FROM t1 WHERE a <> 5;
QUERY PLAN
----------------------------------------------------------------------------------------------------------
Seq Scan on t1 (cost=0.00..1693.00 rows=90057 width=4) (actual
time=0.382..9.287 rows=90000.00 loops=1)
Filter: (a <> 5)
Rows Removed by Filter: 10000
Buffers: shared read=443
Planning:
Buffers: shared hit=5
Planning Time: 0.150 ms
Execution Time: 11.502 ms
(8 rows)
EXPLAIN ANALYZE SELECT * FROM t1 WHERE COALESCE(a, 1) <> 5;
QUERY PLAN
----------------------------------------------------------------------------------------------------------
Seq Scan on t1 (cost=0.00..1693.00 rows=10260 width=4) (actual
time=0.049..6.034 rows=90000.00 loops=1)
Filter: (COALESCE(a, 1) <> 5)
Rows Removed by Filter: 10000
Buffers: shared hit=443
Planning Time: 0.145 ms
Execution Time: 7.977 ms
(6 rows)
```
Since a is never NULL here, COALESCE(a, 1) is identical to a on every
row, so the two queries' estimates should be the same - but they're off
by almost 9x. Moreover, I looked at MCV statistics
```
SELECT null_frac, most_common_vals, most_common_freqs FROM pg_stats
WHERE tablename = 't1';
null_frac | most_common_vals | most_common_freqs
-----------+-----------------------+---------------------------------------------------------------------------------------------------------
0 | {4,8,6,9,3,1,*5*,0,2,7} |
{0.10226667,0.10126667,0.10123333,0.1011,0.10073333,0.09946667,*0.09943333*,0.09903333,0.09826667,0.0972}
(1 row)
```
All 10 distinct values of a are captured as MCVs, so a <> 5 is
essentially exactly computable from these stats.
My guess is that the <> case ends up going through the COALESCE
decomposition without ever actually being turned into its `negator`
operator along the way.
--
Best regards,
Ilia Evdokimov,
Tantor Labs LLC,
https://tantorlabs.com/
Thanks for catching this, and your guess was exactly right.
The <> case went into the COALESCE decomposition while the operator was
still <>, so it computed an equality selectivity with the wrong operator
and never negated the result.
v5 (attached) switches to the = operator before the decomposition and
negates at the end, returning 1 - eq - nullfrac (the clause is NULL
whenever either side is). Now COALESCE (a, 1) <> 5 estimates the same as
the plain a <> 5.
Feedback is welcome.
Regards,
Egor Savelev,
Tantor Labs LLC,
https://tantorlabs.com
пн, 17 авг. 2026 г. в 18:55, Ilia Evdokimov <ilya.evdokimov@tantorlabs.com>:
Show quoted text
Hi everyone,
Thank you for working on it.
On 8/17/26 13:37, prankware wrote:v4 is attached. It passes the regression tests and gives the same
estimates as before.Testing v4 with a `COALESCE(...) <> const` clause, I found a case where
the estimate should be exactly recoverable but is not.```
CREATE TABLE t (a INT);
INSERT INTO t SELECT (i % 10) FROM generate_series(1, 100000) i;
ANALYZE t;
EXPLAIN ANALYZE SELECT * FROM t1 WHERE a <> 5;
QUERY PLAN----------------------------------------------------------------------------------------------------------
Seq Scan on t1 (cost=0.00..1693.00 rows=90057 width=4) (actual
time=0.382..9.287 rows=90000.00 loops=1)
Filter: (a <> 5)
Rows Removed by Filter: 10000
Buffers: shared read=443
Planning:
Buffers: shared hit=5
Planning Time: 0.150 ms
Execution Time: 11.502 ms
(8 rows)EXPLAIN ANALYZE SELECT * FROM t1 WHERE COALESCE(a, 1) <> 5;
QUERY PLAN----------------------------------------------------------------------------------------------------------
Seq Scan on t1 (cost=0.00..1693.00 rows=10260 width=4) (actual
time=0.049..6.034 rows=90000.00 loops=1)
Filter: (COALESCE(a, 1) <> 5)
Rows Removed by Filter: 10000
Buffers: shared hit=443
Planning Time: 0.145 ms
Execution Time: 7.977 ms
(6 rows)
```Since a is never NULL here, COALESCE(a, 1) is identical to a on every row,
so the two queries' estimates should be the same - but they're off by
almost 9x. Moreover, I looked at MCV statistics```
SELECT null_frac, most_common_vals, most_common_freqs FROM pg_stats WHERE
tablename = 't1';
null_frac | most_common_vals |
most_common_freqs-----------+-----------------------+---------------------------------------------------------------------------------------------------------
0 | {4,8,6,9,3,1,*5*,0,2,7} |
{0.10226667,0.10126667,0.10123333,0.1011,0.10073333,0.09946667,
*0.09943333*,0.09903333,0.09826667,0.0972}
(1 row)
```All 10 distinct values of a are captured as MCVs, so a <> 5 is essentially
exactly computable from these stats.My guess is that the <> case ends up going through the COALESCE
decomposition without ever actually being turned into its `negator`
operator along the way.--
Best regards,
Ilia Evdokimov,
Tantor Labs LLC,
https://tantorlabs.com/
Attachments:
v5-0001-Coalesce-eqsel-eqjoinsel.patchtext/x-patch; charset=US-ASCII; name=v5-0001-Coalesce-eqsel-eqjoinsel.patchDownload+497-31
While reviewing try_coalesce_eq() I noticed this
+ bool is_eqjoin = (!fcinfo->flinfo != NULL && fcinfo->flinfo->fn_oid ==
F_EQJOINSEL)
This is checking against one specific selectivity function, but
eqjoinsel() is not the only join-selectivity estimation - it's just the
most common one (used by = operators). pg_proc.dat alone registers over
a dozen others as JOIN estimators. We need a different mechanism. The
only way I see is to stop interfering the context and pass it in
explicitly give try_coalesce_eq() a bool is_eqjoin parameter.
For example, consider this scenario:
```
CREATE TABLE a (x1 int, x2 int, y int);
CREATE TABLE b (w int);
INSERT INTO a (x1, x2, y)
SELECT
CASE WHEN i % 3 = 0 THEN NULL ELSE i % 1000 END,
CASE WHEN i % 3 = 0 THEN i % 500 ELSE NULL END,
i % 200
FROM generate_series(1, 100000) i;
INSERT INTO b (w)
SELECT i % 1000
FROM generate_series(1, 100000) i;
CREATE INDEX a_coalesce_x1x2_idx ON a (COALESCE(x1, x2));
ANALYZE a, b;
EXPLAIN ANALYZE
SELECT * FROM a JOIN b ON COALESCE(COALESCE(a.x1, a.x2), a.y) = b.w;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Hash Join (cost=2693.00..688019.33 rows=66488333 width=16) (actual
time=11.824..343.641 rows=10000000.00 loops=1)
Hash Cond: (COALESCE(COALESCE(a.x1, a.x2), a.y) = b.w)
Buffers: shared read=886
-> Seq Scan on a (cost=0.00..1443.00 rows=100000 width=12) (actual
time=0.355..2.294 rows=100000.00 loops=1)
Buffers: shared read=443
-> Hash (cost=1443.00..1443.00 rows=100000 width=4) (actual
time=10.691..10.693 rows=100000.00 loops=1)
Buckets: 131072 Batches: 1 Memory Usage: 4540kB
Buffers: shared read=443
-> Seq Scan on b (cost=0.00..1443.00 rows=100000 width=4)
(actual time=0.243..3.641 rows=100000.00 loops=1)
Buffers: shared read=443
Planning:
Buffers: shared hit=139 read=33
Planning Time: 1.712 ms
Execution Time: 431.061 ms
(14 rows)
```
Estimated rows are 6 times bigger than actual ones.
--
Best regards,
Ilia Evdokimov,
Tantor Labs LLC,
https://tantorlabs.com/
Hi Ilia,
Thanks for pointing this out. I understand the issue with relying on
F_EQJOINSEL to determine the equality-join context, especially since
PostgreSQL has multiple join selectivity estimators.
Is this issue still open? If so, I’d be happy to take a look at
implementing the explicit is_eqjoin propagation you suggested and add the
necessary regression tests.
With Regards,
Osama Abdul Qader
On Mon, 7 Sept, 2026, 4:34 pm Ilia Evdokimov, <ilya.evdokimov@tantorlabs.com>
wrote:
Show quoted text
While reviewing try_coalesce_eq() I noticed this
+ bool is_eqjoin = (!fcinfo->flinfo != NULL && fcinfo->flinfo->fn_oid ==
F_EQJOINSEL)This is checking against one specific selectivity function, but
eqjoinsel() is not the only join-selectivity estimation - it's just the
most common one (used by = operators). pg_proc.dat alone registers over
a dozen others as JOIN estimators. We need a different mechanism. The
only way I see is to stop interfering the context and pass it in
explicitly give try_coalesce_eq() a bool is_eqjoin parameter.For example, consider this scenario:
```
CREATE TABLE a (x1 int, x2 int, y int);
CREATE TABLE b (w int);INSERT INTO a (x1, x2, y)
SELECT
CASE WHEN i % 3 = 0 THEN NULL ELSE i % 1000 END,
CASE WHEN i % 3 = 0 THEN i % 500 ELSE NULL END,
i % 200
FROM generate_series(1, 100000) i;INSERT INTO b (w)
SELECT i % 1000
FROM generate_series(1, 100000) i;CREATE INDEX a_coalesce_x1x2_idx ON a (COALESCE(x1, x2));
ANALYZE a, b;
EXPLAIN ANALYZE
SELECT * FROM a JOIN b ON COALESCE(COALESCE(a.x1, a.x2), a.y) = b.w;
QUERY PLAN-----------------------------------------------------------------------------------------------------------------------
Hash Join (cost=2693.00..688019.33 rows=66488333 width=16) (actual
time=11.824..343.641 rows=10000000.00 loops=1)
Hash Cond: (COALESCE(COALESCE(a.x1, a.x2), a.y) = b.w)
Buffers: shared read=886
-> Seq Scan on a (cost=0.00..1443.00 rows=100000 width=12) (actual
time=0.355..2.294 rows=100000.00 loops=1)
Buffers: shared read=443
-> Hash (cost=1443.00..1443.00 rows=100000 width=4) (actual
time=10.691..10.693 rows=100000.00 loops=1)
Buckets: 131072 Batches: 1 Memory Usage: 4540kB
Buffers: shared read=443
-> Seq Scan on b (cost=0.00..1443.00 rows=100000 width=4)
(actual time=0.243..3.641 rows=100000.00 loops=1)
Buffers: shared read=443
Planning:
Buffers: shared hit=139 read=33
Planning Time: 1.712 ms
Execution Time: 431.061 ms
(14 rows)
```Estimated rows are 6 times bigger than actual ones.
--
Best regards,
Ilia Evdokimov,
Tantor Labs LLC,
https://tantorlabs.com/
Thanks for the review, and for the clear repro — you're right on both the
diagnosis and the fix.
The context was inferred from fcinfo (fn_oid == F_EQJOINSEL), and that
breaks on recursion: try_coalesce_eq() calls eqjoinsel() through
DirectFunctionCall, which leaves fcinfo->flinfo NULL. So when a branch is
itself a COALESCE, the nested eqjoinsel() saw is_eqjoin = false and
estimated the join as a restriction, which is where the ~6x came from.
v6 (attached) passes is_eqjoin explicitly, as you suggested:
eqsel_internal() passes false and eqjoinsel() passes true, and the fcinfo
check is gone. On your example the estimate drops from ~66M to ~7.8M
(actual 10M), and the earlier cases are unchanged.
Feedback is welcome.
Osama Abdul Qader, thanks for the offer, but it's already handled — v6
(just posted to the thread) passes is_eqjoin explicitly and removes the
F_EQJOINSEL check, so no extra work is needed here.
Regards, Egor Savelev, Tantor Labs LLC, https://tantorlabs.com
пн, 7 сент. 2026 г. в 14:50, Osama Abdul Qader <osamaabdulqader.cs@gmail.com
Show quoted text
:
Hi Ilia,
Thanks for pointing this out. I understand the issue with relying on
F_EQJOINSEL to determine the equality-join context, especially since
PostgreSQL has multiple join selectivity estimators.Is this issue still open? If so, I’d be happy to take a look at
implementing the explicit is_eqjoin propagation you suggested and add the
necessary regression tests.With Regards,
Osama Abdul Qader
On Mon, 7 Sept, 2026, 4:34 pm Ilia Evdokimov, <
ilya.evdokimov@tantorlabs.com> wrote:While reviewing try_coalesce_eq() I noticed this
+ bool is_eqjoin = (!fcinfo->flinfo != NULL && fcinfo->flinfo->fn_oid ==
F_EQJOINSEL)This is checking against one specific selectivity function, but
eqjoinsel() is not the only join-selectivity estimation - it's just the
most common one (used by = operators). pg_proc.dat alone registers over
a dozen others as JOIN estimators. We need a different mechanism. The
only way I see is to stop interfering the context and pass it in
explicitly give try_coalesce_eq() a bool is_eqjoin parameter.For example, consider this scenario:
```
CREATE TABLE a (x1 int, x2 int, y int);
CREATE TABLE b (w int);INSERT INTO a (x1, x2, y)
SELECT
CASE WHEN i % 3 = 0 THEN NULL ELSE i % 1000 END,
CASE WHEN i % 3 = 0 THEN i % 500 ELSE NULL END,
i % 200
FROM generate_series(1, 100000) i;INSERT INTO b (w)
SELECT i % 1000
FROM generate_series(1, 100000) i;CREATE INDEX a_coalesce_x1x2_idx ON a (COALESCE(x1, x2));
ANALYZE a, b;
EXPLAIN ANALYZE
SELECT * FROM a JOIN b ON COALESCE(COALESCE(a.x1, a.x2), a.y) = b.w;
QUERY PLAN-----------------------------------------------------------------------------------------------------------------------
Hash Join (cost=2693.00..688019.33 rows=66488333 width=16) (actual
time=11.824..343.641 rows=10000000.00 loops=1)
Hash Cond: (COALESCE(COALESCE(a.x1, a.x2), a.y) = b.w)
Buffers: shared read=886
-> Seq Scan on a (cost=0.00..1443.00 rows=100000 width=12) (actual
time=0.355..2.294 rows=100000.00 loops=1)
Buffers: shared read=443
-> Hash (cost=1443.00..1443.00 rows=100000 width=4) (actual
time=10.691..10.693 rows=100000.00 loops=1)
Buckets: 131072 Batches: 1 Memory Usage: 4540kB
Buffers: shared read=443
-> Seq Scan on b (cost=0.00..1443.00 rows=100000 width=4)
(actual time=0.243..3.641 rows=100000.00 loops=1)
Buffers: shared read=443
Planning:
Buffers: shared hit=139 read=33
Planning Time: 1.712 ms
Execution Time: 431.061 ms
(14 rows)
```Estimated rows are 6 times bigger than actual ones.
--
Best regards,
Ilia Evdokimov,
Tantor Labs LLC,
https://tantorlabs.com/
Attachments:
t248684_14v6-0001-Coalesce-eqsel-eqjoinsel.patchtext/x-patch; charset=US-ASCII; name=v6-0001-Coalesce-eqsel-eqjoinsel.patchDownload+495-31
Hello all,
Thanks for letting me know that it was handled already.
May the patches we have fixes the problem.
With Regards,
Osama Abdul Qader
On Mon, 7 Sept, 2026, 5:53 pm prankware, <esavelievcode@gmail.com> wrote:
Show quoted text
Thanks for the review, and for the clear repro — you're right on both the
diagnosis and the fix.
The context was inferred from fcinfo (fn_oid == F_EQJOINSEL), and that
breaks on recursion: try_coalesce_eq() calls eqjoinsel() through
DirectFunctionCall, which leaves fcinfo->flinfo NULL. So when a branch is
itself a COALESCE, the nested eqjoinsel() saw is_eqjoin = false and
estimated the join as a restriction, which is where the ~6x came from.
v6 (attached) passes is_eqjoin explicitly, as you suggested:
eqsel_internal() passes false and eqjoinsel() passes true, and the fcinfo
check is gone. On your example the estimate drops from ~66M to ~7.8M
(actual 10M), and the earlier cases are unchanged.
Feedback is welcome.Osama Abdul Qader, thanks for the offer, but it's already handled — v6
(just posted to the thread) passes is_eqjoin explicitly and removes the
F_EQJOINSEL check, so no extra work is needed here.Regards, Egor Savelev, Tantor Labs LLC, https://tantorlabs.com
пн, 7 сент. 2026 г. в 14:50, Osama Abdul Qader <
osamaabdulqader.cs@gmail.com>:Hi Ilia,
Thanks for pointing this out. I understand the issue with relying on
F_EQJOINSEL to determine the equality-join context, especially since
PostgreSQL has multiple join selectivity estimators.Is this issue still open? If so, I’d be happy to take a look at
implementing the explicit is_eqjoin propagation you suggested and add
the necessary regression tests.With Regards,
Osama Abdul Qader
On Mon, 7 Sept, 2026, 4:34 pm Ilia Evdokimov, <
ilya.evdokimov@tantorlabs.com> wrote:While reviewing try_coalesce_eq() I noticed this
+ bool is_eqjoin = (!fcinfo->flinfo != NULL && fcinfo->flinfo->fn_oid ==
F_EQJOINSEL)This is checking against one specific selectivity function, but
eqjoinsel() is not the only join-selectivity estimation - it's just the
most common one (used by = operators). pg_proc.dat alone registers over
a dozen others as JOIN estimators. We need a different mechanism. The
only way I see is to stop interfering the context and pass it in
explicitly give try_coalesce_eq() a bool is_eqjoin parameter.For example, consider this scenario:
```
CREATE TABLE a (x1 int, x2 int, y int);
CREATE TABLE b (w int);INSERT INTO a (x1, x2, y)
SELECT
CASE WHEN i % 3 = 0 THEN NULL ELSE i % 1000 END,
CASE WHEN i % 3 = 0 THEN i % 500 ELSE NULL END,
i % 200
FROM generate_series(1, 100000) i;INSERT INTO b (w)
SELECT i % 1000
FROM generate_series(1, 100000) i;CREATE INDEX a_coalesce_x1x2_idx ON a (COALESCE(x1, x2));
ANALYZE a, b;
EXPLAIN ANALYZE
SELECT * FROM a JOIN b ON COALESCE(COALESCE(a.x1, a.x2), a.y) = b.w;
QUERY PLAN-----------------------------------------------------------------------------------------------------------------------
Hash Join (cost=2693.00..688019.33 rows=66488333 width=16) (actual
time=11.824..343.641 rows=10000000.00 loops=1)
Hash Cond: (COALESCE(COALESCE(a.x1, a.x2), a.y) = b.w)
Buffers: shared read=886
-> Seq Scan on a (cost=0.00..1443.00 rows=100000 width=12) (actual
time=0.355..2.294 rows=100000.00 loops=1)
Buffers: shared read=443
-> Hash (cost=1443.00..1443.00 rows=100000 width=4) (actual
time=10.691..10.693 rows=100000.00 loops=1)
Buckets: 131072 Batches: 1 Memory Usage: 4540kB
Buffers: shared read=443
-> Seq Scan on b (cost=0.00..1443.00 rows=100000 width=4)
(actual time=0.243..3.641 rows=100000.00 loops=1)
Buffers: shared read=443
Planning:
Buffers: shared hit=139 read=33
Planning Time: 1.712 ms
Execution Time: 431.061 ms
(14 rows)
```Estimated rows are 6 times bigger than actual ones.
--
Best regards,
Ilia Evdokimov,
Tantor Labs LLC,
https://tantorlabs.com/