Re: COALESCE patch - Mailing list pgsql-hackers

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>:
>
> 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