Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1cUBxO-00087n-Ea for pgsql-performance@arkaria.postgresql.org; Thu, 19 Jan 2017 12:36:46 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1cUBxO-00086W-1j for pgsql-performance@arkaria.postgresql.org; Thu, 19 Jan 2017 12:36:46 +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 1cUBvh-0005Ro-GI for pgsql-performance@postgresql.org; Thu, 19 Jan 2017 12:35:01 +0000 Received: from mta212a-ord.mtasv.net ([104.245.209.212]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1cUBve-0001yL-Mn for pgsql-performance@postgresql.org; Thu, 19 Jan 2017 12:35:00 +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=mJVycB+e3wwq/1tfpwRKdZ84J+E=; b=dkWTQk0Qo2BybwqGr1SNhM1FdFMyzKAM0Tb1Ubl9pgbgn0p5antnMqV4/CvNQMuvqxhvKqKa6Mj+ KjX/d0vrJ3exyy1z7qvieMT29yNkbhKEoYvoe13KEJz6B3vXQAM+zYXXzJwd6vRLGoAL2It5TtKN YqktnC5dQ3UWTLLFNzw= 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=mJVycB+e3wwq/1tfpwRKdZ84J+E=; b=a+2gMUyULYnEHf6ggPtnCkf+PvPAGp6JaD+1Nyhj7++fPjJ+uZvp1JADILilAIIYhrrfLuuXHxuK vf9JDWxKH55KS24oJqS6GdBkm2dqA6YTs2m3o1r+T7/5JxxuYlg51CAQtCQ2YmveXRKk83yLzXY4 Gsauld6hL5aOdkgtJZg= Received: by mta212a-ord.mtasv.net id hg2p7427m486 for ; Thu, 19 Jan 2017 07:34:57 -0500 (envelope-from ) From: "Phillip Couto" Date: Thu, 19 Jan 2017 07:34:57 -0500 Subject: Re: Optimization inner join MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="_=aspNetEmail=_e54bf6fbda364c54b15e230746783224" X-Mailer: aspNetEmail ver 4.0.0.38 X-Complaints-To: abuse@postmarkapp.com X-PM-RCPT: |bTF8MTExNTg2fDIwMzYxNTN8cGdzcWwtcGVyZm9ybWFuY2VAcG9zdGdyZXNxbC5vcmc=| X-PM-Message-Id: 497e3581-bcce-4efc-b281-6b17a93479c4 Feedback-ID: 2036153:111586::postmark To: "Clailson" Cc: "pgsql-performance@postgresql.org" Reply-To: phillip@couto.in In-Reply-To: <7ad1128e-7fdb-9976-823c-d4abdf554fa2@gmail.com> References: <7ad1128e-7fdb-9976-823c-d4abdf554fa2@gmail.com> X-Mailer: Apple Mail (2.3259) Message-ID: <497e3581-bcce-4efc-b281-6b17a93479c4@mtasv.net> X-Pg-Spam-Score: -2.7 (--) 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=_e54bf6fbda364c54b15e230746783224 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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=2Ea column has NOT NULL def= ined while b=2Eb does not have NOT NULL defined=2E=0D=0A=0D=0ANot sure if= it is all that common=2E Curious what if you put b=2Eb IS NOT NULL in th= e WHERE statement?=0D=0A=0D=0A-----------------=0D=0APhillip Couto=0D=0A=0D= =0A=0D=0A=0D=0A> On Jan 19, 2017, at 05:08, Clailson wrote:=0D=0A> =0D=0A> Hi,=0D=0A> =0D=0A> Is there something in= the roadmap to optimize the inner join?=0D=0A> =0D=0A> I=27ve this situa= tion above=2E Table b has 400 rows with null in the column b=2E=0D=0A> =0D= =0A> explain analyze select * from a inner join b on (b=2Eb =3D a=2Ea);=0D= =0A> =22Merge Join (cost=3D0=2E55=2E=2E65=2E30 rows=3D599 width=3D16) (a= ctual time=3D0=2E030=2E=2E1=2E173 rows=3D599 loops=3D1)=22 =0D=0A> =22 M= erge Cond: (a=2Ea =3D b=2Eb)=22 =0D=0A> =22 -> Index Scan using a_pkey = on a (cost=3D0=2E28=2E=2E35=2E27 rows=3D1000 width=3D8) (actual time=3D0= =2E014=2E=2E0=2E364 rows=3D1000 loops=3D1)=22 =0D=0A> =22 -> Index Scan= using in01 on b (cost=3D0=2E28=2E=2E33=2E27 rows=3D1000 width=3D8) (act= ual time=3D0=2E012=2E=2E0=2E249 rows=3D600 loops=3D1)=22 =0D=0A> =22Total= runtime: 1=2E248 ms=22 =0D=0A> =0D=0A> My question is: Why the planner i= sn=27t removing the null rows during the scan of table b?=0D=0A> -- =0D=0A= > Clailson Soares Din=C3=ADzio de Almeida=0D=0A=0D=0A --_=aspNetEmail=_e54bf6fbda364c54b15e230746783224 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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 defined while b=2Eb does= not have NOT NULL defined=2E

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

---= --------------
Phillip Couto


=0D=0A
=0D=0A
On Jan = 19, 2017, at 05:08, Clailson =26lt;clailson=2Edba=40gmail=2Ecom=26gt; wrote= :

=0D= =0A =0D=0A=0D=0A =0D=0A =0D=0A
=0D=0A = Hi,
=0D=0A
=0D=0A = Is there something in the roadmap to optimize the inner join?
=0D=0A
=0D=0A I=27ve this situati= on above=2E Table b ha= s 400 rows with null in the colum= n b=2E
=0D=0A
=0D= =0A explain analyze select * from a inner join b on (b=2Eb =3D a=2Ea);= =0D=0A 
=22Merge Join  (cost=3D0=2E55=2E=2E65=2E30 =
rows=3D599 width=3D16) (actual time=3D0=2E030=2E=2E1=2E173 rows=3D599 loo=
ps=3D1)=22 =0D=0A=22  Merge Cond: (a=2Ea =3D b=2Eb)=22 =0D=0A=22  -=26gt;=
  Index Scan using a_pkey on a  (cost=3D0=2E28=2E=2E35=2E27 rows=3D1000 w=
idth=3D8) (actual time=3D0=2E014=2E=2E0=2E364 rows=3D1000 loops=3D1)=22 =0D=
=0A=22  -=26gt;  Index Scan using in01 on b  (cost=3D0=2E28=2E=2E33=2E27 =
rows=3D1000 width=3D8) (actual time=3D0=2E012=2E=2E0=2E249 rows=3D600 loo=
ps=3D1)=22 =0D=0A=22Total runtime: 1=2E248 ms=22 =0D=0A=0D=0AMy questi=
on is: Why the planner isn=27t removing the null rows during the scan of =
table b?=0D=0A
=0D=0A 
-- =0D=0AClailson Soares Din=C3=ADzio de Almeida=0D=0A=
=0D=0A
=0D=0A=0D=0A

--_=aspNetEmail=_e54bf6fbda364c54b15e230746783224--