From: Artur Zając <azajac@ang.com.pl>
To: pgsql-performance@lists.postgresql.org
Subject: IS NOT DISTINCT FROM statement
Date: Fri, 8 Mar 2019 12:30:41 +0100
Message-ID: <012a01d4d5a2$5cde0760$169a1620$@ang.com.pl> (raw)
I have some query:
EXPLAIN ANALYZE select id from sometable where fkey IS NOT DISTINCT FROM
21580;
QUERY PLAN
----------------------------------------------------------------------------
------------------------------------------------
Gather (cost=10.00..39465.11 rows=1 width=4) (actual time=0.512..129.625
rows=1 loops=1)
Workers Planned: 4
Workers Launched: 4
-> Parallel Seq Scan on sometable (cost=0.00..39455.01 rows=1 width=4)
(actual time=77.995..103.806 rows=0 loops=5)
Filter: (NOT (fkey IS DISTINCT FROM 21580))
Rows Removed by Filter: 675238
Planning time: 0.101 ms
Execution time: 148.517 ms
Other Query:
EXPLAIN ANALYZE select id from table where fkey=21580;
QUERY PLAN
----------------------------------------------------------------------------
------------------------------------------------------
Index Scan using sometable_index1 on sometable (cost=0.43..8.45 rows=1
width=4) (actual time=0.075..0.076 rows=1 loops=1)
Index Cond: (fkey = 21580)
Planning time: 0.117 ms
Execution time: 0.101 ms
(4 rows)
There is unique index on sometable(fkey);
Is there any reason that "NOT DISTINCT FROM" can't be autotransformed to "="
when value on right side of expression is not NULL or is this any way to use
index with "IS NOT DISTINCT FROM" statement?
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: <012a01d4d5a2$5cde0760$169a1620$@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