From: =?utf-8?Q?Artur_Zaj=C4=85c?= <azajac@ang.com.pl>
To: <pgsql-performance@lists.postgresql.org>
References: <012a01d4d5a2$5cde0760$169a1620$@ang.com.pl>
 <CAKJS1f89WBGzXzEB0b9uVzSGCxMWCe3xnVcH4rJydONK4Afxsg@mail.gmail.com>
In-Reply-To: 
 <CAKJS1f89WBGzXzEB0b9uVzSGCxMWCe3xnVcH4rJydONK4Afxsg@mail.gmail.com>
Subject: RE: IS NOT DISTINCT FROM statement
Date: Fri, 8 Mar 2019 13:25:35 +0100
Message-ID: <014101d4d5aa$084e8ff0$18ebafd0$@ang.com.pl>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="utf-8"
Content-Transfer-Encoding: quoted-printable
Thread-Index: AQJoh2uFXzAfPaWSq7jT9mKN46870ADY0NgSpNN8G+A=
Content-Language: pl
Precedence: bulk


> In short, probably possible, but why not just write an equality =
clause, if you know NULLs are not possible?

In fact I construct query like this (usually in pl/pgsql).

SELECT column FROM table WHERE column1 IS NOT DISTINCT FROM $1 AND =
column2 IS NOT DISTINCT FROM $2;

"IS NOT DISTINCT FROM" statement simplifies the query ($1 OR $2 may be =
null, col1 and col2 has indexes).

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=3Dv2 END);
$BODY$ LANGUAGE 'sql' IMMUTABLE PARALLEL SAFE;


And then

explain analyze select id from sometable where smarteq(id1,21580);
                                                            QUERY PLAN
-------------------------------------------------------------------------=
---------------------------------------------------------
 Index Scan using sometable_index1 on sometable  (cost=3D0.43..8.45 =
rows=3D1 width=3D4) (actual time=3D0.085..0.086 rows=3D1 loops=3D1)
   Index Cond: (id1 =3D 21580)
 Planning time: 0.223 ms
 Execution time: 0.117 ms
(4 rows)

explain analyze select id from sometable where smarteq(id1,NULL);
                                                                  QUERY =
PLAN
-------------------------------------------------------------------------=
---------------------------------------------------------------------
 Bitmap Heap Scan on sometable  (cost=3D19338.59..57045.02 rows=3D882343 =
width=3D4) (actual time=3D116.236..306.304 rows=3D881657 loops=3D1)
   Recheck Cond: (id1 IS NULL)
   Heap Blocks: exact=3D9581
   ->  Bitmap Index Scan on sometable_index1  (cost=3D0.00..19118.00 =
rows=3D882343 width=3D0) (actual time=3D114.209..114.209 rows=3D892552 =
loops=3D1)
         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).

Artur Zajac