Query taking long time

Hello,

One of the queries in our production environment is taking more than 100
seconds to execute, I did an analyze on the table and vacuum is also
running properly
on the included tables. Reindex is also done on the indexes which are used
in the query.

Server version : PostgreSQL 9.6 Running on Aurora:
RAM : 488GB
Vcpu : 64
work_mem = 4MB

Below is the query and EXPLAIN ANALYZE plan :

SELECT m.mem_id::bigint AS memberid,
m.lname AS memberlastname,
m.fname AS memberfirstname,
m.zip AS memberpostalcode,
m.site_id::bigint AS siteid,
cc.masked_card_num AS last4creditcarddigit,
m.addr AS memberaddress ,
m.pch_transaction_id::bigint AS purchasetransactionid ,
m.bill_cycle_start_date AS renewaldate ,
max(mc.cancel_request_date) AS cancelrequestdate ,
cast (
CASE
WHEN m.active_fl = 1 AND m.member_end_date IS
NULL AND m.join_date >= (aws_oracle_ext.sysdate() - (30::numeric
||'days')::interval)THEN 2 -- TRIAL
WHEN m.active_fl = 1 AND m.member_end_date IS
NULL THEN 1 -- ACTIVE RETAIL
WHEN m.active_fl = 0 AND m.member_end_date IS NOT
NULL THEN 3 -- CANCELLED
WHEN m.active_fl = 1 AND m.member_end_date IS NOT
NULL THEN 4 -- STOP BILLED
END AS smallint) AS status,
w.wbs_website_id::bigint AS websiteid,
w.wbs_website_name AS websitename,
w.wbs_url AS websiteurl
FROM wldbowner.member m
JOIN wlcommon.credit_card cc
ON m.mem_id = cc.mem_id
join dbowner.purchases p on m.pch_transaction_id = p.pch_transaction_id
join dbowner.websites w on p.pch_website_id = w.wbs_website_id
LEFT JOIN wldbowner.member_cancel mc
ON m.mem_id = mc.mem_id
WHERE m.site_id = i_siteid
AND cc.active_fl = 1
AND (
postcodes IS NULL
OR postcodes = ''
OR m.zip IN
(
SELECT t.m
FROM unnest(string_to_array(postcodes,
',')) AS t(m)
WHERE NULLIF(trim(t.m),'') IS NOT NULL))
AND (
fourccdigits IS NULL
OR fourccdigits = ''
OR cc.masked_card_num IN
(
SELECT t.m
FROM
unnest(string_to_array(fourccdigits, ',')) AS t(m)
WHERE NULLIF(trim(t.m),'') IS NOT NULL))
AND (
firstnames IS NULL
OR firstnames = ''
OR lower(unaccent_string((m.fname)::text)) IN
(
SELECT t.m
FROM
unnest(string_to_array(lower(unaccent_string((firstnames)::text)),
',')) AS t(m)
WHERE NULLIF(trim(t.m),'') IS NOT NULL))
AND (
lastnames IS NULL
OR lastnames = ''
OR lower(unaccent_string((m.lname)::text)) IN
(
SELECT t.m
FROM
unnest(string_to_array(lower(unaccent_string((lastnames)::text)),
',')) AS t(m)
WHERE NULLIF(trim(t.m),'') IS NOT NULL))
AND (
addresses IS NULL
OR addresses = ''
OR lower(unaccent_string((m.addr)::text)) IN
(
SELECT t.m
FROM
unnest(string_to_array(lower(unaccent_string((addresses)::text)),
',')) AS t(m)
WHERE NULLIF(trim(t.m),'') IS NOT NULL))
GROUP BY m.mem_id,
m.lname,
m.fname,
m.zip,
m.site_id,
cc.masked_card_num,
m.addr ,
m.pch_transaction_id ,
m.bill_cycle_start_date,
m.active_fl,
m.join_date,
m.member_end_date,
w.wbs_website_id,
w.wbs_website_name,
w.wbs_url

Query Plan:

GroupAggregate (cost=13355004.91..14903125.74 rows=5075806 width=177)
(actual time=168417.925..183827.954 rows=5875517 loops=1)

Group Key: m.mem_id, cc.masked_card_num, w.wbs_website_id
-> Sort (cost=13355004.91..13367694.42 rows=5075806 width=143)
(actual time=168417.893..170235.435 rows=6387371 loops=1)
Sort Key: m.mem_id, cc.masked_card_num, w.wbs_website_id
Sort Method: external sort Disk: 1055448kB
-> Hash Join (cost=8220721.71..12165117.80 rows=5075806
width=143) (actual time=99112.378..159723.105 rows=6387371 loops=1)
Hash Cond: (p.pch_website_id = w.wbs_website_id)
-> Merge Left Join (cost=8220576.40..12095180.16
rows=5075806 width=109) (actual time=99110.840..156760.716
rows=6387371 loops=1)
Merge Cond: (m.mem_id = mc.mem_id)
-> Merge Join (cost=8220262.69..10410364.45
rows=5075806 width=101) (actual time=72532.331..103794.615
rows=5875517 loops=1)
Merge Cond: (cc.mem_id = m.mem_id)
-> Index Scan using idx_comp_ccmemid_actfl
on credit_card cc (cost=0.56..1981727.82 rows=44773719 width=13)
(actual time=0.036..30556.453 rows=44842123 loops=1)
-> Materialize
(cost=8219892.16..8252287.96 rows=6479161 width=94) (actual
time=54487.984..60493.744 rows=5876541 loops=1)
-> Sort
(cost=8219892.16..8236090.06 rows=6479161 width=94) (actual
time=54487.981..59702.208 rows=5876541 loops=1)
Sort Key: m.mem_id
Sort Method: external merge
Disk: 698520kB
-> Hash Join
(cost=3937779.63..7107220.11 rows=6479161 width=94) (actual
time=11746.419..41827.367 rows=5876541 loops=1)
Hash Cond:
(p.pch_transaction_id = m.pch_transaction_id)
-> Seq Scan on purchases
p (cost=0.00..1951943.47 rows=50035147 width=12) (actual
time=0.003..12334.942 rows=47779746 loops=1)
-> Hash
(cost=3744085.23..3744085.23 rows=7400832 width=88) (actual
time=8890.322..8890.322 rows=5876541 loops=1)
Buckets: 32768
Batches: 256 Memory Usage: 3291kB
-> Index Scan
using idx_member_site_id on member m (cost=0.56..3744085.23
rows=7400832 width=88) (actual time=0.040..6077.162 rows=5876541
loops=1)
Index Cond:
(site_id = '25200'::numeric)
-> Index Scan using pk_member_cancel on
member_cancel mc (cost=0.56..1523326.60 rows=43918136 width=14)
(actual time=0.032..41383.241 rows=44478735 loops=1)
-> Hash (cost=98.47..98.47 rows=3747 width=40)
(actual time=1.521..1.521 rows=3790 loops=1)
Buckets: 4096 Batches: 1 Memory Usage: 298kB
-> Seq Scan on websites w (cost=0.00..98.47
rows=3747 width=40) (actual time=0.004..0.724 rows=3790 loops=1)
2021-12-27 14:07:47
UTC:10.203.19.151(59169):wl1appusr(at)WLPRD:[83512]:CONTEXT: PL/pgSQL
function wlmember."pkg_ivr_member$sp_get_members_generic"(numeric,text,text,text,text,text)
line 3 at RETURN QUERY
2021-12-27 14:07:49
UTC:10.203.6.171(28983):wl1appusr(at)WLPRD:[101028]:LOG: duration:
101272.778 ms plan

Any suggestions on improvement would be helpful.