Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1h2DiB-0002VT-JN for pgsql-performance@arkaria.postgresql.org; Fri, 08 Mar 2019 11:30:47 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1h2Di9-0006wb-Sy for pgsql-performance@arkaria.postgresql.org; Fri, 08 Mar 2019 11:30:45 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1h2Di9-0006wB-KZ for pgsql-performance@lists.postgresql.org; Fri, 08 Mar 2019 11:30:45 +0000 Received: from cloudserver124499.home.pl ([188.128.182.39]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1h2Di8-0005LS-06 for pgsql-performance@lists.postgresql.org; Fri, 08 Mar 2019 11:30:45 +0000 Received: from host-188.117.168.42.static.3s.pl (188.117.168.42) (HELO DESKTOP5IS4FKS) by serwer1372326.home.pl (188.128.182.39) with SMTP (IdeaSmtpServer 0.83.213) id 1cd8bd8f1e5771c4; Fri, 8 Mar 2019 12:30:41 +0100 From: =?iso-8859-2?Q?Artur_Zaj=B1c?= To: Subject: IS NOT DISTINCT FROM statement Date: Fri, 8 Mar 2019 12:30:41 +0100 Message-ID: <012a01d4d5a2$5cde0760$169a1620$@ang.com.pl> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_012B_01D4D5AA.BEA380D0" X-Mailer: Microsoft Outlook 16.0 Thread-Index: AdTVoh2xgo3KASdfTq+DRbEk7c55iA== Content-Language: pl List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multipart message in MIME format. ------=_NextPart_000_012B_01D4D5AA.BEA380D0 Content-Type: text/plain; charset="iso-8859-2" Content-Transfer-Encoding: 7bit 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 ------=_NextPart_000_012B_01D4D5AA.BEA380D0 Content-Type: text/html; charset="iso-8859-2" Content-Transfer-Encoding: quoted-printable

I have some query:

EXPLAIN = ANALYZE select id from sometable where fkey IS NOT DISTINCT FROM = 21580;

=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 QUERY PLAN

-------------------------------------------------------= ---------------------------------------------------------------------

Gather=A0 (cost=3D10.00..39465.11 = rows=3D1 width=3D4) (actual time=3D0.512..129.625 rows=3D1 = loops=3D1)

=A0=A0 Workers Planned: = 4

=A0=A0 Workers Launched: = 4

=A0=A0 ->=A0 Parallel Seq Scan = on sometable=A0 (cost=3D0.00..39455.01 rows=3D1 width=3D4) (actual = time=3D77.995..103.806 rows=3D0 loops=3D5)

=A0=A0=A0=A0=A0=A0=A0=A0 Filter: (NOT (fkey IS = DISTINCT FROM 21580))

=A0=A0=A0=A0=A0=A0=A0=A0 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=3D21580;

=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 QUERY = PLAN

-------------------------------------------------------= -------------------------------------------------------------------------= --

Index Scan using sometable_index1 = on sometable=A0 (cost=3D0.43..8.45 rows=3D1 width=3D4) (actual = time=3D0.075..0.076 rows=3D1 loops=3D1)

=A0=A0 Index Cond: (fkey =3D 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 „=3D” 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

------=_NextPart_000_012B_01D4D5AA.BEA380D0--