From: Artur Zając <azajac@ang.com.pl>
To: pgsql-performance@lists.postgresql.org
Subject: RE: IS NOT DISTINCT FROM statement
Date: Fri, 8 Mar 2019 13:25:35 +0100
Message-ID: <014101d4d5aa$084e8ff0$18ebafd0$@ang.com.pl> (raw)
In-Reply-To: <CAKJS1f89WBGzXzEB0b9uVzSGCxMWCe3xnVcH4rJydONK4Afxsg@mail.gmail.com>
References: <012a01d4d5a2$5cde0760$169a1620$@ang.com.pl>
<CAKJS1f89WBGzXzEB0b9uVzSGCxMWCe3xnVcH4rJydONK4Afxsg@mail.gmail.com>
> 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=v2 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=0.43..8.45 rows=1 width=4) (actual time=0.085..0.086 rows=1 loops=1)
Index Cond: (id1 = 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=19338.59..57045.02 rows=882343 width=4) (actual time=116.236..306.304 rows=881657 loops=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=892552 loops=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).
Artur Zajac
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: azajac@ang.com.pl, pgsql-performance@lists.postgresql.org
Subject: RE: IS NOT DISTINCT FROM statement
In-Reply-To: <014101d4d5aa$084e8ff0$18ebafd0$@ang.com.pl>
* 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