Re: COALESCE patch

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 <ilya(dot)evdokimov(at)tantorlabs(dot)com>:

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