From: Tom Lane <tgl@sss.pgh.pa.us>
To: David Rowley <david.rowley@2ndquadrant.com>
cc: =?UTF-8?B?QXJ0dXIgWmFqxIVj?= <azajac@ang.com.pl>,
        pgsql-performance@lists.postgresql.org
Subject: Re: IS NOT DISTINCT FROM statement
In-reply-to: 
 <CAKJS1f8JiBSf6JR5WANbt+vUR-XNu2z6b-mfi6w0gD-8k66b-w@mail.gmail.com>
References: <012a01d4d5a2$5cde0760$169a1620$@ang.com.pl>
 <CAKJS1f89WBGzXzEB0b9uVzSGCxMWCe3xnVcH4rJydONK4Afxsg@mail.gmail.com>
 <014101d4d5aa$084e8ff0$18ebafd0$@ang.com.pl>
 <CAKJS1f8JiBSf6JR5WANbt+vUR-XNu2z6b-mfi6w0gD-8k66b-w@mail.gmail.com>
Comments: In-reply-to David Rowley <david.rowley@2ndquadrant.com>
	message dated "Sat, 09 Mar 2019 02:12:34 +1300"
MIME-Version: 1.0
Content-Type: text/plain; charset="UTF-8"
Content-ID: <28200.1552056803.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Fri, 08 Mar 2019 09:53:23 -0500
Message-ID: <28201.1552056803@sss.pgh.pa.us>
Precedence: bulk

David Rowley <david.rowley@2ndquadrant.com> writes:
> On Sat, 9 Mar 2019 at 01:25, Artur Zaj=C4=85c <azajac@ang.com.pl> wrote:
>> 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=3Dv2 END);
>> $BODY$ LANGUAGE 'sql' IMMUTABLE PARALLEL SAFE;

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

Just looking at this example, I'm wondering if there'd be any value in
adding a rule to eval_const_expressions that converts IS DISTINCT FROM
with one constant-NULL argument into an IS NOT NULL test on the other
argument.  Doing anything with the general case would be hard, as you
mentioned, but this "workaround" suggests that the OP isn't actually
concerned with the general case.

[ experiments... ] Oh, look at this:

regression=3D# explain verbose select f1 is distinct from null from int4_t=
bl;
                          QUERY PLAN                           =

---------------------------------------------------------------
 Seq Scan on public.int4_tbl  (cost=3D0.00..1.05 rows=3D5 width=3D1)
   Output: (f1 IS NOT NULL)
(2 rows)

regression=3D# explain verbose select f1 is not distinct from null from in=
t4_tbl;
                          QUERY PLAN                           =

---------------------------------------------------------------
 Seq Scan on public.int4_tbl  (cost=3D0.00..1.05 rows=3D5 width=3D1)
   Output: (f1 IS NULL)
(2 rows)

So somebody already inserted this optimization, but I don't see it
happening in eval_const_expressions ... oh, it's way earlier,
in transformAExprDistinct:

    /*
     * If either input is an undecorated NULL literal, transform to a Null=
Test
     * on the other input. That's simpler to process than a full DistinctE=
xpr,
     * and it avoids needing to require that the datatype have an =3D oper=
ator.
     */
    if (exprIsNullConstant(rexpr))
        return make_nulltest_from_distinct(pstate, a, lexpr);
    if (exprIsNullConstant(lexpr))
        return make_nulltest_from_distinct(pstate, a, rexpr);

I'm hesitant to call that wrong; the ability to avoid a dependency on an
"=3D" operator is kind of nice.  But it doesn't help for cases requiring a
Param substitution.

So maybe if we *also* had a check for this in eval_const_expressions,
that would address the OP's problem.  But the use-case would be a bit
narrow given that the parser is catching the simplest case.

			regards, tom lane