From: "Phillip Couto" <phillip@couto.in> 
Date: Thu, 19 Jan 2017 08:18:19 -0500
Subject: Re: Optimization inner join
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="_=aspNetEmail=_0fe7d448db684598b037f79b5ae37166"
Feedback-ID: 2036153:111586::postmark
To: "Clailson" <clailson.dba@gmail.com>
Cc: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Reply-To: phillip@couto.in
In-Reply-To: <2cd4d430-54ad-4ab8-baae-b597c4a18c43@gmail.com>
References: <7ad1128e-7fdb-9976-823c-d4abdf554fa2@gmail.com>
 <497e3581-bcce-4efc-b281-6b17a93479c4@mtasv.net>
 <2cd4d430-54ad-4ab8-baae-b597c4a18c43@gmail.com>
Message-ID: <dd8dd77e-e09a-45e4-a467-b3ecb8adc749@mtasv.net>
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

--_=aspNetEmail=_0fe7d448db684598b037f79b5ae37166
Content-Type: text/plain;
	charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Ah ok that makes sense=2E I am curious if there is actually a performance=
 benefit to doing that=2E In postgresql as per the execution plan you pro=
vided the Merge Join joins both sets after the have been sorted=2E If the=
y are sorted already then the NULLs will all be grouped at the beginning =
or end=2E (Can=E2=80=99t remember what the ordering is) Postgresql will j=
ust skip all the records with probably the same effort as removing them a=
nd then merging=2E The only performance improvement I could potentially s=
ee is if there is a lot of NULLS in one set then the cost to sort them ma=
y be large enough to recoup by ignoring them before the merge sort=2E=0D=0A=
=0D=0AI hope someone more familiar with the internals can chime in as I w=
ould like to learn more if there is a real benefit here or a better idea =
why postgres does not do it=2E=0D=0A=0D=0A-----------------=0D=0APhillip =
Couto=0D=0A=0D=0A=0D=0A=0D=0A> On Jan 19, 2017, at 08:04, Clailson <clail=
son=2Edba=40gmail=2Ecom> wrote:=0D=0A> =0D=0A> Hi Phillip=2E=0D=0A> =0D=0A=
>> 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=0D=0A> a=2Ea is the=
 primary key on table a and b=2Eb is the foreign key on table b=2E=0D=0A>=
 =0D=0A>  Tabela =22public=2Ea=22 =0D=0A> +--------+---------+-----------=
----+=0D=0A> =7C Coluna =7C  Tipo   =7C Modificadores =7C=0D=0A> +-------=
-+---------+---------------+=0D=0A> =7C a      =7C integer =7C n=C3=A3o n=
ulo      =7C=0D=0A> =7C b      =7C integer =7C               =7C=0D=0A> +=
--------+---------+---------------+=0D=0A> =C3=8Dndices:=0D=0A>     =22a_=
pkey=22 PRIMARY KEY, btree (a)=0D=0A> Referenciada por:=0D=0A>     TABLE =
=22b=22 CONSTRAINT =22b_b_fkey=22 FOREIGN KEY (b) REFERENCES a(a)=0D=0A> =
=0D=0A>          Tabela =22public=2Eb=22 =0D=0A> +--------+---------+----=
-----------+=0D=0A> =7C Coluna =7C  Tipo   =7C Modificadores =7C=0D=0A> +=
--------+---------+---------------+=0D=0A> =7C a      =7C integer =7C n=C3=
=A3o nulo      =7C=0D=0A> =7C b      =7C integer =7C               =7C=0D=
=0A> +--------+---------+---------------+=0D=0A> =C3=8Dndices:=0D=0A>    =
 =22b_pkey=22 PRIMARY KEY, btree (a)=0D=0A> Restri=C3=A7=C3=B5es de chave=
 estrangeira:=0D=0A>     =22b_b_fkey=22 FOREIGN KEY (b) REFERENCES a(a)=0D=
=0A>> =0D=0A>> Not sure if it is all that common=2E Curious what if you p=
ut b=2Eb IS NOT NULL in the WHERE statement?=0D=0A> =0D=0A> It=27s the qu=
estion=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 j=
oining, 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 did the tests wi=
th Oracle and it uses a predicate in the query plan, removing the lines w=
here b=2Eb is null=2E In Oracle, it=C2=B4s the same plan, with and withou=
t IS NOT NULL in the WHERE statement=2E=0D=0A>  -- =0D=0A> Clailson Soare=
s Din=C3=ADzio de Almeida=0D=0A> =0D=0A> On 19/01/2017 09:34, Phillip Cou=
to wrote:=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=2Ea=
 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 yo=
u 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>>> On Ja=
n 19, 2017, at 05:08, Clailson <clailson=2Edba=40gmail=2Ecom <mailto:clai=
lson=2Edba=40gmail=2Ecom>> 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 situation above=2E Table b has 400 rows with null =
in the column b=2E=0D=0A>>> =0D=0A>>> explain analyze select * from a inn=
er 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 row=
s=3D599 loops=3D1)=22 =0D=0A>>> =22  Merge 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 l=
oops=3D1)=22 =0D=0A>>> =22  ->  Index Scan using in01 on b  (cost=3D0=2E2=
8=2E=2E33=2E27 rows=3D1000 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 question is: Why the planner isn=27t removing the null r=
ows during the scan of table b?=0D=0A>>> -- =0D=0A>>> Clailson Soares Din=
=C3=ADzio de Almeida=0D=0A>> =0D=0A> =0D=0A=0D=0A

--_=aspNetEmail=_0fe7d448db684598b037f79b5ae37166
Content-Type: text/html;
	charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<html><head><meta http-equiv=3D=22Content-Type=22 content=3D=22text/html =
charset=3Dutf-8=22></head><body style=3D=22word-wrap: break-word; -webkit=
-nbsp-mode: space; -webkit-line-break: after-white-space;=22 class=3D=22=22=
>Ah ok that makes sense=2E I am curious if there is actually a performanc=
e benefit to doing that=2E In postgresql as per the execution plan you pr=
ovided the Merge Join joins both sets after the have been sorted=2E If th=
ey are sorted already then the NULLs will all be grouped at the beginning=
 or end=2E (Can=E2=80=99t remember what the ordering is) Postgresql will =
just skip all the records with probably the same effort as removing them =
and then merging=2E The only performance improvement I could potentially =
see is if there is a lot of NULLS in one set then the cost to sort them m=
ay be large enough to recoup by ignoring them before the merge sort=2E<di=
v class=3D=22=22><br class=3D=22=22></div><div class=3D=22=22>I hope some=
one more familiar with the internals can chime in as I would like to lear=
n more if there is a real benefit here or a better idea why postgres does=
 not do it=2E</div><div class=3D=22=22>=0D=0A<div style=3D=22color: rgb(0=
, 0, 0); font-family: Helvetica; font-size: 12px; font-style: normal; fon=
t-variant: normal; font-weight: normal; letter-spacing: normal; line-heig=
ht: normal; orphans: auto; text-align: start; text-indent: 0px; text-tran=
sform: none; white-space: normal; widows: auto; word-spacing: 0px; -webki=
t-text-stroke-width: 0px;=22 class=3D=22=22><br class=3D=22Apple-intercha=
nge-newline=22>-----------------</div><div style=3D=22color: rgb(0, 0, 0)=
; font-family: Helvetica; font-size: 12px; font-style: normal; font-varia=
nt: normal; font-weight: normal; letter-spacing: normal; line-height: nor=
mal; orphans: auto; text-align: start; text-indent: 0px; text-transform: =
none; white-space: normal; widows: auto; word-spacing: 0px; -webkit-text-=
stroke-width: 0px;=22 class=3D=22=22>Phillip Couto</div><div style=3D=22c=
olor: rgb(0, 0, 0); font-family: Helvetica; font-size: 12px; font-style: =
normal; font-variant: normal; font-weight: normal; letter-spacing: normal=
; line-height: normal; orphans: auto; text-align: start; text-indent: 0px=
; text-transform: none; white-space: normal; widows: auto; word-spacing: =
0px; -webkit-text-stroke-width: 0px;=22 class=3D=22=22><br class=3D=22=22=
></div><br class=3D=22Apple-interchange-newline=22>=0D=0A</div>=0D=0A<br =
class=3D=22=22><div style=3D=22=22><blockquote type=3D=22cite=22 class=3D=
=22=22><div class=3D=22=22>On Jan 19, 2017, at 08:04, Clailson =26lt;<a h=
ref=3D=22mailto:clailson=2Edba=40gmail=2Ecom=22 class=3D=22=22>clailson=2E=
dba=40gmail=2Ecom</a>=26gt; wrote:</div><br class=3D=22Apple-interchange-=
newline=22><div class=3D=22=22>=0D=0A  =0D=0A    <meta content=3D=22text/=
html; charset=3Dwindows-1252=22 http-equiv=3D=22Content-Type=22 class=3D=22=
=22>=0D=0A  =0D=0A  <div bgcolor=3D=22=23FFFFFF=22 text=3D=22=23000000=22=
 class=3D=22=22>=0D=0A    <font size=3D=22-1=22 class=3D=22=22><font face=
=3D=22Arial=22 class=3D=22=22>Hi Phillip=2E<br class=3D=22=22>=0D=0A     =
 </font></font><br class=3D=22=22>=0D=0A    <blockquote type=3D=22cite=22=
 class=3D=22=22>=0D=0A      <div class=3D=22=22>The only optimization I c=
ould see is if the a=2Ea=0D=0A        column has NOT NULL defined while b=
=2Eb does not have NOT NULL=0D=0A        defined=2E</div>=0D=0A    </bloc=
kquote>=0D=0A    a=2Ea is the primary key on table a and b=2Eb is the for=
eign key on=0D=0A    table b=2E<br class=3D=22=22>=0D=0A    <br class=3D=22=
=22>=0D=0A    <pre class=3D=22=22> Tabela =22public=2Ea=22 =0D=0A+-------=
-+---------+---------------+=0D=0A=7C Coluna =7C  Tipo   =7C Modificadore=
s =7C=0D=0A+--------+---------+---------------+=0D=0A=7C a      =7C integ=
er =7C n=C3=A3o nulo      =7C=0D=0A=7C b      =7C integer =7C            =
   =7C=0D=0A+--------+---------+---------------+=0D=0A=C3=8Dndices:=0D=0A=
    =22a_pkey=22 PRIMARY KEY, btree (a)=0D=0AReferenciada por:=0D=0A    T=
ABLE =22b=22 CONSTRAINT =22b_b_fkey=22 FOREIGN KEY (b) REFERENCES a(a)=0D=
=0A=0D=0A         Tabela =22public=2Eb=22 =0D=0A+--------+---------+-----=
----------+=0D=0A=7C Coluna =7C  Tipo   =7C Modificadores =7C=0D=0A+-----=
---+---------+---------------+=0D=0A=7C a      =7C integer =7C n=C3=A3o n=
ulo      =7C=0D=0A=7C b      =7C integer =7C               =7C=0D=0A+----=
----+---------+---------------+=0D=0A=C3=8Dndices:=0D=0A    =22b_pkey=22 =
PRIMARY KEY, btree (a)=0D=0ARestri=C3=A7=C3=B5es de chave estrangeira:=0D=
=0A    =22b_b_fkey=22 FOREIGN KEY (b) REFERENCES a(a)</pre>=0D=0A    <blo=
ckquote type=3D=22cite=22 class=3D=22=22><br class=3D=22=22>=0D=0A      <=
div class=3D=22=22>Not sure if it is all that common=2E Curious what if=0D=
=0A        you put b=2Eb IS NOT NULL in the WHERE statement?</div>=0D=0A =
   </blockquote>=0D=0A    <br class=3D=22=22>=0D=0A    It=27s the questio=
n=2E I<span id=3D=22result_box=22 class=3D=22=22 tabindex=3D=22-1=22 lang=
=3D=22en=22><span class=3D=22=22>n the company I work with, o</span></spa=
n><span id=3D=22result_box=22 class=3D=22=22 tabindex=3D=22-1=22 lang=3D=22=
en=22><span class=3D=22=22>ne=0D=0A        of my clients asked me: =22Why=
 PostgreSQL does not remove rows=0D=0A        with null in column b (tabl=
e b), before joining, since these=0D=0A        rows have no corresponding=
 in table a?=22 </span></span><span id=3D=22result_box=22 class=3D=22=22 =
tabindex=3D=22-1=22 lang=3D=22en=22><span class=3D=22=22><span id=3D=22re=
sult_box=22 class=3D=22=22 tabindex=3D=22-1=22 lang=3D=22en=22><span clas=
s=3D=22=22>I gave the suggestion to put the IS NOT NULL in the=0D=0A     =
       WHERE </span></span></span></span><span id=3D=22result_box=22 clas=
s=3D=22=22 tabindex=3D=22-1=22 lang=3D=22en=22><span class=3D=22=22><span=
 id=3D=22result_box=22 class=3D=22=22 tabindex=3D=22-1=22 lang=3D=22en=22=
><span class=3D=22=22>statement, but HE can=27t modify the query in the=0D=
=0A            application=2E</span></span> </span><br class=3D=22=22>=0D=
=0A      <span class=3D=22=22><br class=3D=22=22>=0D=0A        I did the =
tests with Oracle and it uses a predicate in the query=0D=0A        plan,=
 removing the lines where b=2Eb is null=2E</span> <span class=3D=22=22>In=
 Oracle, it=C2=B4s the same plan, with and without IS NOT=0D=0A        NU=
LL in the </span></span><span id=3D=22result_box=22 class=3D=22=22 tabind=
ex=3D=22-1=22 lang=3D=22en=22><span class=3D=22=22><span id=3D=22result_b=
ox=22 class=3D=22=22 tabindex=3D=22-1=22 lang=3D=22en=22><span class=3D=22=
=22><span id=3D=22result_box=22 class=3D=22=22 tabindex=3D=22-1=22 lang=3D=
=22en=22><span class=3D=22=22>WHERE </span></span></span></span><span id=3D=
=22result_box=22 class=3D=22=22 tabindex=3D=22-1=22 lang=3D=22en=22><span=
 class=3D=22=22><span id=3D=22result_box=22 class=3D=22=22 tabindex=3D=22=
-1=22 lang=3D=22en=22><span class=3D=22=22>statement</span></span></span>=
</span>=2E</span><br class=3D=22=22>=0D=0A    </span>=0D=0A    <pre class=
=3D=22moz-signature=22 cols=3D=2272=22>-- =0D=0AClailson Soares Din=C3=AD=
zio de Almeida</pre>=0D=0A    <br class=3D=22=22>=0D=0A    <div class=3D=22=
moz-cite-prefix=22>On 19/01/2017 09:34, Phillip Couto=0D=0A      wrote:<b=
r class=3D=22=22>=0D=0A    </div>=0D=0A    <blockquote cite=3D=22mid:497e=
3581-bcce-4efc-b281-6b17a93479c4=40mtasv=2Enet=22 type=3D=22cite=22 class=
=3D=22=22>=0D=0A      <meta http-equiv=3D=22Content-Type=22 content=3D=22=
text/html;=0D=0A        charset=3Dwindows-1252=22 class=3D=22=22>=0D=0A  =
    NULL is still a value that may be paired with a NULL in a=2Ea=0D=0A  =
    <div class=3D=22=22><br class=3D=22=22>=0D=0A      </div>=0D=0A      =
<div class=3D=22=22>The only optimization I could see is if the a=2Ea=0D=0A=
        column has NOT NULL defined while b=2Eb does not have NOT NULL=0D=
=0A        defined=2E</div>=0D=0A      <div class=3D=22=22><br class=3D=22=
=22>=0D=0A      </div>=0D=0A      <div class=3D=22=22>Not sure if it is a=
ll that common=2E Curious what if=0D=0A        you put b=2Eb IS NOT NULL =
in the WHERE statement?<br class=3D=22=22>=0D=0A        <div class=3D=22=22=
>=0D=0A          <div style=3D=22font-family: Helvetica; font-size: 12px;=
 font-style: normal; font-variant-ligatures: normal; font-variant-positio=
n: normal; font-variant-caps: normal; font-variant-numeric: normal; font-=
variant-alternates: normal; font-variant-east-asian: normal; font-weight:=
 normal; letter-spacing: normal; line-height: normal; orphans: auto; text=
-align: start; text-indent: 0px; text-transform: none; white-space: norma=
l; widows: auto; word-spacing: 0px; -webkit-text-stroke-width: 0px;=22 cl=
ass=3D=22=22><br class=3D=22Apple-interchange-newline=22>=0D=0A          =
  -----------------</div>=0D=0A          <div style=3D=22font-family: Hel=
vetica; font-size: 12px; font-style: normal; font-variant-ligatures: norm=
al; font-variant-position: normal; font-variant-caps: normal; font-varian=
t-numeric: normal; font-variant-alternates: normal; font-variant-east-asi=
an: normal; font-weight: normal; letter-spacing: normal; line-height: nor=
mal; orphans: auto; text-align: start; text-indent: 0px; text-transform: =
none; white-space: normal; widows: auto; word-spacing: 0px; -webkit-text-=
stroke-width: 0px;=22 class=3D=22=22>Phillip=0D=0A            Couto</div>=
=0D=0A          <div style=3D=22font-family: Helvetica; font-size: 12px; =
font-style: normal; font-variant-ligatures: normal; font-variant-position=
: normal; font-variant-caps: normal; font-variant-numeric: normal; font-v=
ariant-alternates: normal; font-variant-east-asian: normal; font-weight: =
normal; letter-spacing: normal; line-height: normal; orphans: auto; text-=
align: start; text-indent: 0px; text-transform: none; white-space: normal=
; widows: auto; word-spacing: 0px; -webkit-text-stroke-width: 0px;=22 cla=
ss=3D=22=22><br class=3D=22=22>=0D=0A          </div>=0D=0A          <br =
class=3D=22Apple-interchange-newline=22>=0D=0A        </div>=0D=0A       =
 <br class=3D=22=22>=0D=0A        <div class=3D=22=22>=0D=0A          <bl=
ockquote type=3D=22cite=22 class=3D=22=22>=0D=0A            <div class=3D=
=22=22>On Jan 19, 2017, at 05:08, Clailson =26lt;<a moz-do-not-send=3D=22=
true=22 href=3D=22mailto:clailson=2Edba=40gmail=2Ecom=22 class=3D=22=22>c=
lailson=2Edba=40gmail=2Ecom</a>=26gt;=0D=0A              wrote:</div>=0D=0A=
            <br class=3D=22Apple-interchange-newline=22>=0D=0A           =
 <div class=3D=22=22>=0D=0A              <meta http-equiv=3D=22content-ty=
pe=22 content=3D=22text/html;=0D=0A                charset=3Dwindows-1252=
=22 class=3D=22=22>=0D=0A              <div bgcolor=3D=22=23FFFFFF=22 tex=
t=3D=22=23000000=22 class=3D=22=22> <font class=3D=22=22 size=3D=22-1=22>=
<font class=3D=22=22 face=3D=22Arial=22>Hi,<br class=3D=22=22>=0D=0A     =
               <br class=3D=22=22>=0D=0A                    Is there some=
thing in the roadmap to optimize the=0D=0A                    inner join?=
<br class=3D=22=22>=0D=0A                    <br class=3D=22=22>=0D=0A   =
                 I=27ve this situation </font></font>above=2E Table b ha<=
font class=3D=22=22 size=3D=22-1=22><font class=3D=22=22 face=3D=22Arial=22=
>s 400=0D=0A                    rows with null in the column b=2E<br clas=
s=3D=22=22>=0D=0A                  </font></font><br class=3D=22=22>=0D=0A=
                explain analyze select * from a inner join b on (b=2Eb =3D=
=0D=0A                a=2Ea);=0D=0A                <pre class=3D=22=22>=22=
Merge Join  (cost=3D0=2E55=2E=2E65=2E30 rows=3D599 width=3D16) (actual ti=
me=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 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  -=26gt;  Index Scan using=
 in01 on b  (cost=3D0=2E28=2E=2E33=2E27 rows=3D1000 width=3D8) (actual ti=
me=3D0=2E012=2E=2E0=2E249 rows=3D600 loops=3D1)=22 =0D=0A=22Total runtime=
: 1=2E248 ms=22 =0D=0A=0D=0A<font class=3D=22=22 size=3D=22-1=22><font cl=
ass=3D=22=22 face=3D=22Arial=22>My question is: Why the planner isn=27t r=
emoving the null rows during the scan of table b?</font></font>=0D=0A</pr=
e>=0D=0A                <pre class=3D=22moz-signature=22 cols=3D=2272=22>=
-- =0D=0AClailson Soares Din=C3=ADzio de Almeida=0D=0A</pre>=0D=0A       =
       </div>=0D=0A            </div>=0D=0A          </blockquote>=0D=0A =
       </div>=0D=0A        <br class=3D=22=22>=0D=0A      </div>=0D=0A   =
 </blockquote>=0D=0A    <br class=3D=22=22>=0D=0A  </div>=0D=0A=0D=0A</di=
v></blockquote></div><br class=3D=22=22></body></html>

--_=aspNetEmail=_0fe7d448db684598b037f79b5ae37166--