Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1cUDWR-0005RV-RI for pgsql-performance@arkaria.postgresql.org; Thu, 19 Jan 2017 14:17:04 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1cUDWR-0007jH-9S for pgsql-performance@arkaria.postgresql.org; Thu, 19 Jan 2017 14:17:03 +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_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1cUDUh-0004Ut-VW for pgsql-performance@postgresql.org; Thu, 19 Jan 2017 14:15:16 +0000 Received: from mta216a-ord.mtasv.net ([104.245.209.216]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1cUDUd-00051b-N3 for pgsql-performance@postgresql.org; Thu, 19 Jan 2017 14:15:15 +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=Mr67n2J4OGN/KWSqWLYwlOt6Tro=; b=g94XDbHshpEMXn2BUTJ73Teisw9Uh1ZatZ3RVQaRKXx7QPs6mqMZYNVRkTKwfd1A3e5kVaL/mSWg yJBqHAYcc6uf5HVo498toAwmxbNEidmHJd8ntslNcSWgTdLvclTWt61pWCkJjlybZWYANEWwgeBV C70rSV9Y4Hm4JmdR64w= 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=Mr67n2J4OGN/KWSqWLYwlOt6Tro=; b=tvnB+KcyitUmxobsfv5VjbZ8SIZqApoNjN49EYbf6WlwLsYmNfs2Ij9T/UrEcyVqLeTQd5xI7O0y bQLWfIT84lqTdE6Aaz3M0GN6FzzwIt1bO2j8qfwHFJaHDxOUs0VnNskq08yyzPnlBK5CGymwl1gl D0Ha0Yf+letgVy7Eto0= Received: by mta216a-ord.mtasv.net id hg34uu27tk4e for ; Thu, 19 Jan 2017 09:15:09 -0500 (envelope-from ) From: "Phillip Couto" Date: Thu, 19 Jan 2017 09:15:09 -0500 Subject: Re: Optimization inner join MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="_=aspNetEmail=_d0161ab10a1b4c7691e3b31f03dfdcad" X-Mailer: aspNetEmail ver 4.0.0.38 X-Complaints-To: abuse@postmarkapp.com X-PM-RCPT: |bTF8MTExNTg2fDIwMzYxNTN8cGdzcWwtcGVyZm9ybWFuY2VAcG9zdGdyZXNxbC5vcmc=| X-PM-Message-Id: 58c6b116-e24b-4324-a53e-74aa6cbf1b31 Feedback-ID: 2036153:111586::postmark To: "Gustavo Rezende Montesino" Cc: "Clailson" ,"pgsql-performance@postgresql.org" Reply-To: phillip@couto.in In-Reply-To: <5880BDEA.9050804@trtsp.jus.br> References: <7ad1128e-7fdb-9976-823c-d4abdf554fa2@gmail.com> <497e3581-bcce-4efc-b281-6b17a93479c4@mtasv.net> <2cd4d430-54ad-4ab8-baae-b597c4a18c43@gmail.com> <5880BDEA.9050804@trtsp.jus.br> X-Mailer: Apple Mail (2.3259) Message-ID: <58c6b116-e24b-4324-a53e-74aa6cbf1b31@mtasv.net> X-Pg-Spam-Score: -2.2 (--) 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=_d0161ab10a1b4c7691e3b31f03dfdcad Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable The picture is becoming clearer now=2E So to recap the issue is in the pl= an selection not utilizing the null_frac statistic properly to skip what = seems to be in your case 99=25 of the rows which are NULL for the field t= he join is happening on and would be discarded anyways=2E=0D=0A=0D=0AFor = completeness do you mind posting what versions of postgres you have teste= d this on?=0D=0A=0D=0A-----------------=0D=0APhillip Couto=0D=0A=0D=0A=0D= =0A=0D=0A> On Jan 19, 2017, at 08:23, Gustavo Rezende Montesino wrote:=0D=0A> =0D=0A> Hello,=0D=0A> =0D=0A>= Em 19/01/2017 11:04, Clailson escreveu:=0D=0A>> Hi Phillip=2E=0D=0A>> =0D= =0A>> =0D=0A>>> =0D=0A>>> Not sure if it is all that common=2E Curious wh= at if you put b=2Eb IS NOT NULL in the WHERE statement?=0D=0A>> =0D=0A>> = It=27s the question=2E In the company I work with, one of my clients aske= d me: =22Why PostgreSQL does not remove rows with null in column b (table= b), before joining, since these rows have no corresponding in table a?=22= I gave the suggestion to put the IS NOT NULL in the WHERE statement, but= HE can=27t modify the query in the application=2E =0D=0A>> =0D=0A>> I di= d the tests with Oracle and it uses a predicate in the query plan, removi= ng the lines where b=2Eb is null=2E In Oracle, it=C2=B4s the same plan, w= ith and without IS NOT NULL in the WHERE statement=2E=0D=0A> =0D=0A> Bein= g the client in question, I would like to make a little remark: What we t= hought could be optimized here at first is on the row estimate of the ind= ex scan; which could take null_frac into account=2E To put things into pe= rspective, our similar case in production has a table with 6 million line= s where only 9=2E5k aren=C2=B4t null for the join field, an the over-esti= mation is throwing away good plans (like =7E150ms execution time) in favo= r of pretty bad ones (=7E80s execution time)=2E=0D=0A> =0D=0A> We=C2=B4ve= asked application people to put the where not null workaround, which wor= ks great, and are waiting on an answer, but I believe getting better esti= mates without that would be great if possible=2E=0D=0A> =0D=0A>> =0D=0A>>= On 19/01/2017 09:34, Phillip Couto wrote:=0D=0A>>> NULL is still a value= that may be paired with a NULL in a=2Ea=0D=0A> =0D=0A> Is that so? I wou= ld believe you would never get a match, as NULL <> NULL=0D=0A> =0D=0A>>>>= On Jan 19, 2017, at 05:08, Clailson < clailson=2Edba=40gmail=2Ecom > wr= ote:=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 = situation 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) (actual time=3D0=2E030=2E=2E1=2E173 rows=3D599 loops=3D1)=22 = =0D=0A>>>> =22 Merge Cond: (a=2Ea =3D b=2Eb)=22 =0D=0A>>>> =22 -> Inde= x Scan using a_pkey on a (cost=3D0=2E28=2E=2E35=2E27 rows=3D1000 width=3D= 8) (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=3D= 1000 width=3D8) (actual time=3D0=2E012=2E=2E0=2E249 rows=3D600 loops=3D1)= =22 =0D=0A>>>> =22Total runtime: 1=2E248 ms=22 =0D=0A>>>> =0D=0A>>>> My q= uestion is: Why the planner isn=27t removing the null rows during the sca= n of table b?=0D=0A>>>> -- =0D=0A>>>> Clailson Soares Din=C3=ADzio de Alm= eida=0D=0A>>> =0D=0A>> =0D=0A> =0D=0A> =0D=0A> Regards,=0D=0A> =0D=0A> Gu= stavo R=2E Montesino=0D=0A> Tribunal Regional do Trabalho da 2a Regi=C3=A3= o=0D=0A> Secretaria de Tecnologia da Informa=C3=A7=C3=A3o e Comunica=C3=A7= =C3=A3o=0D=0A> Coordenadoria de Infraestrutura de TIC=0D=0A> Se=C3=A7=C3=A3= o de Administra=C3=A7=C3=A3o de Banco de Dados=0D=0A> Av=2E Marqu=C3=AAs = de S=C3=A3o Vicente, 121 - Bl=2E A - Sala 404=0D=0A> Telefone: (11) 3150-= 2082=0D=0A> =0D=0A> =0D=0A=0D=0A --_=aspNetEmail=_d0161ab10a1b4c7691e3b31f03dfdcad Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable The picture is becoming clearer now=2E So to recap the issue is in the p= lan selection not utilizing the null_frac statistic properly to skip what= seems to be in your case 99=25 of the rows which are NULL for the field = the join is happening on and would be discarded anyways=2E

For completeness do you = mind posting what versions of postgres you have tested this on?
=0D=0A

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


=0D=0A
=0D=0A
On= Jan 19, 2017, at 08:23, Gustavo Rezende Montesino =26lt;gustavo=2Emo= ntesino=40trtsp=2Ejus=2Ebr=26gt; wrote:

Hello,

Em 19/01/2017 11:04, Clailson escrev= eu:
Hi Phillip=2E



Not sure if it is all that common=2E Curio= us what if you put b=2Eb IS NOT NULL in the WHERE statement?

It=27s the question=2E In the company I work with, one = of my clients asked me: =22Why PostgreSQL does not remove rows with null = in column b (table b), before joining, since these rows have no correspon= ding in table a?=22=26nbsp;I g= ave the suggestion to put the IS NOT NULL in the WHERE=26nbsp;<= span class=3D=22=22>statement, but HE can=27t m= odify the query in the application=2E=26nbsp;

I did the tests with Oracle and it uses a predi= cate in the query plan, removing the lines where b=2Eb is null=2E<= span class=3D=22Apple-converted-space=22>=26nbsp;In Oracle, it=C2=B4s the same plan, with and without IS NOT NULL in t= he=26nbsp;= WHERE=26nbsp;statement=2E

Being the cli= ent in question, I would like to make a little remark: What we thought co= uld be optimized here at first is on the row estimate of the index scan; = which could take null_frac into account=2E To put things into perspective= , our similar case in production has a table with 6 million lines where o= nly 9=2E5k aren=C2=B4t null for the join field, an the over-estimation is= throwing away good plans (like =7E150ms execution time) in favor of pret= ty bad ones (=7E80s execution time)=2E

We=C2=B4ve asked application people to put the where not nu= ll workaround, which works great, and are waiting on an answer, but I bel= ieve getting better estimates without that would be great if possible=2E<= /span>


On 19/01/2017 09:34, Philli= p Couto wrote:
NULL is still a value that may be paired with a NULL in a=2Ea

Is that so? I= would believe you would never get a match, as NULL =26lt;=26gt; NULL

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

Hi,

Is there = something in the roadmap to optimize the inner join?
I=27ve this situation=26nbsp;above=2E Table b has 400 rows with = null in the column b=2E

explain analyze select * from a inner join b on (b=2Eb =3D a=2Ea);
=22Merge Join  (cost=3D0=2E55=2E=2E65=2E30 rows=3D599 width=
=3D16) (actual time=3D0=2E030=2E=2E1=2E173 rows=3D599 loops=3D1)=22 =0D=0A=
=22  Merge Cond: (a=2Ea =3D b=2Eb)=22 =0D=0A=22  -=26gt;  Index Scan usin=
g 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  -=26gt;=
  Index Scan using in01 on b  (cost=3D0=2E28=2E=2E33=2E27 rows=3D1000 wid=
th=3D8) (actual time=3D0=2E012=2E=2E0=2E249 rows=3D600 loops=3D1)=22 =0D=0A=
=22Total runtime: 1=2E248 ms=22 =0D=0A=0D=0AMy question is: Why the pla=
nner isn=27t removing the null rows during the scan of table b?=0D=0A
-- =0D=0A=
Clailson Soares Din=C3=ADzio de Almeida=0D=0A




Regards,

Gustavo R=2E Montesino=0D=0ATribu=
nal Regional do Trabalho da 2a Regi=C3=A3o=0D=0ASecretaria de Tecnologia =
da Informa=C3=A7=C3=A3o e Comunica=C3=A7=C3=A3o=0D=0ACoordenadoria de Inf=
raestrutura de TIC=0D=0ASe=C3=A7=C3=A3o de Administra=C3=A7=C3=A3o de Ban=
co de Dados=0D=0AAv=2E Marqu=C3=AAs de S=C3=A3o Vicente, 121 - Bl=2E A - =
Sala 404=0D=0ATelefone: (11) 3150-2082



--_=aspNetEmail=_d0161ab10a1b4c7691e3b31f03dfdcad--