Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jlIu4-0004iH-Bq for pgsql-performance@arkaria.postgresql.org; Tue, 16 Jun 2020 21:13:56 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1jlItX-0000Tf-Gm for pgsql-performance@arkaria.postgresql.org; Tue, 16 Jun 2020 21:13:23 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jlItW-0000TY-W5 for pgsql-performance@lists.postgresql.org; Tue, 16 Jun 2020 21:13:23 +0000 Received: from sonic310-24.consmr.mail.ne1.yahoo.com ([66.163.186.205]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1jlItQ-0002hV-3t for pgsql-performance@lists.postgresql.org; Tue, 16 Jun 2020 21:13:21 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1592341994; bh=tqH8x6DfCvXSABIlJ8gokU5b+RwV/FWqaq/2p7Pd9P4=; h=Date:From:To:Cc:In-Reply-To:References:Subject:From:Subject; b=hIgnhRVwaVGSIwEOzx667If2WKxRPEUxWoRwcV4bZyl0+YbzusVrHCtPxDMcSy+sKiyLBmPqSgr/RyyEwaOQbq5yF7i4JMSZVgrV+jcTVdA507SNCqPiDjl1oN5I70NK6PucIZwMD9J0piBqSUtpVcCCQ1JHO93jVR0txSKDAAxFpX5O3V+CB9fEyzhaRuMuI8NAeprvZrxheuRl5jN4XMC3ssMCpEgHk5gTmzLKPsD9WlE1keKcGtNaxIFCAVcEbVLuua3ifARbE9k7LcbL6NXd9VFTnOs3fA6cv9Z0EfRBMyuzzIxcGW43al1QE8uX0MMzPZ3L0JyuTd1iEL0JvA== X-YMail-OSG: l38_JKEVM1msQGDvYuGXY9j6ZXtXygiVtQPmz97u_WzbFfhuHTBp8H06ovuRWoo aXazCmm3K9HQAEg6tbg5Wdy_RIDxS8QeXv9euHuOHEjimnnlDs6EDTk34LsxUiw6rQ9BNqLLCmsC hMlC.NFfc6EEd3yYv2.s97ReDL9wKaauSicavQ3iTXLgPAdxhj9QEqTJtxph44KUIc3FIL1A.TJp LHHTlEK8qAqIYkmvuKsdn3z98ChfIPivN9Lh4efbOk7RS2E2jig5swsQGJDWManhD4BS4yLi2dXc r8WZMZpgxm8vxjKpbtZidcmvKTh.zGI5VYOicluBYvIiZ.fVjGRXUkGKYL3D28BfvwsHPH8IKS_O ypKfcrLkk3fGV_TZ_a3OJtIFRtA4aSiew2WJjUuglsn9L.KLC7BpwzKIAj1.CqjgQrHgl71Iv8JH A4LrO_.QTdSmXX5dzImKTbXpWvUD2pqKZbkQzrq7i1r63rVz.7mY7rhdmci93P2GgX_JCd1A7NFz xiJzLLE04QhHIVFhq3UrT1OFzu6wdejQ7ULAnUgLUjmXnSw2RSIBwutpr.PdyDignJbR49d1givs OOIF3iG.GPFhPViqRKZpTE6dN6AVGr2HW.UTdraUf3Hosn9L9OSM.vdOzkkVAyeqiNrpnKXKm_bC PVvbm6ZHKZqUDcH.v0lG6zrqGCOp4iRe.eMnr8ivtkueR5GzqRu7p7xKcOQZ2PlDdznlL8Pxx.07 EDEbs42fEhCZKDmCZqs6O7ryyHvDkhbdrDxojUvoqmtI18NI60o6WjTfDPw.uGX.el05dArwPh.N S2tTM_LxC3t7H_fazwrLYYtAQ.qzevZf2.7jfLNBWxxBXrDZFNqMHEouSIN8YJI99YR978dGkuOH s8Pg8aDy.NpKPv3xXlRxKRY1ik.gJUVwLSt6rLxnb9PECt9f4zfbsG4z6ei0uESxr_0WVd0ZM6vY yvQYGwJmbSoCqcZmd3fALjnhOBNf1qIlRDvB1L2Fto1a6bitpSy.i3ZCB9.Uuk0tCf16U_XIb484 KwRghv0EKtiwNUHcTNOxGGQ3YRg_EJNNBOBi6Ntd0YkOBc6IvV4u_8Ex4px9dIpYlh5FMmGaT2YQ Mrlk8bHjlPSeUGbCrXkzf0dita3g0U2_zQxjj19EoMOW7r60QwHM7TTgKa.ovXv4ZXMcn2JiWV8T n0b5H4J7ol3aKOS.QpimKAevsBIvtIfBP5KyXl3YQmxMzrxnRaxQZrOxqqL4c.hnj7cDFL4xcOeK RIMDvcvqr3X5HFDaiLqLHb0JjA1i3fSo4g65oQotN71Qi_ocj9wlqDJ79RyWIzfYa7fKFWDNonOd VK9sGbEcotTESjGbGde4DKwoEtdwHASMqBdqyUn9Fvc04mw-- Received: from sonic.gate.mail.ne1.yahoo.com by sonic310.consmr.mail.ne1.yahoo.com with HTTP; Tue, 16 Jun 2020 21:13:14 +0000 Date: Tue, 16 Jun 2020 21:13:13 +0000 (UTC) From: Nagaraj Raj To: Michael Lewis Cc: Pgsql Performance Message-ID: <1746629545.1880709.1592341993870@mail.yahoo.com> In-Reply-To: References: <574079062.1873527.1592339731136.ref@mail.yahoo.com> <574079062.1873527.1592339731136@mail.yahoo.com> Subject: Re: simple query running for ever MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_1880708_1539065773.1592341993868" X-Mailer: WebService/1.1.16119 YMailNodin Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_4) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/83.0.4103.97 Safari/537.36 Content-Length: 14436 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk ------=_Part_1880708_1539065773.1592341993868 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Hi Michael, Sorry, I missed table structure, explain select T0."physical_address_sid", T0."individual_entity_proxy_id", = T2."infrrd_hh_rank_nbr" from "cms_prospects".PROSPECT T0 inner join public.t1680035748gcccqqdpmrblxp33_bkp T1 on T0."individual_enti= ty_proxy_id" =3D T1."individual_entity_proxy_id" left join "cms_prospects".INDIVIDUAL_DEMOGRAPHIC T2 on T0."individual_entit= y_proxy_id" =3D T2."individual_entity_proxy_id"; "Hash Join (cost=3D1417.48..21353422.52 rows=3D213620928 width=3D20)" " Hash Cond: ((t0.individual_entity_proxy_id)::numeric =3D t1.individual_en= tity_proxy_id)" " -> Merge Left Join (cost=3D55.96..18147747.08 rows=3D213620928 width=3D20= )" " Merge Cond: (t0.individual_entity_proxy_id =3D t2.individual_entity_proxy= _id)" " -> Index Scan using pk_prospect on prospect t0 (cost=3D0.57..10831606.89 = rows=3D213620928 width=3D16)" " -> Index Only Scan using indxp_individual_demo_infrrd_hh_rank_nbr on indi= vidual_demographic t2 (cost=3D0.57..5013756.93 rows=3D260652064 width=3D12)= " " -> Hash (cost=3D741.79..741.79 rows=3D49579 width=3D8)" " -> Seq Scan on t1680035748gcccqqdpmrblxp33_bkp t1 (cost=3D0.00..741.79 ro= ws=3D49579 width=3D8)" --T0 CREATE TABLE cms_prospects.prospect ( individual_entity_proxy_id bigint NOT NULL, household_entity_proxy_id bigint, individual_personal_link_sid bigint NOT NULL, city_name character varying(100) COLLATE pg_catalog."default", state_prov_cd character varying(40) COLLATE pg_catalog."default", pstl_code character varying(40) COLLATE pg_catalog."default", npa integer, nxx integer, email_domain character varying(400) COLLATE pg_catalog."default", email_preference character varying(40) COLLATE pg_catalog."default", direct_mail_preference character varying(40) COLLATE pg_catalog."default", profane_wrd_ind character(1) COLLATE pg_catalog."default", tmo_ofnsv_name_ind character(1) COLLATE pg_catalog."default", census_block_id character varying(40) COLLATE pg_catalog."default", has_first_name character(1) COLLATE pg_catalog."default", has_middle_name character(1) COLLATE pg_catalog."default", has_last_name character(1) COLLATE pg_catalog."default", has_email_address character(1) COLLATE pg_catalog."default", has_individual_address character(1) COLLATE pg_catalog."default", email_address_sid bigint, person_name_sid bigint, physical_address_sid bigint, telephone_number_sid bigint, last_contacted_email_datetime timestamp without time zone, last_contacted_dm_datetime timestamp without time zone, last_contacted_digital_datetime timestamp without time zone, last_contacted_anychannel_dttm timestamp without time zone, hard_bounce_ind integer, closest_store_site_id1 character varying(40) COLLATE pg_catalog."default", distance_1 numeric(5,2), load_dttm timestamp without time zone NOT NULL, updt_dttm timestamp without time zone, md5_chk_sum character varying(200) COLLATE pg_catalog."default", deld_from_src_ind character(1) COLLATE pg_catalog."default" NOT NULL, orphan_ind character(1) COLLATE pg_catalog."default" NOT NULL, CONSTRAINT pk_prospect PRIMARY KEY (individual_entity_proxy_id) ); --T1 CREATE TABLE public.t1680035748gcccqqdpmrblxp33_bkp( individual_entity_proxy_id numeric(20,0)); -- T2=20 CREATE TABLE cms_prospects.individual_demographic ( individual_entity_proxy_id bigint NOT NULL, cstmr_prspct_ind character varying(40) COLLATE pg_catalog."default", last_appnd_dttm timestamp without time zone, last_sprsn_dttm timestamp without time zone, infrrd_gender_code character varying(40) COLLATE pg_catalog."default", govt_prison_ind character(1) COLLATE pg_catalog."default", tax_bnkrpt_dcsd_ind character(1) COLLATE pg_catalog."default", underbank_rank_nbr integer, hvy_txn_rank_nbr integer, prominence_nbr integer, ocptn_code character varying(40) COLLATE pg_catalog."default", educ_lvl_nbr integer, gender_code character varying(40) COLLATE pg_catalog."default", infrrd_hh_rank_nbr integer, econmc_stable_nbr integer, directv_sbscrbr_propnsty_code character varying(40) COLLATE pg_catalog."de= fault", amazon_prm_propnsty_code character varying(40) COLLATE pg_catalog."default= ", iphone_user_propnsty_code character varying(40) COLLATE pg_catalog."defaul= t", smrt_hm_devc_propnsty_code character varying(40) COLLATE pg_catalog."defau= lt", dog_ownr_propnsty_code character varying(40) COLLATE pg_catalog."default", cat_ownr_propnsty_code character varying(40) COLLATE pg_catalog."default", msc_cncrt_propnsty_code character varying(40) COLLATE pg_catalog."default"= , dine_out_propnsty_code character varying(40) COLLATE pg_catalog."default", taco_bell_diner_propnsty_code character varying(40) COLLATE pg_catalog."de= fault", auto_insrnc_byr_propnsty_code character varying(40) COLLATE pg_catalog."de= fault", load_dttm timestamp without time zone NOT NULL, updt_dttm timestamp without time zone, md5_chk_sum character varying(200) COLLATE pg_catalog."default", deld_from_src_ind character(1) COLLATE pg_catalog."default" NOT NULL, orphan_ind character(1) COLLATE pg_catalog."default" NOT NULL, CONSTRAINT pk_individual_demographic PRIMARY KEY (individual_entity_proxy_= id) ); Server config: PostgreSQL v10.11RAM: 380GB vCore: 32 Shared_buffers: 65G Bwork_mem:104857kB maintenance_work_mem:256MB effective_cache_size: 160GB On Tuesday, June 16, 2020, 01:44:09 PM PDT, Michael Lewis wrote: =20 =20 On Tue, Jun 16, 2020 at 2:35 PM Nagaraj Raj wrote: I wrote a simple query, and it is taking too long, not sure what is wrong i= n it, even its not giving EXPLAIN ANALYZE. More context is needed. Please review- https://wiki.postgresql.org/wiki/Slow_Query_Questions=C2=A0 =20 ------=_Part_1880708_1539065773.1592341993868 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Hi Michael,

Sorry, I missed table structure,

explain select T0."physical_address_sid", T0."indiv= idual_entity_proxy_id", T2."infrrd_hh_rank_nbr"
from &quo= t;cms_prospects".PROSPECT T0
inner join public.t1680035748gcccqqdpm= rblxp33_bkp T1 on T0."individual_entity_proxy_id" =3D T1."in= dividual_entity_proxy_id"
left join "cms_prospects".INDIV= IDUAL_DEMOGRAPHIC T2 on T0."individual_entity_proxy_id" =3D T2.&q= uot;individual_entity_proxy_id";



"Hash Join (cost= =3D1417.48..21353422.52 rows=3D213620928 width=3D20)"
" Hash = Cond: ((t0.individual_entity_proxy_id)::numeric =3D t1.individual_entity_pr= oxy_id)"
" -> Merge Left Join (cost=3D55.96..18147747.08= rows=3D213620928 width=3D20)"
" Merge Cond: (t0.indivi= dual_entity_proxy_id =3D t2.individual_entity_proxy_id)"
" = -> Index Scan using pk_prospect on prospect t0 (cost=3D0.57..10831= 606.89 rows=3D213620928 width=3D16)"
" -> Index Onl= y Scan using indxp_individual_demo_infrrd_hh_rank_nbr on individual_demogra= phic t2 (cost=3D0.57..5013756.93 rows=3D260652064 width=3D12)"
&qu= ot; -> Hash (cost=3D741.79..741.79 rows=3D49579 width=3D8)"
&= quot; -> Seq Scan on t1680035748gcccqqdpmrblxp33_bkp t1 (cost= =3D0.00..741.79 rows=3D49579 width=3D8)"

--T0

CREATE TAB= LE cms_prospects.prospect
(
individual_entity_proxy_id bigint NOT= NULL,
household_entity_proxy_id bigint,
individual_personal_= link_sid bigint NOT NULL,
city_name character varying(100) COLLATE p= g_catalog."default",
state_prov_cd character varying(40) C= OLLATE pg_catalog."default",
pstl_code character varying(4= 0) COLLATE pg_catalog."default",
npa integer,
nxx i= nteger,
email_domain character varying(400) COLLATE pg_catalog."= ;default",
email_preference character varying(40) COLLATE pg_ca= talog."default",
direct_mail_preference character varying(= 40) COLLATE pg_catalog."default",
profane_wrd_ind characte= r(1) COLLATE pg_catalog."default",
tmo_ofnsv_name_ind char= acter(1) COLLATE pg_catalog."default",
census_block_id cha= racter varying(40) COLLATE pg_catalog."default",
has_first= _name character(1) COLLATE pg_catalog."default",
has_middl= e_name character(1) COLLATE pg_catalog."default",
has_last= _name character(1) COLLATE pg_catalog."default",
has_email= _address character(1) COLLATE pg_catalog."default",
has_in= dividual_address character(1) COLLATE pg_catalog."default",
= email_address_sid bigint,
person_name_sid bigint,
physical_= address_sid bigint,
telephone_number_sid bigint,
last_contact= ed_email_datetime timestamp without time zone,
last_contacted_dm_dat= etime timestamp without time zone,
last_contacted_digital_datetime t= imestamp without time zone,
last_contacted_anychannel_dttm timestamp= without time zone,
hard_bounce_ind integer,
closest_store_si= te_id1 character varying(40) COLLATE pg_catalog."default",
= distance_1 numeric(5,2),
load_dttm timestamp without time zone NOT = NULL,
updt_dttm timestamp without time zone,
md5_chk_sum char= acter varying(200) COLLATE pg_catalog."default",
deld_from= _src_ind character(1) COLLATE pg_catalog."default" NOT NULL,
= orphan_ind character(1) COLLATE pg_catalog."default" NOT NULL,=
CONSTRAINT pk_prospect PRIMARY KEY (individual_entity_proxy_id)
= );

--T1
CREATE TABLE public.t1680035748gcccqqdpmrblxp33_bkp(
= individual_entity_proxy_id numeric(20,0));

-- T2

CREATE T= ABLE cms_prospects.individual_demographic
(
individual_entity_pro= xy_id bigint NOT NULL,
cstmr_prspct_ind character varying(40) COLLAT= E pg_catalog."default",
last_appnd_dttm timestamp without = time zone,
last_sprsn_dttm timestamp without time zone,
infrr= d_gender_code character varying(40) COLLATE pg_catalog."default",=
govt_prison_ind character(1) COLLATE pg_catalog."default"= ,
tax_bnkrpt_dcsd_ind character(1) COLLATE pg_catalog."default&= quot;,
underbank_rank_nbr integer,
hvy_txn_rank_nbr integer,<= br> prominence_nbr integer,
ocptn_code character varying(40) COLL= ATE pg_catalog."default",
educ_lvl_nbr integer,
gen= der_code character varying(40) COLLATE pg_catalog."default",
= infrrd_hh_rank_nbr integer,
econmc_stable_nbr integer,
dir= ectv_sbscrbr_propnsty_code character varying(40) COLLATE pg_catalog."d= efault",
amazon_prm_propnsty_code character varying(40) COLLATE= pg_catalog."default",
iphone_user_propnsty_code character= varying(40) COLLATE pg_catalog."default",
smrt_hm_devc_pr= opnsty_code character varying(40) COLLATE pg_catalog."default", dog_ownr_propnsty_code character varying(40) COLLATE pg_catalog."= ;default",
cat_ownr_propnsty_code character varying(40) COLLATE= pg_catalog."default",
msc_cncrt_propnsty_code character v= arying(40) COLLATE pg_catalog."default",
dine_out_propnsty= _code character varying(40) COLLATE pg_catalog."default",
= taco_bell_diner_propnsty_code character varying(40) COLLATE pg_catalog.&quo= t;default",
auto_insrnc_byr_propnsty_code character varying(40)= COLLATE pg_catalog."default",
load_dttm timestamp without= time zone NOT NULL,
updt_dttm timestamp without time zone,
m= d5_chk_sum character varying(200) COLLATE pg_catalog."default", deld_from_src_ind character(1) COLLATE pg_catalog."default"= NOT NULL,
orphan_ind character(1) COLLATE pg_catalog."default&= quot; NOT NULL,
CONSTRAINT pk_individual_demographic PRIMARY KEY (in= dividual_entity_proxy_id)
);


Server config:
PostgreSQL v10= .11RAM: 380GB
vCore: 32
Shared_buffers: 65G
Bwork_mem:104857kB
= maintenance_work_mem:256MB
effective_cache_size: 160GB
<= div style=3D"font-family:'Helvetica Neue', Helvetica, Arial, sans-serif;fon= t-size:13px;color:#26282a;">
= On Tuesday, June 16, 2020, 01:44:09 PM PDT, Michael Lewis <mle= wis@entrata.com> wrote:


On Tue, Jun 16, 2020 at 2:35 = PM Nagaraj Raj <n= agaraj.sf@yahoo.com> wrote:
I wrote a simple query, and it is taking too long, not sur= e what is wrong in it, even its not giving EXPLAIN ANALYZE.


More context is n= eeded. Please review-

=
------=_Part_1880708_1539065773.1592341993868--