Re: simple query running for ever

pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed

From: Nagaraj Raj <nagaraj.sf@yahoo.com>
To: Michael Lewis <mlewis@entrata.com>
Cc: Pgsql Performance <pgsql-performance@lists.postgresql.org>
Subject: Re: simple query running for ever
Date: Tue, 16 Jun 2020 21:13:13 +0000 (UTC)
Message-ID: <1746629545.1880709.1592341993870@mail.yahoo.com> (raw)
In-Reply-To: <CAHOFxGoKg7-9nWKiHiu8RwaD=DRJ9XygTcQ1J=6jQ4pL=zjUog@mail.gmail.com>
References: <574079062.1873527.1592339731136.ref@mail.yahoo.com>
	<574079062.1873527.1592339731136@mail.yahoo.com>
	<CAHOFxGoKg7-9nWKiHiu8RwaD=DRJ9XygTcQ1J=6jQ4pL=zjUog@mail.gmail.com>

 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_entity_proxy_id" = T1."individual_entity_proxy_id"
left join "cms_prospects".INDIVIDUAL_DEMOGRAPHIC T2 on T0."individual_entity_proxy_id" = T2."individual_entity_proxy_id";



"Hash Join (cost=1417.48..21353422.52 rows=213620928 width=20)"
" Hash Cond: ((t0.individual_entity_proxy_id)::numeric = t1.individual_entity_proxy_id)"
" -> Merge Left Join (cost=55.96..18147747.08 rows=213620928 width=20)"
" Merge Cond: (t0.individual_entity_proxy_id = t2.individual_entity_proxy_id)"
" -> Index Scan using pk_prospect on prospect t0 (cost=0.57..10831606.89 rows=213620928 width=16)"
" -> Index Only Scan using indxp_individual_demo_infrrd_hh_rank_nbr on individual_demographic t2 (cost=0.57..5013756.93 rows=260652064 width=12)"
" -> Hash (cost=741.79..741.79 rows=49579 width=8)"
" -> Seq Scan on t1680035748gcccqqdpmrblxp33_bkp t1 (cost=0.00..741.79 rows=49579 width=8)"

--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 

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."default",
 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_propnsty_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 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."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 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 <mlewis@entrata.com> wrote:  
 
 On Tue, Jun 16, 2020 at 2:35 PM Nagaraj Raj <nagaraj.sf@yahoo.com> 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.


More context is needed. Please review-
https://wiki.postgresql.org/wiki/Slow_Query_Questions

view thread (10+ messages)  latest in thread

Message-ID: <1746629545.1880709.1592341993870@mail.yahoo.com>
Permalink:  ../1746629545.1880709.1592341993870@mail.yahoo.com/
Also on:    postgresql.org/message-id/1746629545.1880709.1592341993870@mail.yahoo.com

 ·

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: nagaraj.sf@yahoo.com, mlewis@entrata.com, pgsql-performance@lists.postgresql.org
  Subject: Re: simple query running for ever
  In-Reply-To: <1746629545.1880709.1592341993870@mail.yahoo.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox