Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtps  (TLS1.3) tls
 TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384
	(Exim 4.94.2)
	(envelope-from <pgsql-bugs-owner+archive@lists.postgresql.org>)
	id 1qRZCJ-002CUG-Kc
	for pgsql-bugs@arkaria.postgresql.org; Thu, 03 Aug 2023 14:21:04 +0000
Received: from localhost ([127.0.0.1] helo=malur.postgresql.org)
	by malur.postgresql.org with esmtp (Exim 4.94.2)
	(envelope-from <pgsql-bugs-owner+archive@lists.postgresql.org>)
	id 1qRZCI-00E25c-3H
	for pgsql-bugs@arkaria.postgresql.org; Thu, 03 Aug 2023 14:21:02 +0000
Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29])
	by malur.postgresql.org with esmtps  (TLS1.3) tls
 TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384
	(Exim 4.94.2)
	(envelope-from <karg@quipsy.de>)
	id 1qRZCH-00E25U-Ps
	for pgsql-bugs@lists.postgresql.org; Thu, 03 Aug 2023 14:21:02 +0000
Received: from mail.proses.de ([87.234.246.173])
	by magus.postgresql.org with esmtps  (TLS1.3) tls
 TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384
	(Exim 4.94.2)
	(envelope-from <karg@quipsy.de>)
	id 1qRZCF-000M25-Ju
	for pgsql-bugs@lists.postgresql.org; Thu, 03 Aug 2023 14:21:01 +0000
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=quipsy.de; s=seg;
	t=1691072458; bh=b0MMsNbbM6g+kPW3MKvqry9erW2Df9WAKV3udRYAp18=;
	h=From:To:Subject:Date;
	b=bioFzCVBVdPaCXW3FKtpK0KxFS3HrMHBZvQ1i9drqkneoEF8/YBIE4JKJJ/BkB5VJ
	 CDRrpHE0tw70CDvBK8/6Tmlx5pFuzfxKziGO96OqFKDHHut3iHJz9KPXPj0yXW1UC4
	 naeQpU06duxF6U51gVN5P7ZWqJB/+aXlbTtdhFbOM/C6FG08bw7eXWZmaYK6gKvxe3
	 abxlkvUGGo9MkFYy+E4W/gRtkcgGgd6x6oeJXwLAabILVRAwygKQaeR2HsbR/WkQVM
	 6CMM7jSlhU/IWkR1bqs1l0inC4bhkQgzroThyLz2VIk3OEffc1/lWnthwBUQusFRCU
	 /S3lfnyOFrZ5Q==
Received: from ProSeS-MX2016.proses.de (Not Verified[10.147.37.34]) by
 mail.proses.de with Trustwave SEG (v10,0,1,2030)
	id <B64cbb7ca0000>; Thu, 03 Aug 2023 16:20:58 +0200
Received: from ProSeS-MX2016.proses.de (10.147.37.34) by
 ProSeS-MX2016.proses.de (10.147.37.34) with Microsoft SMTP Server
 (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA384) id
 15.1.2242.10; Thu, 3 Aug 2023 16:20:58 +0200
Received: from ProSeS-MX2016.proses.de ([::1]) by ProSeS-MX2016.proses.de
 ([::1]) with mapi id 15.01.2242.010; Thu, 3 Aug 2023 16:20:58 +0200
From: "[Quipsy] Markus Karg" <karg@quipsy.de>
To: "pgsql-bugs@lists.postgresql.org" <pgsql-bugs@lists.postgresql.org>
Subject: WHERE column = X AND column = Y will always be zero matching rows
Thread-Topic: WHERE column = X AND column = Y will always be zero matching
 rows
Thread-Index: AdnGFY1ZyFwM9s1qTQibB1h7OxfWUw==
Date: Thu, 3 Aug 2023 14:20:57 +0000
Message-ID: <1e15568107854a75ae8dd35a5a75daa7@quipsy.de>
Accept-Language: de-DE, en-US
Content-Language: de-DE
X-MS-Has-Attach: 
X-MS-TNEF-Correlator: 
x-originating-ip: [10.147.32.78]
Content-Type: multipart/alternative;
	boundary="_000_1e15568107854a75ae8dd35a5a75daa7quipsyde_"
MIME-Version: 1.0
X-SEG-SpamProfiler-Analysis: v=2.4 cv=L6yj/8f8 c=1 sm=1 tr=0 ts=64cbb7ca
 a=hljXQ6ALYVQa6Cx5z6SxUg==:117 a=xqWC_Br6kY4A:10 a=O53yQbXMW-gA:10
 a=UttIx32zK-AA:10 a=xAD3l70eLPzUgNZen44A:9 a=CjuIK1q_8ugA:10 a=yMhMjlubAAAA:8
 a=SSmOFEACAAAA:8 a=gKO2Hq4RSVkA:10 a=UiCQ7L4-1S4A:10 a=hTZeC7Yk6K0A:10
 a=frz4AuCg-hUA:10 a=rVnDm9A_-c-k2ki-JAcA:9
X-SEG-SpamProfiler-Score: 0
List-Id: <pgsql-bugs.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-bugs@lists.postgresql.org>
List-Owner: <mailto:pgsql-bugs-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-bugs>
Archived-At: 
 <https://www.postgresql.org/message-id/1e15568107854a75ae8dd35a5a75daa7%40quipsy.de>
Precedence: bulk

--_000_1e15568107854a75ae8dd35a5a75daa7quipsyde_
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

Hello PostgreSQL Developers,

I am using PostgreSQL 15.3 (Debian 15.3-1.pgdg120+1) on aarch64-unknown-lin=
ux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit.

(In the following X and Y are literals; X <> Y)

I noticed is that...

EXPLAIN SELECT column FROM Table WHERE column =3D X AND column =3D Y

...says that PostgreSQL actually wants to perform a Scan!

I wonder why squandering any resources into a Scan here, as it is pretty ob=
vious that the result is guaranteed to be always, under any conditions, and=
 will always be: zero matching rows - at least in a universe where a single=
 value cannot be X AND Y at the same time.

This can be seen as a chance to add an optimization ("col =3D X AND col =3D=
 Y always is zero matching rows), or in case such an optimization already e=
xists in PostgreSQL, as a bug.

BTW the same happens for column IS NULL AND column =3D Y.

Regards,
-Markus


--_000_1e15568107854a75ae8dd35a5a75daa7quipsyde_
Content-Type: text/html; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

<html xmlns:v=3D"urn:schemas-microsoft-com:vml" xmlns:o=3D"urn:schemas-micr=
osoft-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=3D"Content-Type" content=3D"text/html; charset=3Dus-ascii"=
>
<meta name=3D"Generator" 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.E-MailFormatvorlage17
	{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 2.0cm 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=3D"DE" link=3D"#0563C1" vlink=3D"#954F72">
<div class=3D"WordSection1">
<p class=3D"MsoNormal">Hello PostgreSQL Developers,<o:p></o:p></p>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
<p class=3D"MsoNormal">I am using PostgreSQL 15.3 (Debian 15.3-1.pgdg120&#4=
3;1) on aarch64-unknown-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.=
0, 64-bit.<o:p></o:p></p>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
<p class=3D"MsoNormal">(In the following X and Y are literals; X &lt;&gt; Y=
)<o:p></o:p></p>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
<p class=3D"MsoNormal">I noticed is that&#8230;<o:p></o:p></p>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
<p class=3D"MsoNormal">EXPLAIN SELECT column FROM Table WHERE column =3D X =
AND column =3D Y<o:p></o:p></p>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
<p class=3D"MsoNormal">&#8230;says that PostgreSQL actually wants to perfor=
m a Scan!<o:p></o:p></p>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
<p class=3D"MsoNormal">I wonder why squandering any resources into a Scan h=
ere, as it is pretty obvious that the result is guaranteed to be always, un=
der any conditions, and will always be: zero matching rows &#8211; at least=
 in a universe where a single value cannot
 be X AND Y at the same time.<o:p></o:p></p>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
<p class=3D"MsoNormal">This can be seen as a chance to add an optimization =
(&#8222;col =3D X AND col =3D Y always is zero matching rows), or in case s=
uch an optimization already exists in PostgreSQL, as a bug. &nbsp;<o:p></o:=
p></p>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
<p class=3D"MsoNormal">BTW the same happens for column IS NULL AND column =
=3D Y.<o:p></o:p></p>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
<p class=3D"MsoNormal">Regards,<o:p></o:p></p>
<p class=3D"MsoNormal">-Markus<o:p></o:p></p>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
</div>
</body>
</html>

--_000_1e15568107854a75ae8dd35a5a75daa7quipsyde_--