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 <[email protected]>: > 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/ >
