Re: missing estimation for coalesce function - Mailing list pgsql-hackers

From Pavel Stehule
Subject Re: missing estimation for coalesce function
Date
Msg-id CAFj8pRCNZ41XoZynzF4J1rGm3O2xpb_cyyehVsuFNAuST1gWBA@mail.gmail.com
Whole thread Raw
In response to Re: missing estimation for coalesce function  (Laurenz Albe <laurenz.albe@cybertec.at>)
List pgsql-hackers


čt 28. 11. 2019 v 15:51 odesílatel Laurenz Albe <laurenz.albe@cybertec.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

pgsql-hackers by date:

Previous
From: Tomas Vondra
Date:
Subject: Re: Memory-Bounded Hash Aggregation
Next
From: Mark Dilger
Date:
Subject: Do XID sequences need to be contiguous?