Re: missing estimation for coalesce function

čt 28. 11. 2019 v 15:51 odesílatel Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
napsal:

> 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)
>
> I think that this is asking for a planner support function:
> https://www.postgresql.org/docs/current/xfunc-optimization.html

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

Regards

Pavel

>
> Yours,
> Laurenz Albe
>
>