Re: simple query running for ever - Mailing list pgsql-performance

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