Query taking long time

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@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  plan
Any suggestions on improvement would be helpful.

[Index of Archives]     [Postgresql Home]     [Postgresql General]     [Postgresql Performance]     [Postgresql PHP]     [Postgresql Jobs]     [PHP Users]     [PHP Databases]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Yosemite Forum]

  Powered by Linux