Performance issue - Mailing list pgsql-performance

Hello 


My PostgreSQL server 10.11 running on windows which are running very slow. DB has two tables with ~200Mil records in
each.user queries are very slow even explain analyze also taking a longer.
 


Could you please help me to tune this query and any suggestions to improve system performance?

Table structures: 

Table1:

-- Records 213621151

CREATE TABLE test1
(
    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,
    shared_email_with_customer_ind character(1) COLLATE pg_catalog."default",
    shared_paddr_with_customer_ind character(1) COLLATE pg_catalog."default",
    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,
    src_sys_id integer NOT NULL,
    insrt_prcs_id bigint,
    updt_prcs_id bigint,
    stg_prcs_id bigint,
    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_i_entity_proxy_id PRIMARY KEY (individual_entity_proxy_id)

);
CREATE INDEX indx_prospect_indv_entty_id
    ON test1 USING btree
    (individual_entity_proxy_id )


Table 2:
-- Records 260652202 

CREATE TABLE test2
(
    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",
    dish_sbscrbr_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",
    sml_busi_ownr_propnsty_code character varying(40) COLLATE pg_catalog."default",
    tv_internet_bndl_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",
 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",
    src_sys_id integer NOT NULL,
    insrt_prcs_id bigint,
    updt_prcs_id bigint,
    stg_prcs_id bigint,
    load_dttm timestamp without time zone NOT NULL,
    updt_dttm timestamp without time zone,
    deld_from_src_ind character(1) COLLATE pg_catalog."default" NOT NULL,
    orphan_ind character(1) COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT pk_entity_proxy_id PRIMARY KEY (individual_entity_proxy_id)
);


User query: 

explain analyze select COUNT(*) as "DII_1"
  from ( select distinct table0."individual_entity_proxy_id" as "INDIVIDUAL_ENTITY_PROXY_ID"
                                from test1 table0
                                inner join test2 table1
                                on table0."individual_entity_proxy_id" = table1."individual_entity_proxy_id"
                                where ((table0."shared_paddr_with_customer_ind" = 'N')
                                                   and (table0."profane_wrd_ind" = 'N')
                                                   and (table0."tmo_ofnsv_name_ind" = 'N')
                                                   and ((table0."last_contacted_dm_datetime" is null)
                                                   or (table0."last_contacted_dm_datetime" <  TIMESTAMP '2020-03-15
0:00:00.000000'))
                                                   and (table0."has_individual_address" = 'Y')
                                                   and (table0."has_last_name" = 'Y')
                                                   and (table0."has_first_name" = 'Y')
                                                   and (table0."direct_mail_preference" is null))
                                                   and ((table1."tax_bnkrpt_dcsd_ind" = 'N')
                                                  and (table1."cstmr_prspct_ind" = 'Prospect')
                                                  and (table1."govt_prison_ind" = 'N')) ) TXT_1;

Explain Analyze :

"Aggregate  (cost=5345632.91..5345632.92 rows=1 width=8) (actual time=442688.462..442688.462 rows=1 loops=1)"
"  ->  Unique  (cost=150.13..4943749.39 rows=32150682 width=8) (actual time=0.022..439964.214 rows=32368180 loops=1)"
"        ->  Merge Join  (cost=150.13..4863372.68 rows=32150682 width=8) (actual time=0.021..435818.276 rows=32368180
loops=1)"
"              Merge Cond: (table0.individual_entity_proxy_id = table1.individual_entity_proxy_id)"
"              ->  Index Scan using indx_prospect_indv_entty_id on test1 table0  (cost=0.56..2493461.92 rows=32233405
width=8)(actual time=0.011..63009.551 rows=32368180 loops=1)"
 
"                    Filter: ((direct_mail_preference IS NULL) AND ((last_contacted_dm_datetime IS NULL) OR
(last_contacted_dm_datetime< '2020-03-15 00:00:00'::timestamp without time zone)) AND (shared_paddr_with_customer_ind =
'N'::bpchar)AND (profane_wrd_ind = 'N'::bpchar) AND (tmo_ofnsv_name_ind = 'N'::bpchar) AND (has_individual_address =
'Y'::bpchar)AND (has_last_name = 'Y'::bpchar) AND (has_first_name = 'Y'::bpchar))"
 
"                    Rows Removed by Filter: 7709177"
"              ->  Index Scan using pk_entity_proxy_id on test2 table1  (cost=0.56..1867677.94 rows=40071417 width=8)
(actualtime=0.008..363534.437 rows=40077727 loops=1)"
 
"                    Filter: ((tax_bnkrpt_dcsd_ind = 'N'::bpchar) AND (govt_prison_ind = 'N'::bpchar) AND
((cstmr_prspct_ind)::text= 'Prospect'::text))"
 
"                    Rows Removed by Filter: 94756"
"Planning time: 0.400 ms"
"Execution time: 442688.523 ms"

Server config:

PostgreSQL v10.11
RAM: 380GB
vCore: 32
Shared_buffers: 65GB
work_mem:104857kB
maintenance_work_mem:256MB
effective_cache_size: 160GB




https://dba.stackexchange.com/questions/269138/postgresql-server-running-very-slow-at-minimal-work-load


Thanks,
Raj