From azajac@ang.com.pl Fri Mar  8 11:30:41 2019
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 <pgsql-performance-owner+archive@lists.postgresql.org>)
	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 <pgsql-performance-owner+archive@lists.postgresql.org>)
	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 <azajac@ang.com.pl>)
	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 <azajac@ang.com.pl>)
	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?= <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>
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: <pgsql-performance.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-performance@lists.postgresql.org>
List-Owner: <mailto:pgsql-performance-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-performance>
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

<html xmlns:v=3D"urn:schemas-microsoft-com:vml" =
xmlns:o=3D"urn:schemas-microsoft-com:office:office" =
xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns:m=3D"http://schemas.microsoft.com/office/2004/12/omml" =
xmlns=3D"http://www.w3.org/TR/REC-html40"><head><meta =
http-equiv=3DContent-Type content=3D"text/html; =
charset=3Diso-8859-2"><meta name=3DGenerator content=3D"Microsoft Word =
15 (filtered medium)"><style><!--
/* Font Definitions */
@font-face
	{font-family:"Cambria Math";
	panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
	{font-family:Calibri;
	panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
	{margin:0cm;
	margin-bottom:.0001pt;
	font-size:11.0pt;
	font-family:"Calibri",sans-serif;
	mso-fareast-language:EN-US;}
a:link, span.MsoHyperlink
	{mso-style-priority:99;
	color:#0563C1;
	text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
	{mso-style-priority:99;
	color:#954F72;
	text-decoration:underline;}
span.Stylwiadomocie-mail17
	{mso-style-type:personal-compose;
	font-family:"Calibri",sans-serif;
	color:windowtext;}
.MsoChpDefault
	{mso-style-type:export-only;
	font-family:"Calibri",sans-serif;
	mso-fareast-language:EN-US;}
@page WordSection1
	{size:612.0pt 792.0pt;
	margin:70.85pt 70.85pt 70.85pt 70.85pt;}
div.WordSection1
	{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext=3D"edit" spidmax=3D"1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext=3D"edit">
<o:idmap v:ext=3D"edit" data=3D"1" />
</o:shapelayout></xml><![endif]--></head><body lang=3DPL =
link=3D"#0563C1" vlink=3D"#954F72"><div class=3DWordSection1><p =
class=3DMsoNormal>I have some query:<o:p></o:p></p><p =
class=3DMsoNormal><o:p>&nbsp;</o:p></p><p class=3DMsoNormal>EXPLAIN =
ANALYZE select id from sometable where fkey IS NOT DISTINCT FROM =
21580;<o:p></o:p></p><p class=3DMsoNormal><o:p>&nbsp;</o:p></p><p =
class=3DMsoNormal><o:p>&nbsp;</o:p></p><p =
class=3DMsoNormal>=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<o:p></o:p></p><p =
class=3DMsoNormal>-------------------------------------------------------=
---------------------------------------------------------------------<o:p=
></o:p></p><p class=3DMsoNormal> Gather=A0 (cost=3D10.00..39465.11 =
rows=3D1 width=3D4) (actual time=3D0.512..129.625 rows=3D1 =
loops=3D1)<o:p></o:p></p><p class=3DMsoNormal>=A0=A0 Workers Planned: =
4<o:p></o:p></p><p class=3DMsoNormal>=A0=A0 Workers Launched: =
4<o:p></o:p></p><p class=3DMsoNormal>=A0=A0 -&gt;=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)<o:p></o:p></p><p =
class=3DMsoNormal>=A0=A0=A0=A0=A0=A0=A0=A0 Filter: (NOT (fkey IS =
DISTINCT FROM 21580))<o:p></o:p></p><p =
class=3DMsoNormal>=A0=A0=A0=A0=A0=A0=A0=A0 Rows Removed by Filter: =
675238<o:p></o:p></p><p class=3DMsoNormal> Planning time: 0.101 =
ms<o:p></o:p></p><p class=3DMsoNormal> Execution time: 148.517 =
ms<o:p></o:p></p><p class=3DMsoNormal><o:p>&nbsp;</o:p></p><p =
class=3DMsoNormal><o:p>&nbsp;</o:p></p><p class=3DMsoNormal>Other =
Query:<o:p></o:p></p><p class=3DMsoNormal><o:p>&nbsp;</o:p></p><p =
class=3DMsoNormal>EXPLAIN ANALYZE select id from table where =
fkey=3D21580;<o:p></o:p></p><p class=3DMsoNormal><o:p>&nbsp;</o:p></p><p =
class=3DMsoNormal>=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<o:p></o:p></p><p =
class=3DMsoNormal>-------------------------------------------------------=
-------------------------------------------------------------------------=
--<o:p></o:p></p><p class=3DMsoNormal> 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)<o:p></o:p></p><p =
class=3DMsoNormal>=A0=A0 Index Cond: (fkey =3D 21580)<o:p></o:p></p><p =
class=3DMsoNormal> Planning time: 0.117 ms<o:p></o:p></p><p =
class=3DMsoNormal> Execution time: 0.101 ms<o:p></o:p></p><p =
class=3DMsoNormal>(4 rows)<o:p></o:p></p><p =
class=3DMsoNormal><o:p>&nbsp;</o:p></p><p class=3DMsoNormal>There is =
unique index on sometable(fkey);<o:p></o:p></p><p =
class=3DMsoNormal><o:p>&nbsp;</o:p></p><p class=3DMsoNormal>Is there any =
reason that &#8222;NOT DISTINCT FROM&#8221; can&#8217;t be =
autotransformed to &#8222;=3D&#8221; when value on right side of =
expression is not NULL or is this any way to use index with &#8222;IS =
NOT DISTINCT FROM&#8221; statement?<o:p></o:p></p><p =
class=3DMsoNormal><o:p>&nbsp;</o:p></p><p =
class=3DMsoNormal><o:p>&nbsp;</o:p></p><p class=3DMsoNormal>Artur =
Zajac<o:p></o:p></p><p class=3DMsoNormal><o:p>&nbsp;</o:p></p><p =
class=3DMsoNormal><o:p>&nbsp;</o:p></p></div></body></html>
------=_NextPart_000_012B_01D4D5AA.BEA380D0--





From laurenz.albe@cybertec.at Fri Mar  8 11:45:39 2019
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 <pgsql-performance-owner+archive@lists.postgresql.org>)
	id 1h2Dze-0003Jm-Nj
	for pgsql-performance@arkaria.postgresql.org; Fri, 08 Mar 2019 11:48:50 +0000
Received: from localhost ([127.0.0.1] helo=malur.postgresql.org)
	by malur.postgresql.org with esmtp (Exim 4.89)
	(envelope-from <pgsql-performance-owner+archive@lists.postgresql.org>)
	id 1h2Dzd-00076l-E2
	for pgsql-performance@arkaria.postgresql.org; Fri, 08 Mar 2019 11:48:49 +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 <laurenz.albe@cybertec.at>)
	id 1h2Dwg-0007HO-SD
	for pgsql-performance@lists.postgresql.org; Fri, 08 Mar 2019 11:45:46 +0000
Received: from mx1.mailbox.org ([80.241.60.212])
	by magus.postgresql.org with esmtps (TLS1.2:RSA_AES_256_CBC_SHA1:256)
	(Exim 4.89)
	(envelope-from <laurenz.albe@cybertec.at>)
	id 1h2Dwf-0005zU-8H
	for pgsql-performance@lists.postgresql.org; Fri, 08 Mar 2019 11:45:46 +0000
Received: from smtp1.mailbox.org (smtp1.mailbox.org
 [IPv6:2001:67c:2050:105:465:1:1:0])
	(using TLSv1.2 with cipher ECDHE-RSA-CHACHA20-POLY1305 (256/256 bits))
	(No client certificate requested)
	by mx1.mailbox.org (Postfix) with ESMTPS id B5C4C4C439;
	Fri,  8 Mar 2019 12:45:43 +0100 (CET)
X-Virus-Scanned: amavisd-new at heinlein-support.de
Received: from smtp1.mailbox.org ([80.241.60.240])
	by spamfilter02.heinlein-hosting.de (spamfilter02.heinlein-hosting.de
 [80.241.56.116]) (amavisd-new, port 10030)
	with ESMTP id cHTgfFna5uJG; Fri,  8 Mar 2019 12:45:40 +0100 (CET)
Message-ID: <9d4c7b0de250db9aef716600ca6e39652738ff4e.camel@cybertec.at>
Subject: Re: IS NOT DISTINCT FROM statement
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Artur =?UTF-8?Q?Zaj=C4=85c?= <azajac@ang.com.pl>,
	pgsql-performance@lists.postgresql.org
Date: Fri, 08 Mar 2019 12:45:39 +0100
In-Reply-To: <012a01d4d5a2$5cde0760$169a1620$@ang.com.pl>
References: <012a01d4d5a2$5cde0760$169a1620$@ang.com.pl>
Organization: Cybertec
Content-Type: text/plain; charset="UTF-8"
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
List-Id: <pgsql-performance.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-performance@lists.postgresql.org>
List-Owner: <mailto:pgsql-performance-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-performance>
Precedence: bulk

Artur Zając wrote:
> 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?

That would subtly change the semantics of the expression:

test=> SELECT NULL IS NOT DISTINCT FROM 21580;
 ?column? 
----------
 f
(1 row)

test=> SELECT NULL = 21580;
 ?column? 
----------
 
(1 row)

One expression is FALSE, the other NULL.

It doesn't matter in the context of your specific query, but it could matter.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com





From david.rowley@2ndquadrant.com Fri Mar  8 12:13:49 2019
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 <pgsql-performance-owner+archive@lists.postgresql.org>)
	id 1h2EO8-0004Sq-Pa
	for pgsql-performance@arkaria.postgresql.org; Fri, 08 Mar 2019 12:14:08 +0000
Received: from localhost ([127.0.0.1] helo=malur.postgresql.org)
	by malur.postgresql.org with esmtp (Exim 4.89)
	(envelope-from <pgsql-performance-owner+archive@lists.postgresql.org>)
	id 1h2EO7-0006Su-JM
	for pgsql-performance@arkaria.postgresql.org; Fri, 08 Mar 2019 12:14:07 +0000
Received: from makus.postgresql.org ([2001:4800:3e1:1::229])
	by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256)
	(Exim 4.89)
	(envelope-from <david.rowley@2ndquadrant.com>)
	id 1h2EO7-0006Sn-7W
	for pgsql-performance@lists.postgresql.org; Fri, 08 Mar 2019 12:14:07 +0000
Received: from mail-io1-xd2f.google.com ([2607:f8b0:4864:20::d2f])
	by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256)
	(Exim 4.89)
	(envelope-from <david.rowley@2ndquadrant.com>)
	id 1h2EO4-0001Zj-DR
	for pgsql-performance@lists.postgresql.org; Fri, 08 Mar 2019 12:14:06 +0000
Received: by mail-io1-xd2f.google.com with SMTP id v10so16516278iop.4
        for <pgsql-performance@lists.postgresql.org>;
 Fri, 08 Mar 2019 04:14:04 -0800 (PST)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed;
        d=2ndquadrant-com.20150623.gappssmtp.com; s=20150623;
        h=mime-version:references:in-reply-to:from:date:message-id:subject:to
         :cc:content-transfer-encoding;
        bh=C8GIkDEbDSDHHDEXBa7L3o/BK+ELGn7MMWfLks7tJh4=;
        b=t4uYz3iI8dQCEWciARehOagQihWTcqFMfIQKNMtRafBWRqmRbPrWXEmcURQqid/g9D
         1ytXz8Hpoo7gZazF82L9kSYwtyoBxxSUwQsyo3wbyN+3ATjp/y+uhIcCJPP7RQdOXuPT
         /acSwVZXrqrryL5cy+998yOrfo9hwX7yaP4cTXJ/moNvg3oVYc1yQ5PnUTVRw4qYYaGp
         QQ3xNNmk+syvv2h+v99SxgLmr4BWBvMLrOKQuL1ddcGWxZq31xz27rhglpdy5mGQvkQ1
         +x8y5hV9G6u5e2MvStUs7r5aiarya6zbC6NnnjCvNxHUTxKnxEvU+cYpxacjP0SDn7nq
         GKOg==
X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed;
        d=1e100.net; s=20161025;
        h=x-gm-message-state:mime-version:references:in-reply-to:from:date
         :message-id:subject:to:cc:content-transfer-encoding;
        bh=C8GIkDEbDSDHHDEXBa7L3o/BK+ELGn7MMWfLks7tJh4=;
        b=W7BgGA+ZgGRyMIuA4p0wSGYI5wBBhMOLW29M+VrMOx1tpaqC8gDiCmHRCvDJrkDuiF
         K5gXRMwNgY/Qc8fhW+y4jCWJ2250HX/upzWu5sS28UmyL8Gbjyxm6m/3eTNPkkoIGbIt
         cJLDwLExy3oOXVCTFhM+POmpt8tRfcKlrFE8OStkIfGVQz6ASlbNgWHppvMjKVttcNef
         wdP0KxWm7NSMMLorwqTwKDp1eomFJx9TPZ3seHRRaj370edvXUhajfcsLx0P5C2X6qoY
         JuClF38JndaaS7XY9nFjuyWu9927wPaG4Y3CWnAygWdwziGUdLz2t9eEt/0gtdJA0+/M
         1RiA==
X-Gm-Message-State: APjAAAX1Fjg+hcLSwchBU6pLQ7ZwL8ubZvY5ABww3y3Bavx3m32IxmUw
	GnnbyJyCwut1aTQ61r1UuR/KNS+MNfSE/RcCxu2xqw==
X-Google-Smtp-Source: 
 APXvYqzPemIDvrPdZ7pFO5cl/+T0L564ky5100Vn5oGSu/SjtEMH3BT+4RynAY7XrkgLJLwIOm7T1/NxB/S0eg18XF0=
X-Received: by 2002:a6b:6108:: with SMTP id v8mr9502522iob.159.1552047243577;
 Fri, 08 Mar 2019 04:14:03 -0800 (PST)
MIME-Version: 1.0
References: <012a01d4d5a2$5cde0760$169a1620$@ang.com.pl>
In-Reply-To: <012a01d4d5a2$5cde0760$169a1620$@ang.com.pl>
From: David Rowley <david.rowley@2ndquadrant.com>
Date: Sat, 9 Mar 2019 01:13:49 +1300
Message-ID: 
 <CAKJS1f89WBGzXzEB0b9uVzSGCxMWCe3xnVcH4rJydONK4Afxsg@mail.gmail.com>
Subject: Re: IS NOT DISTINCT FROM statement
To: =?UTF-8?B?QXJ0dXIgWmFqxIVj?= <azajac@ang.com.pl>
Cc: pgsql-performance@lists.postgresql.org
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
List-Id: <pgsql-performance.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-performance@lists.postgresql.org>
List-Owner: <mailto:pgsql-performance-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-performance>
Precedence: bulk

On Sat, 9 Mar 2019 at 00:30, Artur Zaj=C4=85c <azajac@ang.com.pl> wrote:
> Is there any reason that =E2=80=9ENOT DISTINCT FROM=E2=80=9D can=E2=80=99=
t be autotransformed to =E2=80=9E=3D=E2=80=9D when value on right side of e=
xpression is not NULL or is this any way to use index with =E2=80=9EIS NOT =
DISTINCT FROM=E2=80=9D statement?

Probably nothing other than nobody has done it yet.  It might be
reasonable to have some transformation stage called from
distribute_restrictinfo_to_rels() when adding single rel RestrictInfos
to RTE_RELATION base rels.  It's only these you can check for NOT NULL
constraints, i.e. not so possible with rtekinds such as RTE_FUNCTION
and the like.

It becomes more complex if you consider that someone might have added
a partial index on the relation that matches the IS NOT DISTINCT FROM
clause.  In this case, they might not be happy that their index can no
longer be used. Fixing that would require some careful surgery on
predicate_implied_by() to teach it about IS NOT DISTINCT FROM clauses.
However, that seems to go a step beyond what predicate_implied_by()
does for now. Currently, it only gets to know about quals. Not the
relations they belong to, so there'd be no way to know that the NOT
NULL constraint exists from there.  I'm not sure if there's a good
reason for this or not, it might be because it's not been required
before.  It gets more complex still if you want to consider other
quals in the list to prove not nullness.

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

--=20
 David Rowley                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services




From azajac@ang.com.pl Fri Mar  8 12:25:35 2019
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 <pgsql-performance-owner+archive@lists.postgresql.org>)
	id 1h2EZL-00051A-Ai
	for pgsql-performance@arkaria.postgresql.org; Fri, 08 Mar 2019 12:25:43 +0000
Received: from localhost ([127.0.0.1] helo=malur.postgresql.org)
	by malur.postgresql.org with esmtp (Exim 4.89)
	(envelope-from <pgsql-performance-owner+archive@lists.postgresql.org>)
	id 1h2EZJ-0002Pt-Ky
	for pgsql-performance@arkaria.postgresql.org; Fri, 08 Mar 2019 12:25:41 +0000
Received: from makus.postgresql.org ([2001:4800:3e1:1::229])
	by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256)
	(Exim 4.89)
	(envelope-from <azajac@ang.com.pl>)
	id 1h2EZJ-0002PW-A4
	for pgsql-performance@lists.postgresql.org; Fri, 08 Mar 2019 12:25:41 +0000
Received: from cloudserver124499.home.pl ([188.128.182.39])
	by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256)
	(Exim 4.89)
	(envelope-from <azajac@ang.com.pl>)
	id 1h2EZG-0001ot-LV
	for pgsql-performance@lists.postgresql.org; Fri, 08 Mar 2019 12:25:40 +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 634cd114dbdcb22a; Fri, 8 Mar 2019 13:25:36 +0100
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
X-Mailer: Microsoft Outlook 16.0
Thread-Index: AQJoh2uFXzAfPaWSq7jT9mKN46870ADY0NgSpNN8G+A=
Content-Language: pl
List-Id: <pgsql-performance.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-performance@lists.postgresql.org>
List-Owner: <mailto:pgsql-performance-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-performance>
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






From david.rowley@2ndquadrant.com Fri Mar  8 13:12:34 2019
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 <pgsql-performance-owner+archive@lists.postgresql.org>)
	id 1h2FIx-00070h-Um
	for pgsql-performance@arkaria.postgresql.org; Fri, 08 Mar 2019 13:12:52 +0000
Received: from localhost ([127.0.0.1] helo=malur.postgresql.org)
	by malur.postgresql.org with esmtp (Exim 4.89)
	(envelope-from <pgsql-performance-owner+archive@lists.postgresql.org>)
	id 1h2FIw-0001Ky-L0
	for pgsql-performance@arkaria.postgresql.org; Fri, 08 Mar 2019 13:12:50 +0000
Received: from makus.postgresql.org ([2001:4800:3e1:1::229])
	by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256)
	(Exim 4.89)
	(envelope-from <david.rowley@2ndquadrant.com>)
	id 1h2FIw-0001IT-AW
	for pgsql-performance@lists.postgresql.org; Fri, 08 Mar 2019 13:12:50 +0000
Received: from mail-it1-x143.google.com ([2607:f8b0:4864:20::143])
	by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256)
	(Exim 4.89)
	(envelope-from <david.rowley@2ndquadrant.com>)
	id 1h2FIt-00030L-9R
	for pgsql-performance@lists.postgresql.org; Fri, 08 Mar 2019 13:12:49 +0000
Received: by mail-it1-x143.google.com with SMTP id z131so20685605itf.5
        for <pgsql-performance@lists.postgresql.org>;
 Fri, 08 Mar 2019 05:12:47 -0800 (PST)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed;
        d=2ndquadrant-com.20150623.gappssmtp.com; s=20150623;
        h=mime-version:references:in-reply-to:from:date:message-id:subject:to
         :cc:content-transfer-encoding;
        bh=IUcnOGk5Iw6XACHIzrOn2eQO4swhxDDfZdxDX4qCNY0=;
        b=Jy9tnwLE92Piq97k/n2AsA9i4l28Vh7Kpq/51hgHl8lC1j054gpGaQEtlhVGN7lrZD
         45ksuCwuuAd7C7Sp+jtiQbdURKfron0ZJqXmnv2fvMV+9ZTwHcZPrTKB7lgEE9MSzPoO
         Cv4cjqNPnvAr6lyVqOA4fy+mwImwfyb36YC9B/CC8aiKLJpDzOCMz6F9NyyaguEheU4J
         RTYRt4iqMzNUClLWgIXJsYRQEyE6VdR0NOO0sMXVgzE3vl330VOlCvp5/5VP7XgoDnvW
         OBd8gGifPNGzvM0H6Uj9PzZA+WltvUXE/585drkUv75PwMeY0UHSi4+a+AcddNDZIMT2
         1yYQ==
X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed;
        d=1e100.net; s=20161025;
        h=x-gm-message-state:mime-version:references:in-reply-to:from:date
         :message-id:subject:to:cc:content-transfer-encoding;
        bh=IUcnOGk5Iw6XACHIzrOn2eQO4swhxDDfZdxDX4qCNY0=;
        b=s3OnJcfK4t+nYJhlkwRcGqL7cTkcS+AG1YPknDybjctMpyEHpiyKfj66/3WXtnH/tP
         h0Z9qd+UbLE8Atjr3wVLzF5Sp20IA73bzBFa6iaIH3dosBr5EouOpoeaXeTwn7S9a9PK
         tcPmuTzlPxUVnqYh0ejC5K2ENAg2ccW6s7ZwKK6fhwAbY4hCCGkmYFQY/rSWxfQr0J3G
         p31MuG1x+p/YDFJjj2BtpTzAI/oABgemVzoBtqvDlb1dWWF/UcUohuNqMidNAEF9e4t7
         nL4Xw17p6eg2FZmIO8fSyY9Xt8/JOcLt9N8UoTlyDYOJgEmwvTq19TzafnQcumxjYAzK
         ZNlg==
X-Gm-Message-State: APjAAAUGH/LHwj9tVqfjpvdE0T28EwRwKqwbHmcFMcbWGx0DnzEISxXe
	GOYkGaKxl5QtIia23hqUHGuxxJHVyhYlwXPdft50t7ivLUg=
X-Google-Smtp-Source: 
 APXvYqyLnlKgQ0Rx5lcvlQb0SxBtEnI9lekydp/lkT4dw0JE6oNoQeFm1jKIjLgVZabYSwPZZqvpckuC7eDO0lT7c2A=
X-Received: by 2002:a24:450d:: with SMTP id y13mr7916329ita.3.1552050766535;
 Fri, 08 Mar 2019 05:12:46 -0800 (PST)
MIME-Version: 1.0
References: <012a01d4d5a2$5cde0760$169a1620$@ang.com.pl>
 <CAKJS1f89WBGzXzEB0b9uVzSGCxMWCe3xnVcH4rJydONK4Afxsg@mail.gmail.com>
 <014101d4d5aa$084e8ff0$18ebafd0$@ang.com.pl>
In-Reply-To: <014101d4d5aa$084e8ff0$18ebafd0$@ang.com.pl>
From: David Rowley <david.rowley@2ndquadrant.com>
Date: Sat, 9 Mar 2019 02:12:34 +1300
Message-ID: 
 <CAKJS1f8JiBSf6JR5WANbt+vUR-XNu2z6b-mfi6w0gD-8k66b-w@mail.gmail.com>
Subject: Re: IS NOT DISTINCT FROM statement
To: =?UTF-8?B?QXJ0dXIgWmFqxIVj?= <azajac@ang.com.pl>
Cc: pgsql-performance@lists.postgresql.org
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
List-Id: <pgsql-performance.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-performance@lists.postgresql.org>
List-Owner: <mailto:pgsql-performance-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-performance>
Precedence: bulk

On Sat, 9 Mar 2019 at 01:25, Artur Zaj=C4=85c <azajac@ang.com.pl> wrote:
> 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;

> explain analyze select id from sometable where smarteq(id1,NULL);
>                                                                   QUERY P=
LAN
> -------------------------------------------------------------------------=
---------------------------------------------------------------------
>  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=3D=
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 plpgsq=
l (I expect some problems if query is prepared).

I think with either that you'll just be at the mercy of whether a
generic or custom plan is chosen.  If you get a custom plan then
likely your case statement will be inlined and constant folded away,
but for a generic plan, that can't happen since those constants are
not consts, they're parameters.   Most likely, if you've got an index
on the column you'll perhaps always get a custom plan as the generic
plan would result in a seqscan and it would have to evaluate your case
statement for each row.  By default, generic plans are only considered
on the 6th query execution and are only chosen if the generic cost is
cheaper than the average custom plan cost + fuzz cost for planning.
PG12 gives you a bit more control over that with the plan_cache_mode
GUC, but... that's the not out yet.

However, possibly the cost of planning each execution is cheaper than
doing the seq scan, so you might be better off with this.  There is a
risk that the planner does for some reason choose a generic plan and
ends up doing the seq scan, but for that to happen likely the table
would have to be small, in which case it wouldn't matter or the costs
would have to be off, which might cause you some pain.

The transformation mentioned earlier could only work if the arguments
of the IS NOT DISTINCT FROM were Vars or Consts. It couldn't work with
Params since the values are unknown to the planner.

--=20
 David Rowley                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services




From tgl@sss.pgh.pa.us Fri Mar  8 14:53:23 2019
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 <pgsql-performance-owner+archive@lists.postgresql.org>)
	id 1h2GsT-0003CE-Gm
	for pgsql-performance@arkaria.postgresql.org; Fri, 08 Mar 2019 14:53:37 +0000
Received: from localhost ([127.0.0.1] helo=malur.postgresql.org)
	by malur.postgresql.org with esmtp (Exim 4.89)
	(envelope-from <pgsql-performance-owner+archive@lists.postgresql.org>)
	id 1h2GsS-0007py-1h
	for pgsql-performance@arkaria.postgresql.org; Fri, 08 Mar 2019 14:53:36 +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 <tgl@sss.pgh.pa.us>)
	id 1h2GsR-0007Z8-MX
	for pgsql-performance@lists.postgresql.org; Fri, 08 Mar 2019 14:53:35 +0000
Received: from sss.pgh.pa.us ([66.207.139.130])
	by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256)
	(Exim 4.89)
	(envelope-from <tgl@sss.pgh.pa.us>)
	id 1h2GsO-0001ne-Iq
	for pgsql-performance@lists.postgresql.org; Fri, 08 Mar 2019 14:53:34 +0000
Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1])
	by sss.pgh.pa.us (8.14.4/8.14.4) with ESMTP id x28ErNRM028202;
	Fri, 8 Mar 2019 09:53:23 -0500
From: Tom Lane <tgl@sss.pgh.pa.us>
To: David Rowley <david.rowley@2ndquadrant.com>
cc: =?UTF-8?B?QXJ0dXIgWmFqxIVj?= <azajac@ang.com.pl>,
        pgsql-performance@lists.postgresql.org
Subject: Re: IS NOT DISTINCT FROM statement
In-reply-to: 
 <CAKJS1f8JiBSf6JR5WANbt+vUR-XNu2z6b-mfi6w0gD-8k66b-w@mail.gmail.com>
References: <012a01d4d5a2$5cde0760$169a1620$@ang.com.pl>
 <CAKJS1f89WBGzXzEB0b9uVzSGCxMWCe3xnVcH4rJydONK4Afxsg@mail.gmail.com>
 <014101d4d5aa$084e8ff0$18ebafd0$@ang.com.pl>
 <CAKJS1f8JiBSf6JR5WANbt+vUR-XNu2z6b-mfi6w0gD-8k66b-w@mail.gmail.com>
Comments: In-reply-to David Rowley <david.rowley@2ndquadrant.com>
	message dated "Sat, 09 Mar 2019 02:12:34 +1300"
MIME-Version: 1.0
Content-Type: text/plain; charset="UTF-8"
Content-ID: <28200.1552056803.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Fri, 08 Mar 2019 09:53:23 -0500
Message-ID: <28201.1552056803@sss.pgh.pa.us>
List-Id: <pgsql-performance.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-performance@lists.postgresql.org>
List-Owner: <mailto:pgsql-performance-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-performance>
Precedence: bulk

David Rowley <david.rowley@2ndquadrant.com> writes:
> On Sat, 9 Mar 2019 at 01:25, Artur Zaj=C4=85c <azajac@ang.com.pl> wrote:
>> 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;

> The transformation mentioned earlier could only work if the arguments
> of the IS NOT DISTINCT FROM were Vars or Consts. It couldn't work with
> Params since the values are unknown to the planner.

Just looking at this example, I'm wondering if there'd be any value in
adding a rule to eval_const_expressions that converts IS DISTINCT FROM
with one constant-NULL argument into an IS NOT NULL test on the other
argument.  Doing anything with the general case would be hard, as you
mentioned, but this "workaround" suggests that the OP isn't actually
concerned with the general case.

[ experiments... ] Oh, look at this:

regression=3D# explain verbose select f1 is distinct from null from int4_t=
bl;
                          QUERY PLAN                           =

---------------------------------------------------------------
 Seq Scan on public.int4_tbl  (cost=3D0.00..1.05 rows=3D5 width=3D1)
   Output: (f1 IS NOT NULL)
(2 rows)

regression=3D# explain verbose select f1 is not distinct from null from in=
t4_tbl;
                          QUERY PLAN                           =

---------------------------------------------------------------
 Seq Scan on public.int4_tbl  (cost=3D0.00..1.05 rows=3D5 width=3D1)
   Output: (f1 IS NULL)
(2 rows)

So somebody already inserted this optimization, but I don't see it
happening in eval_const_expressions ... oh, it's way earlier,
in transformAExprDistinct:

    /*
     * If either input is an undecorated NULL literal, transform to a Null=
Test
     * on the other input. That's simpler to process than a full DistinctE=
xpr,
     * and it avoids needing to require that the datatype have an =3D oper=
ator.
     */
    if (exprIsNullConstant(rexpr))
        return make_nulltest_from_distinct(pstate, a, lexpr);
    if (exprIsNullConstant(lexpr))
        return make_nulltest_from_distinct(pstate, a, rexpr);

I'm hesitant to call that wrong; the ability to avoid a dependency on an
"=3D" operator is kind of nice.  But it doesn't help for cases requiring a
Param substitution.

So maybe if we *also* had a check for this in eval_const_expressions,
that would address the OP's problem.  But the use-case would be a bit
narrow given that the parser is catching the simplest case.

			regards, tom lane