Date: Tue, 16 Jun 2020 20:57:43 +0000 (UTC)
From: Nagaraj Raj <nagaraj.sf@yahoo.com>
To: Justin Pryzby <pryzby@telsasoft.com>
Cc: Pgsql Performance <pgsql-performance@lists.postgresql.org>
Message-ID: <2019958432.1888473.1592341063368@mail.yahoo.com>
In-Reply-To: <20200616204659.GA20513@telsasoft.com>
References: <574079062.1873527.1592339731136.ref@mail.yahoo.com>
 <574079062.1873527.1592339731136@mail.yahoo.com>
 <20200616204659.GA20513@telsasoft.com>
Subject: Re: simple query running for ever
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_1888472_124108254.1592341063367"
Content-Length: 5086
Precedence: bulk

------=_Part_1888472_124108254.1592341063367
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

 Hi Justin,

My apologies, I missed that.

Yes, I change work mem to 2GB but didn't see any difference. So, as your su=
ggestion removed the distinct on pk and added a multi-column index so query=
 planner did index-only can that is fixed the issue and query completed in =
1Min.

Best regards,
Rj
     On Tuesday, June 16, 2020, 01:47:21 PM PDT, Justin Pryzby <pryzby@tels=
asoft.com> wrote: =20
=20
 On Tue, Jun 16, 2020 at 08:35:31PM +0000, Nagaraj Raj wrote:
> I wrote a simple query, and it is taking too long, not sure what is wrong=
 in it, even its not giving EXPLAIN ANALYZE.

Is this related to last week's question ?
https://www.postgresql.org/message-id/1211705382.726951.1592174752720%40mai=
l.yahoo.com

Was that issue resolved ?

I didn't see answers to a few questions I asked there.

> select T0."physical_address_sid", T0."individual_entity_proxy_id", T2."in=
frrd_hh_rank_nbr"
> from "cms_prospects".PROSPECT T0
> --inner join "sas_prs_tmp".DEDUPE3583E3F18 T1 on T0."individual_entity_pr=
oxy_id" =3D T1."individual_entity_proxy_id"
> left join "cms_prospects".INDIVIDUAL_DEMOGRAPHIC T2 on T0."individual_ent=
ity_proxy_id" =3D T2."individual_entity_proxy_id"=20
>=20
> "Merge Left Join=C2=A0 (cost=3D55.96..18147747.08 rows=3D213620928 width=
=3D20)"
> "=C2=A0 Merge Cond: (t0.individual_entity_proxy_id =3D t2.individual_enti=
ty_proxy_id)"
> "=C2=A0 ->=C2=A0 Index Scan using pk_prospect on prospect t0=C2=A0 (cost=
=3D0.57..10831606.89 rows=3D213620928 width=3D16)"
> "=C2=A0 ->=C2=A0 Index Only Scan using indxp_individual_demo_infrrd_hh_ra=
nk_nbr on individual_demographic t2=C2=A0 (cost=3D0.57..5013756.93 rows=3D2=
60652064 width=3D12)"
>=20
> Any suggestions or help would be highly appreciated.=20
>=20
> Best regards,
> Rj


 =20
------=_Part_1888472_124108254.1592341063367
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

<div>                Hi Justin,<br><br>My apologies, I missed that.<br><br>=
Yes, I change work mem to 2GB but didn&#39;t see any difference. So, as you=
r suggestion removed the distinct on pk and added a multi-column index so q=
uery planner did index-only can that is fixed the issue and query completed=
 in 1Min.<br><br>Best regards,<br>Rj<br>            </div>            <div =
class=3D"yahoo_quoted" style=3D"margin:10px 0px 0px 0.8ex;border-left:1px s=
olid #ccc;padding-left:1ex;">                        <div style=3D"font-fam=
ily:'Helvetica Neue', Helvetica, Arial, sans-serif;font-size:13px;color:#26=
282a;">                                <div>                    On Tuesday,=
 June 16, 2020, 01:47:21 PM PDT, Justin Pryzby &lt;pryzby@telsasoft.com&gt;=
 wrote:                </div>                <div><br></div>               =
 <div><br></div>                <div>On Tue, Jun 16, 2020 at 08:35:31PM +00=
00, Nagaraj Raj wrote:<br clear=3D"none">&gt; I wrote a simple query, and i=
t is taking too long, not sure what is wrong in it, even its not giving EXP=
LAIN ANALYZE.<br clear=3D"none"><br clear=3D"none">Is this related to last =
week's question ?<br clear=3D"none"><a shape=3D"rect" href=3D"https://www.p=
ostgresql.org/message-id/1211705382.726951.1592174752720%40mail.yahoo.com" =
target=3D"_blank">https://www.postgresql.org/message-id/1211705382.726951.1=
592174752720%40mail.yahoo.com</a><br clear=3D"none"><br clear=3D"none">Was =
that issue resolved ?<br clear=3D"none"><br clear=3D"none">I didn't see ans=
wers to a few questions I asked there.<div class=3D"yqt9093298980" id=3D"yq=
tfd67850"><br clear=3D"none"><br clear=3D"none">&gt; select T0."physical_ad=
dress_sid", T0."individual_entity_proxy_id", T2."infrrd_hh_rank_nbr"<br cle=
ar=3D"none">&gt; from "cms_prospects".PROSPECT T0<br clear=3D"none">&gt; --=
inner join "sas_prs_tmp".DEDUPE3583E3F18 T1 on T0."individual_entity_proxy_=
id" =3D T1."individual_entity_proxy_id"<br clear=3D"none">&gt; left join "c=
ms_prospects".INDIVIDUAL_DEMOGRAPHIC T2 on T0."individual_entity_proxy_id" =
=3D T2."individual_entity_proxy_id" <br clear=3D"none">&gt; <br clear=3D"no=
ne">&gt; "Merge Left Join&nbsp; (cost=3D55.96..18147747.08 rows=3D213620928=
 width=3D20)"<br clear=3D"none">&gt; "&nbsp; Merge Cond: (t0.individual_ent=
ity_proxy_id =3D t2.individual_entity_proxy_id)"<br clear=3D"none">&gt; "&n=
bsp; -&gt;&nbsp; Index Scan using pk_prospect on prospect t0&nbsp; (cost=3D=
0.57..10831606.89 rows=3D213620928 width=3D16)"<br clear=3D"none">&gt; "&nb=
sp; -&gt;&nbsp; Index Only Scan using indxp_individual_demo_infrrd_hh_rank_=
nbr on individual_demographic t2&nbsp; (cost=3D0.57..5013756.93 rows=3D2606=
52064 width=3D12)"<br clear=3D"none">&gt; <br clear=3D"none">&gt; Any sugge=
stions or help would be highly appreciated. <br clear=3D"none">&gt; <br cle=
ar=3D"none">&gt; Best regards,<br clear=3D"none">&gt; Rj<br clear=3D"none">=
<br clear=3D"none"><br clear=3D"none"></div></div>            </div>       =
         </div>
------=_Part_1888472_124108254.1592341063367--