IS NOT DISTINCT FROM statement

From: Artur Zając <azajac(at)ang(dot)com(dot)pl>
To: <pgsql-performance(at)lists(dot)postgresql(dot)org>
Subject: IS NOT DISTINCT FROM statement
Date: 2019-03-08 11:30:41
Message-ID: 012a01d4d5a2$5cde0760$169a1620$@ang.com.pl
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Laurenz Albe 2019-03-08 11:45:39 Re: IS NOT DISTINCT FROM statement
Previous Message Julien Rouhaud 2019-03-06 22:59:22 Re: rusage (Re: Good afternoon.)