Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1cUChA-0002L9-EY for pgsql-performance@arkaria.postgresql.org; Thu, 19 Jan 2017 13:24: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 1cUCh9-0005eS-CB for pgsql-performance@arkaria.postgresql.org; Thu, 19 Jan 2017 13:24: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 1cUCh8-0005e1-Jo for pgsql-performance@postgresql.org; Thu, 19 Jan 2017 13:24:02 +0000 Received: from as02.trtsp.jus.br ([177.85.155.221]) by magus.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1cUCh3-0003sn-OT for pgsql-performance@postgresql.org; Thu, 19 Jan 2017 13:24:02 +0000 Received: from localhost (localhost [127.0.0.1]) by as02.trtsp.jus.br (Vertical IP Secure Mail) with ESMTP id C63ED1176281; Thu, 19 Jan 2017 11:23:51 -0200 (BRST) X-VERTICAL-IP-Quarantine-ID: X-VERTICAL-IP-SCANNED: Vertical IP Secure Mail X-VERTICAL-IP-Spam-Score: -3.976 Received: from correio.trtsp.jus.br (correio.trt02.gov.br [10.2.1.40]) by as02-out.trtsp.jus.br (Vertical IP Secure Mail) with SMTP id C159C149C3AC; Thu, 19 Jan 2017 11:23:50 -0200 (BRST) Received: from [10.2.71.120] (unknown [10.2.71.120]) by correio.trtsp.jus.br (Postfix) with ESMTPSA id 3F0F12C1ACC; Thu, 19 Jan 2017 11:23:52 -0200 (BRST) Subject: Re: Optimization inner join To: Clailson , phillip@couto.in References: <7ad1128e-7fdb-9976-823c-d4abdf554fa2@gmail.com> <497e3581-bcce-4efc-b281-6b17a93479c4@mtasv.net> <2cd4d430-54ad-4ab8-baae-b597c4a18c43@gmail.com> Cc: "pgsql-performance@postgresql.org" From: Gustavo Rezende Montesino Message-ID: <5880BDEA.9050804@trtsp.jus.br> Date: Thu, 19 Jan 2017 11:23:54 -0200 User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:38.0) Gecko/20100101 Thunderbird/38.6.0 MIME-Version: 1.0 In-Reply-To: <2cd4d430-54ad-4ab8-baae-b597c4a18c43@gmail.com> Content-Type: multipart/alternative; boundary="------------070107010808040707080300" X-Pg-Spam-Score: -5.1 (-----) 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 This is a multi-part message in MIME format. --------------070107010808040707080300 Content-Type: text/plain; charset=windows-1252; format=flowed Content-Transfer-Encoding: quoted-printable Hello, Em 19/01/2017 11:04, Clailson escreveu: > Hi Phillip. > > >> >> Not sure if it is all that common. Curious what if you put b.b IS NOT=20 >> NULL in the WHERE statement? > > It's the question. In the company I work with, one of my clients asked=20 > me: "Why PostgreSQL does not remove rows with null in column b (table=20 > b), before joining, since these rows have no corresponding in table=20 > a?" I gave the suggestion to put the IS NOT NULL in the WHERE=20 > statement, but HE can't modify the query in the application. > > I did the tests with Oracle and it uses a predicate in the query plan,=20 > removing the lines where b.b is null. In Oracle, it=B4s the same plan,=20 > with and without IS NOT NULL in the WHERE statement. Being the client in question, I would like to make a little remark: What=20 we thought could be optimized here at first is on the row estimate of=20 the index scan; which could take null_frac into account. To put things=20 into perspective, our similar case in production has a table with 6=20 million lines where only 9.5k aren=B4t null for the join field, an the=20 over-estimation is throwing away good plans (like ~150ms execution time)=20 in favor of pretty bad ones (~80s execution time). We=B4ve asked application people to put the where not null workaround,=20 which works great, and are waiting on an answer, but I believe getting=20 better estimates without that would be great if possible. > > On 19/01/2017 09:34, Phillip Couto wrote: >> NULL is still a value that may be paired with a NULL in a.a=20 Is that so? I would believe you would never get a match, as NULL <> NULL >>> On Jan 19, 2017, at 05:08, Clailson >> > wrote: >>> >>> Hi, >>> >>> Is there something in the roadmap to optimize the inner join? >>> >>> I've this situation above. Table b has 400 rows with null in the=20 >>> column b. >>> >>> explain analyze select * from a inner join b on (b.b =3D a.a); >>> "Merge Join (cost=3D0.55..65.30 rows=3D599 width=3D16) (actual time=3D= 0.030..1.173 rows=3D599 loops=3D1)" >>> " Merge Cond: (a.a =3D b.b)" >>> " -> Index Scan using a_pkey on a (cost=3D0.28..35.27 rows=3D1000 = width=3D8) (actual time=3D0.014..0.364 rows=3D1000 loops=3D1)" >>> " -> Index Scan using in01 on b (cost=3D0.28..33.27 rows=3D1000 wi= dth=3D8) (actual time=3D0.012..0.249 rows=3D600 loops=3D1)" >>> "Total runtime: 1.248 ms" >>> >>> My question is: Why the planner isn't removing the null rows during=20 >>> the scan of table b? >>> --=20 >>> Clailson Soares Din=EDzio de Almeida >> > Regards, Gustavo R. Montesino Tribunal Regional do Trabalho da 2a Regi=E3o Secretaria de Tecnologia da Informa=E7=E3o e Comunica=E7=E3o Coordenadoria de Infraestrutura de TIC Se=E7=E3o de Administra=E7=E3o de Banco de Dados Av. Marqu=EAs de S=E3o Vicente, 121 - Bl. A - Sala 404 Telefone: (11) 3150-2082 --------------070107010808040707080300 Content-Type: text/html; charset=windows-1252 Content-Transfer-Encoding: quoted-printable Hello,

Em 19/01/2017 11:04, Clailson escreveu= :

Hi Phillip.

Not sure if it is all that common. Curious what i= f you put b.b IS NOT NULL in the WHERE statement?

It's the question. In the company I work with, o<= /span>ne of my clients asked me: "Why PostgreSQL does not remove rows with null in column b (table b), before joining, since these rows have no corresponding in table a?" I gave the suggestion to put the IS NOT NULL in the WHERE statement, but HE can't modify the query in the application.

I did the tests with Oracle and it uses a predicate in the query plan, removing the lines where b.b is null. In Oracle, it=B4s the same plan, with and without IS NOT NULL in the = WHERE statement.

Being the client in question, I would like to make a little remark: What we thought could be optimized here at first is on the row estimate of the index scan; which could take null_frac into account. To put things into perspective, our similar case in production has a table with 6 million lines where only 9.5k aren=B4t null for the join field, an the over-estimation is throwing away good plans (like ~150ms execution time) in favor of pretty bad ones (~80s execution time).

We=B4ve asked application people to put the where not null workaround= , which works great, and are waiting on an answer, but I believe getting better estimates without that would be great if possible.

On 19/01/2017 09:34, Phillip Couto wrote:

NULL is still a value that may be paired with a NULL in a.a

Is that so? I would believe you would never get a match, as NULL <> NULL
On Jan 19, 2017, at 05:08, Clailson <c= lailson.dba@gmail.com> wrote:
Hi,

Is there something in the roadmap to optimize the inner join?

I've this situation above. Table b has 400 rows with null in the column b.

explain analyze select * from a inner join b on (b.b =3D a.a);
"Merge Join (cost=3D0.55..65.30 rows=3D= 599 width=3D16) (actual time=3D0.030..1.173 rows=3D599 loops=3D1)"=20 " Merge Cond: (a.a =3D b.b)"=20 " -> Index Scan using a_pkey on a (cost=3D0.28..35.27 rows=3D1000 w= idth=3D8) (actual time=3D0.014..0.364 rows=3D1000 loops=3D1)"=20 " -> Index Scan using in01 on b (cost=3D0.28..33.27 rows=3D1000 wid= th=3D8) (actual time=3D0.012..0.249 rows=3D600 loops=3D1)"=20 "Total runtime: 1.248 ms"=20 My question = is: Why the planner isn't removing the null rows during the scan of table= b?

--=20 Clailson Soares Din=EDzio de Almeida
Regards,
Gustavo R. Montesino
Tribunal Regional do Trabalho da 2a Regi=E3o
Secretaria de Tecnologia da Informa=E7=E3o e Comunica=E7=E3o
Coordenadoria de Infraestrutura de TIC
Se=E7=E3o de Administra=E7=E3o de Banco de Dados
Av. Marqu=EAs de S=E3o Vicente, 121 - Bl. A - Sala 404
Telefone: (11) 3150-2082
--------------070107010808040707080300--