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
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@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@WLPRD:[101028]:LOG: duration: 101272.778 ms planAny suggestions on improvement would be helpful.