Re: COALESCE patch

On Mon, Oct 19, 2026 at 4:52 PM prankware <esavelievcode(at)gmail(dot)com> wrote:
>
> Hi,
> For SEMI/ANTI joins the selectivity is existence-based - whether an
> outer row has at least one match - so it cannot be written as the
> inner-join pair sum.

I found two cases where the current calculation gives incorrect estimates.

1. An outer MCV value that is absent from the inner MCV list may still
exist in the inner relation.
```
CREATE TABLE coalesce_outer AS
SELECT 7::int AS a
FROM generate_series(1, 10000);

CREATE TABLE coalesce_inner AS
SELECT g::int AS b
FROM generate_series(1, 10000) AS g;

ANALYZE coalesce_outer;
ANALYZE coalesce_inner;

EXPLAIN ANALYZE
SELECT *
FROM coalesce_outer o
WHERE EXISTS (
SELECT 1
FROM coalesce_inner i
WHERE coalesce(o.a, -1) = i.b
);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
Hash Semi Join (cost=289.00..479.25 rows=1 width=4) (actual
time=0.836..1.618 rows=10000.00 loops=1)
Hash Cond: (COALESCE(o.a, '-1'::integer) = i.b)
Buffers: shared hit=214 dirtied=2 written=8
-> Seq Scan on coalesce_outer o (cost=0.00..164.00 rows=10000
width=4) (actual time=0.157..0.414 rows=10000.00 loops=1)
Buffers: shared hit=107 dirtied=1 written=4
-> Hash (cost=164.00..164.00 rows=10000 width=4) (actual
time=0.656..0.656 rows=10000.00 loops=1)
Buckets: 16384 Batches: 1 Memory Usage: 480kB
Buffers: shared hit=107 dirtied=1 written=4
-> Seq Scan on coalesce_inner i (cost=0.00..164.00
rows=10000 width=4) (actual time=0.059..0.326 rows=10000.00 loops=1)
Buffers: shared hit=107 dirtied=1 written=4
Planning:
Buffers: shared hit=71 read=7
Planning Time: 0.206 ms
Execution Time: 1.731 ms
(14 rows)
```

2. The inner distinct count doesn't account for restrictions on the inner input.
```
EXPLAIN ANALYZE
SELECT *
FROM coalesce_inner o
WHERE EXISTS (
SELECT 1
FROM coalesce_inner i
WHERE i.b <= 10
AND coalesce(o.b, -1) = i.b
);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=440.17..440.35 rows=10000 width=4) (actual
time=3.466..3.477 rows=10.00 loops=1)
Hash Cond: (i.b = COALESCE(o.b, '-1'::integer))
Buffers: shared hit=90
-> Unique (cost=170.17..170.22 rows=10 width=4) (actual
time=0.815..0.820 rows=10.00 loops=1)
Buffers: shared hit=45
-> Sort (cost=170.17..170.19 rows=10 width=4) (actual
time=0.812..0.814 rows=10.00 loops=1)
Sort Key: i.b
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=45
-> Seq Scan on coalesce_inner i (cost=0.00..170.00
rows=10 width=4) (actual time=0.009..0.803 rows=10.00 loops=1)
Filter: (b <= 10)
Rows Removed by Filter: 9990
Buffers: shared hit=45
-> Hash (cost=145.00..145.00 rows=10000 width=4) (actual
time=2.614..2.614 rows=10000.00 loops=1)
Buckets: 16384 Batches: 1 Memory Usage: 480kB
Buffers: shared hit=45
-> Seq Scan on coalesce_inner o (cost=0.00..145.00
rows=10000 width=4) (actual time=0.019..0.804 rows=10000.00 loops=1)
Buffers: shared hit=45
Planning:
Buffers: shared hit=13
Planning Time: 0.490 ms
Execution Time: 3.528 ms
(22 rows)

```

--
Best regards,
Ilia Evdokimov,
Tantor Labs LLC,
https://tantorlabs.com/