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/
