Hi,
I have a query performance issue, it takes a long time, and not even getting
explain analyze the output. this query joining on 3 tables which have around a
- 176223509
b - 286887780
c - 214219514
explainselect Count(a."individual_entity_proxy_id")from "prospect" ainner join
"individual_demographic" bon a."individual_entity_proxy_id" =
b."individual_entity_proxy_id"inner join "household_demographic" c on
a."household_entity_proxy_id" = c."household_entity_proxy_id"where
(((a."last_contacted_anychannel_dttm" is null) or
(a."last_contacted_anychannel_dttm" < TIMESTAMP '2020-11-23 0:00:00.000000'))
and (a."shared_paddr_with_customer_ind" = 'N')
and (a."profane_wrd_ind" = 'N') and (a."tmo_ofnsv_name_ind" =
'N') and (a."has_individual_address" = 'Y')
and (a."has_last_name" = 'Y') and (a."has_first_name" = 'Y'))
and ((b."tax_bnkrpt_dcsd_ind" = 'N') and (b."govt_prison_ind" =
'N') and (b."cstmr_prspct_ind" = 'Prospect')) and ((
c."hspnc_lang_prfrnc_cval" in ('B', 'E', 'X') ) or
(c."hspnc_lang_prfrnc_cval" is null));-- Explain output
"Finalize Aggregate (cost=32813309.28..32813309.29 rows=1 width=8)"" ->
Gather (cost=32813308.45..32813309.26 rows=8 width=8)"" Workers
Planned: 8"" -> Partial Aggregate (cost=32812308.45..32812308.46
rows=1 width=8)"" -> Merge Join (cost=23870130.00..32759932.46
rows=20950395 width=8)"" Merge Cond:
(a.individual_entity_proxy_id = b.individual_entity_proxy_id)""
-> Sort (cost=23870127.96..23922503.94 rows=20950395 width=8)""
Sort Key: a.individual_entity_proxy_id""
-> Hash Join (cost=13533600.42..21322510.26 rows=20950395 width=8)""
Hash Cond: (a.household_entity_proxy_id =
c.household_entity_proxy_id)"" -> Parallel Seq
Scan on prospect a (cost=0.00..6863735.60 rows=22171902 width=16)""
Filter: (((last_contacted_anychannel_dttm IS NULL) OR
(last_contacted_anychannel_dttm < '2020-11-23 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))"" -> Hash
(cost=10801715.18..10801715.18 rows=166514899 width=8)""
-> Seq Scan on household_demographic c (cost=0.00..10801715.18
rows=166514899 width=8)"" Filter:
(((hspnc_lang_prfrnc_cval)::text = ANY ('{B,E,X}'::text[])) OR
(hspnc_lang_prfrnc_cval IS NULL))"" -> Index Only Scan
using indx_individual_demographic_prxyid_taxind_prspctind_prsnind on
individual_demographic b (cost=0.57..8019347.13 rows=286887776 width=8)""
Index Cond: ((tax_bnkrpt_dcsd_ind = 'N'::bpchar) AND
(cstmr_prspct_ind = 'Prospect'::text) AND (govt_prison_ind = 'N'::bpchar))"
Tables ddl are attached in dbfiddle -- Postgres 11 | db<>fiddle
|
|
| |
Postgres 11 | db<>fiddle
Free online SQL environment for experimenting and sharing.
|
|
|
Server configuration is: Version: 10.11RAM - 320GBvCPU - 32
"maintenance_work_mem" 256MB"work_mem" 1GB"shared_buffers" 64GB
Any suggestions?
Thanks,Rj
