MIME-Version: 1.0
References: <1503164276875-5979128.post@n3.nabble.com>
 <20170821183226.GW24376@telsasoft.com>
 <CAK2MEjuV+99716Qg7wU8C4LMdYdEXSimzXJ7uf1yH6yhp4e7Qg@mail.gmail.com>
In-Reply-To: 
 <CAK2MEjuV+99716Qg7wU8C4LMdYdEXSimzXJ7uf1yH6yhp4e7Qg@mail.gmail.com>
From: Carlos Augusto Machado <caugustom@gmail.com>
Date: Mon, 21 Aug 2017 19:19:10 +0000
Message-ID: 
 <CAK2MEjur9Mtw+XKAO_DGBXROtLV=84PpNCpuZ7GLenZPt=2Piw@mail.gmail.com>
Subject: Re: Performance Issue -- "Materialize"
To: pgsql-performance@postgresql.org
Content-Type: multipart/alternative; boundary="001a1134f72e13a7eb0557485b17"
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

--001a1134f72e13a7eb0557485b17
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

I think you query is a bit confusing and have many subqueries, so I tried
to simplify

If you cant=C2=B4t have more import_num =3D 0 to the same login, try this

SELECT count(*)
FROM test_tab tab1
LEFT JOIN test_tab tab2
    ON tab1.login =3D tab2.login AND tab2.import_num =3D '0'
WHERE
   tab2.login IS NULL AND
   import_num =3D '4520440'

otherwise try this

SELECT count(*)
FROM test_tab tab1
LEFT JOIN (
   SELECT DISTINCT login FROM test_tab WHERE import_num =3D '0'
) tab2
    ON tab1.login =3D tab2.login
WHERE
   tab2.login IS NULL AND
   import_num =3D '4520440'


Em seg, 21 de ago de 2017 =C3=A0s 15:47, Carlos Augusto Machado <
caugustom@gmail.com> escreveu:

>
> Do you have an index on login column ?
>
> If not, try creating an index and taking off those DISTICTs.
>
> Em seg, 21 de ago de 2017 =C3=A0s 15:33, Justin Pryzby <pryzby@telsasoft.=
com>
> escreveu:
>
>> On Sat, Aug 19, 2017 at 10:37:56AM -0700, anand086 wrote:
>> >
>> +-----------------------------------------------------------------------=
---------------------------------------------------------------------------=
---------+|
>> > QUERY PLAN
>> >
>> |+----------------------------------------------------------------------=
---------------------------------------------------------------------------=
----------+|
>> > Aggregate  (cost=3D351405.08..351405.09 rows=3D1 width=3D8)
>>
>> Would you send explain ANALYZE and not just explain ?
>>
>> Justin
>>
>>
>> --
>> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.or=
g
>> )
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-performance
>>
>

--001a1134f72e13a7eb0557485b17
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div><br></div><div>I think you query is a bit confusing a=
nd have many subqueries, so I tried to simplify</div><div><br></div><div>If=
 you cant=C2=B4t have more import_num =3D 0 to the same login, try this</di=
v><div><br></div><div>SELECT count(*)</div><div>FROM test_tab tab1</div><di=
v>LEFT JOIN test_tab tab2</div><div>=C2=A0 =C2=A0 ON tab1.login =3D tab2.lo=
gin AND tab2.import_num =3D &#39;0&#39;</div><div>WHERE=C2=A0</div><div>=C2=
=A0 =C2=A0tab2.login IS NULL AND</div><div>=C2=A0 =C2=A0import_num =3D &#39=
;4520440&#39;</div><div><br></div><div>otherwise try this</div><div><br></d=
iv><div>SELECT count(*)</div><div>FROM test_tab tab1</div><div>LEFT JOIN (<=
/div><div>=C2=A0 =C2=A0SELECT DISTINCT login FROM test_tab WHERE import_num=
 =3D &#39;0&#39;</div><div>) tab2</div><div>=C2=A0 =C2=A0 ON tab1.login =3D=
 tab2.login</div><div>WHERE=C2=A0</div><div>=C2=A0 =C2=A0tab2.login IS NULL=
 AND</div><div>=C2=A0 =C2=A0import_num =3D &#39;4520440&#39;</div><div><br>=
</div><br><div class=3D"gmail_quote"><div dir=3D"ltr">Em seg, 21 de ago de =
2017 =C3=A0s 15:47, Carlos Augusto Machado &lt;<a href=3D"mailto:caugustom@=
gmail.com">caugustom@gmail.com</a>&gt; escreveu:<br></div><blockquote class=
=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padd=
ing-left:1ex"><div dir=3D"ltr"><br><div>Do you have an index on login colum=
n ?</div><div><br></div><div>If not, try creating an index and taking off t=
hose DISTICTs.</div></div><br><div class=3D"gmail_quote"><div dir=3D"ltr">E=
m seg, 21 de ago de 2017 =C3=A0s 15:33, Justin Pryzby &lt;<a href=3D"mailto=
:pryzby@telsasoft.com" target=3D"_blank">pryzby@telsasoft.com</a>&gt; escre=
veu:<br></div><blockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;=
border-left:1px #ccc solid;padding-left:1ex">On Sat, Aug 19, 2017 at 10:37:=
56AM -0700, anand086 wrote:<br>
&gt; +---------------------------------------------------------------------=
---------------------------------------------------------------------------=
-----------+|<br>
&gt; QUERY PLAN<br>
&gt; |+--------------------------------------------------------------------=
---------------------------------------------------------------------------=
------------+|<br>
&gt; Aggregate=C2=A0 (cost=3D351405.08..351405.09 rows=3D1 width=3D8)<br>
<br>
Would you send explain ANALYZE and not just explain ?<br>
<br>
Justin<br>
<br>
<br>
--<br>
Sent via pgsql-performance mailing list (<a href=3D"mailto:pgsql-performanc=
e@postgresql.org" target=3D"_blank">pgsql-performance@postgresql.org</a>)<b=
r>
To make changes to your subscription:<br>
<a href=3D"http://www.postgresql.org/mailpref/pgsql-performance" rel=3D"nor=
eferrer" target=3D"_blank">http://www.postgresql.org/mailpref/pgsql-perform=
ance</a><br>
</blockquote></div></blockquote></div></div>

--001a1134f72e13a7eb0557485b17--