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 1jlIwC-0004md-J4 for pgsql-performance@arkaria.postgresql.org; Tue, 16 Jun 2020 21:16:08 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1jlIwB-000501-Fo for pgsql-performance@arkaria.postgresql.org; Tue, 16 Jun 2020 21:16:07 +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 1jlIwB-0004zu-0Q for pgsql-performance@lists.postgresql.org; Tue, 16 Jun 2020 21:16:07 +0000 Received: from sonic309-21.consmr.mail.ne1.yahoo.com ([66.163.184.147]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1jlIw8-0002jV-DL for pgsql-performance@lists.postgresql.org; Tue, 16 Jun 2020 21:16:06 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1592342163; bh=T+LU4rv8W7kIL8G/TQRQvTFW7usPdJOPpc8jBD/pQ2Y=; h=Date:From:To:Cc:In-Reply-To:References:Subject:From:Subject; b=az8bzJgLbf5rxCnHzefKTOcF7xc4OpIxUNYxCikbTLzhAW76nn7GUKxmn0VWX4oVoXGx72M3A6KpgqwUkHsv8CYs5/8BhiBiEGvADvg9WmWZ/dtcXB4wo3MEAX+FovuH4DFpa4w3OohF01joAp0+ck1Me05PwdHI+izju1CssYxZSrLdnvfD8DZNNCHWXhjSlTvyyTPebvLepFwlx3dYsHcWLGBWqQ//UqgPJBxqOD7oDtF9mdXql+7LhUiuWCVJ7WMu4RbH2BEArHIGml2IW5rdKB9PpW1UnwxnAowgah87gUKaqQ7yxhaaO7hmoptzGdHSDgdKC1Bv7N3bPWOiZQ== X-YMail-OSG: pjK4p9QVM1mbJBa9c0kn7qoyipaYzGQMAEGD3ChZLwhZi6cIlwUNF3vAoXcaTF0 uIsqxQUeqpq9RTdg56c9l2UyG0G_yU.RvPs_n0sMtPWU_pKgg8XKU98Md8mgGUUR4CwUzxcspPrq V5OXleGxJ68qGMM8s.rkru3.z4lu.woDeI9_92swK9cFDgsvrsb3d7prsmgTFfeJMvWRFn48TtrJ 5uO92FJ3ciV0nGPpIgp29S5sdiwQy9WqgsIhbSfO4OQJ_DS.z5Lp5hTK0HKgY89K2nNQ99drUW6F qHGScgDrpXYvstz_1R0awJNd4WLTdmejAqs__1we7Y5eLZRm2iOYIsrSKhDVXhVP_xn0bb97N3_M fvbG.awRAZwtF_Hh0zDQSIbWIBQiehc1qxT2KI_4bcAapwfphB7YNHRNS6tsZLGJgnS1RogpAlsN Omd3xIuQXmHFs8ffv3wzM9lzHTZgkDF4IufYLnzhJhJojpyikVtccrN_4jKQYoCbtaRXX9a0Qp7R 8BCVB37CaGVI3ABdmvSMNe3hWJA1bgHtuMKVne4oWRbdoUF_NeWzldEkZwwlsDfTW9yDhE80tdwX ee4nisQjhHTaLJUbt7pGnvH9YhUAvIJsjXGoDdvxg.t3zszHqb3WNErm7a6pvRYrfkR6uLTuj_50 eYDnNjeDiXCynRqGMyoUSLQplzTdawSav7b0rchxXsSeytokPSJ9Z_Oxyttcx.KYmWGIHH.Fttsa TIqzN0v54yZu6pv2NKUv6ok6b4wFWJBJcWYOjK5BR45GhvYkN5OsmDvVk5NAiEao9FRZ5Ko2L.iZ U_B3905C57ek6YNaqzPOL_IsHPRDHKeMNijZ0REuEIwOTTFbho4uFy.OXy4x77QdKUoxv4GJiy7y nuT_fzjm6TxK0MMRpj2LjcLFUsV7cbk27ix0cwDsj7.IBro4lr1jI_f_EViSCXa5GSwIP4lFnzBT ot_OdFtT6DkFEoipxGorwMWewp2_0GgZtrc.PUFbVGS9TNpxyw8beJIksoPd5L8UoAk9XKOReL01 qp.QI9i2IFg0bWXyNXK8DtnFtwAWfijIBU2TH2PXBUgK9si0ToHFhJvYDUEBWFvgZmfOW7QG..Lu 8.DdhsGSAvjyZZ1dbk1XuiycGk837DZjTTsNGdRCjy.bYCQ6Wipy2jrxZcYmFwTHIBstmNOQfTja Xu4nLJPRtgZqwgzVRGUGUd4DFvG8kmLU95T4cRe9nm_mbIcGiG4sV.UPpYxmYL7b40bUmbaX3raI J00W.0S9S4yK1lto.3MPg8At6mmKpZv9045oeVgSGTMkCdkp.R_OhvmvZc3gaEpYZQaDIpSvF0ao DXZUPDrSKZUdVBFxl0bLBVZzC1qlTy9QcMf8- Received: from sonic.gate.mail.ne1.yahoo.com by sonic309.consmr.mail.ne1.yahoo.com with HTTP; Tue, 16 Jun 2020 21:16:03 +0000 Date: Tue, 16 Jun 2020 21:15:58 +0000 (UTC) From: Nagaraj Raj To: Michael Lewis Cc: Pgsql Performance Message-ID: <779859179.1893101.1592342158978@mail.yahoo.com> In-Reply-To: <1746629545.1880709.1592341993870@mail.yahoo.com> References: <574079062.1873527.1592339731136.ref@mail.yahoo.com> <574079062.1873527.1592339731136@mail.yahoo.com> <1746629545.1880709.1592341993870@mail.yahoo.com> Subject: Re: simple query running for ever MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_1893100_507263748.1592342158976" 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: 16649 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk ------=_Part_1893100_507263748.1592342158976 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable And here is the explain analyze: https://explain.depesz.com/s/uQGA Thanks! On Tuesday, June 16, 2020, 02:13:37 PM PDT, Nagaraj Raj wrote: =20 =20 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_1893100_507263748.1592342158976 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
And here is the explain analyze:

https://explai= n.depesz.com/s/uQGA

Thanks!
On Tuesday, J= une 16, 2020, 02:13:37 PM PDT, Nagaraj Raj <nagaraj.sf@yahoo.com> wro= te:


= Hi Michael,

Sorry, I missed = table structure,


ex= plain select T0."physical_address_sid", T0."individual_entity_proxy_id", T2= ."infrrd_hh_rank_nbr"
from "cms_prospects".PROSPECT T0inner join public.t1680035748gcccqqdpmrblxp33_bkp T1 on T0.= "individual_entity_proxy_id" =3D T1."individual_entity_proxy_id"
left join "cms_prospects".INDIVIDUAL_DEMOGRAPHIC T2 on T0."indivi= dual_entity_proxy_id" =3D T2."individual_entity_proxy_id";



"Hash Join (cos= t=3D1417.48..21353422.52 rows=3D213620928 width=3D20)"
" = Hash Cond: ((t0.individual_entity_proxy_id)::numeric =3D t1.individual_ent= ity_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 individual_demographic t2 (cost=3D0.57..5013756.93 rows=3D26065206= 4 width=3D12)"
" -> Hash (cost=3D741.79..741.79 row= s=3D49579 width=3D8)"
" -> Seq Scan on t168003= 5748gcccqqdpmrblxp33_bkp t1 (cost=3D0.00..741.79 rows=3D49579 width=3D8)"<= br clear=3D"none">
--T0

CREATE TABLE cms_prospects.prospect
(
individual_entity_proxy_id bigint NOT NULL,
ho= usehold_entity_proxy_id bigint,
individual_personal_l= ink_sid bigint NOT NULL,
city_name character varying(= 100) COLLATE pg_catalog."default",
state_prov_cd char= acter varying(40) COLLATE pg_catalog."default",
pstl_= code character varying(40) COLLATE pg_catalog."default",
= npa integer,
nxx integer,
e= mail_domain character varying(400) COLLATE pg_catalog."default",
email_preference character varying(40) COLLATE pg_catalog."de= fault",
direct_mail_preference character varying(40) = COLLATE pg_catalog."default",
profane_wrd_ind charact= er(1) COLLATE pg_catalog."default",
tmo_ofnsv_name_in= d character(1) COLLATE pg_catalog."default",
census_b= lock_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_ad= dress_sid bigint,
telephone_number_sid bigint,
last_contacted_email_datetime timestamp without time zone,<= br clear=3D"none"> last_contacted_dm_datetime timestamp without time zon= e,
last_contacted_digital_datetime timestamp without = time zone,
last_contacted_anychannel_dttm timestamp w= ithout time zone,
hard_bounce_ind integer,
closest_store_site_id1 character varying(40) COLLATE pg_catal= og."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

CR= EATE TABLE cms_prospects.individual_demographic
(
individual_entity_proxy_id bigint NOT NULL,
cstmr_prspct_ind character varying(40) COLLATE pg_catalog."default",<= br clear=3D"none"> 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."de= fault",
tax_bnkrpt_dcsd_ind character(1) COLLATE pg_c= atalog."default",
underbank_rank_nbr integer,
hvy_txn_rank_nbr integer,
prominence_n= br 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_c= ode character varying(40) COLLATE pg_catalog."default",
= amazon_prm_propnsty_code character varying(40) COLLATE pg_catalog."defau= lt",
iphone_user_propnsty_code character varying(40) = COLLATE pg_catalog."default",
smrt_hm_devc_propnsty_c= ode 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) COLLA= TE pg_catalog."default",
msc_cncrt_propnsty_code char= acter 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) COLLAT= E pg_catalog."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,
md5_chk_sum charact= er varying(200) COLLATE pg_catalog."default",
deld_fr= om_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 (indiv= idual_entity_proxy_id)
);


Server config:
PostgreSQL v10.11= RAM: 380GB
vCore: 32
Shared_buffers: 65= G
Bwork_mem:104857kB
maintenance_work_m= em:256MB
effective_cache_size: 160GB
=
=
=
On Tuesday, June 16, 2020, 01:44:09 PM PDT, = Michael Lewis <mlewis@entrata.com> wrote:
=


On Tue, Jun 16, 2020 at 2:35 PM Nagaraj Raj <nagaraj.sf@yahoo.com&g= t; wrote:
I wrote = a simple query, and it is taking too long, not sure what is wrong in it, ev= en its not giving EXPLAIN ANALYZE.


More context is needed. Please review-

=
------=_Part_1893100_507263748.1592342158976--