Re: COALESCE patch

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/