Re: IS NOT DISTINCT FROM statement

On Sat, 9 Mar 2019 at 01:25, Artur Zając <azajac@ang.com.pl> wrote:
> I made some workaround. I made function:
>
> CREATE OR REPLACE FUNCTION smarteq(v1 int,v2 INT) RETURNS BOOL AS
> $BODY$
>         SELECT (CASE WHEN v2 IS NULL THEN (v1 IS NULL) ELSE v1=v2 END);
> $BODY$ LANGUAGE 'sql' IMMUTABLE PARALLEL SAFE;

> explain analyze select id from sometable where smarteq(id1,NULL);
>                                                                   QUERY PLAN
>
----------------------------------------------------------------------------------------------------------------------------------------------
>  Bitmap Heap Scan on sometable  (cost=19338.59..57045.02 rows=882343 width=4) (actual time=116.236..306.304
rows=881657loops=1) 
>    Recheck Cond: (id1 IS NULL)
>    Heap Blocks: exact=9581
>    ->  Bitmap Index Scan on sometable_index1  (cost=0.00..19118.00 rows=882343 width=0) (actual time=114.209..114.209
rows=892552loops=1) 
>          Index Cond: (id1 IS NULL)
>  Planning time: 0.135 ms
>  Execution time: 339.229 ms
>
> It looks like it works, but I must check if it will still works in plpgsql (I expect some problems if query is
prepared).

I think with either that you'll just be at the mercy of whether a
generic or custom plan is chosen.  If you get a custom plan then
likely your case statement will be inlined and constant folded away,
but for a generic plan, that can't happen since those constants are
not consts, they're parameters.   Most likely, if you've got an index
on the column you'll perhaps always get a custom plan as the generic
plan would result in a seqscan and it would have to evaluate your case
statement for each row.  By default, generic plans are only considered
on the 6th query execution and are only chosen if the generic cost is
cheaper than the average custom plan cost + fuzz cost for planning.
PG12 gives you a bit more control over that with the plan_cache_mode
GUC, but... that's the not out yet.

However, possibly the cost of planning each execution is cheaper than
doing the seq scan, so you might be better off with this.  There is a
risk that the planner does for some reason choose a generic plan and
ends up doing the seq scan, but for that to happen likely the table
would have to be small, in which case it wouldn't matter or the costs
would have to be off, which might cause you some pain.

The transformation mentioned earlier could only work if the arguments
of the IS NOT DISTINCT FROM were Vars or Consts. It couldn't work with
Params since the values are unknown to the planner.

--
 David Rowley                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services