On Wed, 2019-11-27 at 08:47 +0100, Pavel Stehule wrote: > The most significant issue was missing correct estimation for coalesce function. > He had to rewrite coalesce(var, X) = X to "var IS NULL or var = X". > Then the result was very satisfactory. > > postgres=# explain analyze select * from xxx where coalesce(a, 0) = 0; > QUERY PLAN > ---------------------------------------------------------------------------------------------------- > Seq Scan on xxx (cost=0.00..194.00 rows=60 width=4) (actual time=0.041..4.276 rows=11000 loops=1)
Probably it needs more work - currently this support is for SRF function or for boolean functions.
On second hand coalesce is not function - it's expr node. Originally I though so selectivity function can be enough. Now I think so it is not enough. It is similar to DISTINCT FROM operator.
So some plan can look like
1. introduction isnull_or_eq operator
2. this operator can be used for indexscan too
3. implement selectivity function for this operator (and maybe for coalesce)
4. translate COALESCE(var, const) = const --> var isnull_or_eq const
I am not sure if @4 is possible or if some more complex transformations are possible COALESCE(var1, var2) = var2
But what I read about it - MSSQL and Oracle has does this optimization