From: Tom Lane <tgl@sss.pgh.pa.us>
To: David Rowley <david.rowley@2ndquadrant.com>
Cc: Artur Zając <azajac@ang.com.pl>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: IS NOT DISTINCT FROM statement
Date: Fri, 08 Mar 2019 09:53:23 -0500
Message-ID: <28201.1552056803@sss.pgh.pa.us> (raw)
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>
David Rowley <david.rowley@2ndquadrant.com> writes:
> On Sat, 9 Mar 2019 at 01:25, Artur Zając <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=v2 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=# explain verbose select f1 is distinct from null from int4_tbl;
QUERY PLAN
---------------------------------------------------------------
Seq Scan on public.int4_tbl (cost=0.00..1.05 rows=5 width=1)
Output: (f1 IS NOT NULL)
(2 rows)
regression=# explain verbose select f1 is not distinct from null from int4_tbl;
QUERY PLAN
---------------------------------------------------------------
Seq Scan on public.int4_tbl (cost=0.00..1.05 rows=5 width=1)
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 NullTest
* on the other input. That's simpler to process than a full DistinctExpr,
* and it avoids needing to require that the datatype have an = operator.
*/
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
"=" 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, david.rowley@2ndquadrant.com, azajac@ang.com.pl, pgsql-performance@lists.postgresql.org
Subject: Re: IS NOT DISTINCT FROM statement
In-Reply-To: <28201.1552056803@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox