Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1cUD4B-0003l1-M2 for pgsql-performance@arkaria.postgresql.org; Thu, 19 Jan 2017 13:47:51 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1cUD4B-0002Io-8w for pgsql-performance@arkaria.postgresql.org; Thu, 19 Jan 2017 13:47:51 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1cUD2V-0000L5-8r for pgsql-performance@postgresql.org; Thu, 19 Jan 2017 13:46:07 +0000 Received: from sc-ord-mta122.mtasv.net ([50.31.156.122]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1cUD2S-0003ZX-7h for pgsql-performance@postgresql.org; Thu, 19 Jan 2017 13:46:06 +0000 DKIM-Signature: v=1; a=rsa-sha1; c=relaxed/relaxed; s=20160609055401pm; d=couto.in; h=From:Date:Subject:MIME-Version:Content-Type:To:Cc:Reply-To:In-Reply-To:References:Message-ID; i=phillip@couto.in; bh=Vbdvi/6iTXlsdBKWfB4BxLhG1QY=; b=ZLqz3YvAxPnVZD8G4JdPKl7ueoXfkiT2hxy83wsMAljlGZHLf3X7U0qpOlJMUbOTeIbL9FDZz7st G82EgXQHA26yfG8llGi6AcgaqWr4oZ8/N64CDTOPdRE3F6RkEr4g1CdYLTZMYy1Pt0tKRqKh97rb dRP6G6SvhhiEOs4etes= DKIM-Signature: v=1; a=rsa-sha1; c=relaxed/relaxed; s=pm; d=pm.mtasv.net; h=From:Date:Subject:MIME-Version:Content-Type:To:Cc:Reply-To:In-Reply-To:References:Message-ID; bh=Vbdvi/6iTXlsdBKWfB4BxLhG1QY=; b=rx9zMiEWfDc0ct2a75HtP14SJRDiEd7/fw5QJwkhgqz27tLyR6gVV+1En5aGHQFn/Y3ZyhKRvCzK 1CJ+ZSUR03S+OmnYnDxCSetSjs6pq7/3qITqMg4VJP8V1sV/UOwOrYqAKdnS2Q4x+rzJZrrno5zn DwjYcihgkoIHdHjEWvM= Received: by sc-ord-mta122.mtasv.net id hg31hm27m48r for ; Thu, 19 Jan 2017 08:46:02 -0500 (envelope-from ) X-IADB-IP: 50.31.156.122 X-IADB-IP-REVERSE: 122.156.31.50 From: "Phillip Couto" Date: Thu, 19 Jan 2017 08:46:03 -0500 Subject: Re: Optimization inner join MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="_=aspNetEmail=_ce6300655f8f4e878bb53ba0810fa98c" X-Mailer: aspNetEmail ver 4.0.0.38 X-Complaints-To: abuse@postmarkapp.com X-PM-RCPT: |bTF8MTExNTg2fDIwMzYxNTN8cGdzcWwtcGVyZm9ybWFuY2VAcG9zdGdyZXNxbC5vcmc=| X-PM-Message-Id: 07fa17ae-ed3c-42bb-8a63-e24093cbac3d Feedback-ID: 2036153:111586::postmark To: "Vitalii Tymchyshyn" Cc: "Clailson" ,"pgsql-performance@postgresql.org" Reply-To: phillip@couto.in In-Reply-To: References: <7ad1128e-7fdb-9976-823c-d4abdf554fa2@gmail.com> <497e3581-bcce-4efc-b281-6b17a93479c4@mtasv.net> X-Mailer: Apple Mail (2.3259) Message-ID: <07fa17ae-ed3c-42bb-8a63-e24093cbac3d@mtasv.net> X-Pg-Spam-Score: -7.9 (-------) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org --_=aspNetEmail=_ce6300655f8f4e878bb53ba0810fa98c Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable I apologize my statement about NULL being used to join is incorrect as bo= th Vitalii and Gustavo have both pointed out in their respective replies=2E= =0D=0A=0D=0A-----------------=0D=0APhillip Couto=0D=0A=0D=0A=0D=0A=0D=0A>= On Jan 19, 2017, at 08:30, Vitalii Tymchyshyn wrote:=0D= =0A> =0D=0A> =0D=0A> Hi=2E=0D=0A> =0D=0A> In SQL =22null =3D=3D any value= =22 resolves to false, so optimizer can safely skip nulls from either sid= e if any for the inner join=2E=0D=0A> =0D=0A> Best regards, Vitalii Tymch= yshyn=0D=0A> =0D=0A> NULL is still a value that may be paired with a NULL= in a=2Ea=0D=0A> =0D=0A> The only optimization I could see is if the a=2E= a column has NOT NULL defined while b=2Eb does not have NOT NULL defined=2E= =0D=0A> =0D=0A> Not sure if it is all that common=2E Curious what if you = put b=2Eb IS NOT NULL in the WHERE statement?=0D=0A> =0D=0A> ------------= -----=0D=0A> Phillip Couto=0D=0A> =0D=0A> =0D=0A> =0D=0A> =0D=0A=0D=0A --_=aspNetEmail=_ce6300655f8f4e878bb53ba0810fa98c Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable I apologize my statement about NULL being used to join is incorrect a= s both Vitalii and Gustavo have both pointed out in their respective repl= ies=2E
=0D=0A

-----------------
Phillip Couto


=0D=0A
=0D=0A<= br class=3D=22=22>
On Jan 19, 2017, at 08:30, Vitalii Tymchyshyn =26lt;vit=40tym=2Eim=26gt; w= rote:


Hi=2E

In SQL =22null =3D=3D any value=22 resolves to false, so= optimizer can safely skip nulls from either side if any for the inner jo= in=2E

Best regards, Vitalii Tymchyshyn

NULL= is still a value that may be paired with a NULL in a=2Ea

The only optimization I could see is if the a=2Ea column has NOT NULL de= fined while b=2Eb does not have NOT NULL defined=2E

Not sure if it is all that common=2E Curious what if you put b=2Eb IS NO= T NULL in the WHERE statement?
=0D=0A

-----------------
Phillip Couto


=0D=0A
=0D=0A

=0D=0A

--_=aspNetEmail=_ce6300655f8f4e878bb53ba0810fa98c--