Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kKFVN-0005L1-Cd for pgsql-admin@arkaria.postgresql.org; Mon, 21 Sep 2020 06:40:53 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kKFVL-0007Eg-9T for pgsql-admin@arkaria.postgresql.org; Mon, 21 Sep 2020 06:40:51 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kKFVK-0007EY-Sz for pgsql-admin@lists.postgresql.org; Mon, 21 Sep 2020 06:40:51 +0000 Received: from mail-pg1-x52d.google.com ([2607:f8b0:4864:20::52d]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1kKFVD-0001V5-O6 for pgsql-admin@lists.postgresql.org; Mon, 21 Sep 2020 06:40:50 +0000 Received: by mail-pg1-x52d.google.com with SMTP id k14so8160898pgi.9 for ; Sun, 20 Sep 2020 23:40:43 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=mime-version:subject:from:in-reply-to:date:cc :content-transfer-encoding:message-id:references:to; bh=9FkhRtYE2tE8zWGgXq/7t5Q/5UL4592hK8LbBIg4Aj0=; b=pJ8kSTQHW5r8q3EbyuNuCQ03m754UhbLaarCiOkZFfKJA92XWKEFQyDgaVT8e2mNg8 h+jCdZed3YQH/hTW8WJ71adD/DL2m01Uzhnw8tzW5ELFa8FumKCIVmmQU+73i32devQ8 6Bf2s0gqbICgnxysCX2BABZAhEjhxQpYXXYqwLuUd7wodIuBRxZxQjaKK06P55u0A5nj VMLVr1v2qB1XZfNlcvQtnAea9bW6B7wF1lrNhcEdzKB7f0PVK/i7nWZesE2F6VVpoU/Z tSBUv/1Rc9ZK7OfJQQQMnS5N2szUoPQUGN1zgb1bSze4yGYskkfxVp1dBgKj5+X1H+J5 oihg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:subject:from:in-reply-to:date:cc :content-transfer-encoding:message-id:references:to; bh=9FkhRtYE2tE8zWGgXq/7t5Q/5UL4592hK8LbBIg4Aj0=; b=tgCGX04dbSoISJp0fRSDUm/FR8STzGhuX74RBKhHhXSJstHZreqcAc3nt8w8Btpuar rbG0e5Qu5i2+7E8SbV1J60NwNKD5elphfo6/RiK+YtHm4ZrG5rulM1jIet3OJlh0gTNi iQjkVsQtok7RYlTcX4quepw4QmWfkUNPS3f7uN7WCJDyEXfU9VrJyoClZ7wC617PzIKr Y8PJjokaE9qNtadOXmWdsWqGR36zOIe9UVEsqSCccDQmwGGcc1BUOf80X8e1FBLYmcF7 nO2UwIj8DbYX6yIYNTBrFFHwXSZb/Fzc3THhntSKQd4Qp5j7NjPJqZIJXQ6kTUZJLCsB Ihmg== X-Gm-Message-State: AOAM530ZSF1qmjZ5A35GM7SxOvFCrW1LlW1tTQOpnE/W58N9KhdDJBDp Hvp+ximT2acGLl18UAq7DUEiO/tK+4N9Vw== X-Google-Smtp-Source: ABdhPJwBiMy9S88TDIUt38M2zBIGjcKJW3rZE85n6Z1ua4jYulw3W5UlE56fmTYSFBvspCwgDkMBPw== X-Received: by 2002:a17:902:9e08:b029:d0:8a6a:d5e8 with SMTP id d8-20020a1709029e08b02900d08a6ad5e8mr43567188plq.0.1600670441140; Sun, 20 Sep 2020 23:40:41 -0700 (PDT) Received: from ?IPv6:2001:8003:5894:ed01:6108:7253:5730:dcd1? ([2001:8003:5894:ed01:6108:7253:5730:dcd1]) by smtp.gmail.com with ESMTPSA id x3sm10598651pgg.54.2020.09.20.23.40.39 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Sun, 20 Sep 2020 23:40:40 -0700 (PDT) Content-Type: multipart/alternative; boundary=Apple-Mail-C5A772D3-0AC8-44F7-9F2B-9C38AF532E8B Mime-Version: 1.0 (1.0) Subject: Re: Query taking seq scan on a table From: Jim Gmail X-Mailer: iPhone Mail (16G201) In-Reply-To: Date: Mon, 21 Sep 2020 16:40:37 +1000 Cc: Avinash Kumar , dbatoCloud Solution , pgsql-admin@lists.postgresql.org Content-Transfer-Encoding: 7bit Message-Id: References: To: Shrikant Bhende List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --Apple-Mail-C5A772D3-0AC8-44F7-9F2B-9C38AF532E8B Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: quoted-printable If you can , avoid using DISTINCT and the sorting this invokes Sent from my iPhone > On 21 Sep 2020, at 15:04, Shrikant Bhende w= rote: >=20 > Hello,=20 >=20 > Configuration of instance :=20 > Type : Amazon Aurora=20 > DB instance : db.r4Large > Vcpu : 2=20 > ECU : 7=20 > Total Memory : 15.25 GB=20 >=20 > Shared buffers are set to 75% of total RAM as per the AWS recommendations a= nd random_page_cost is already altered to 1 and shared the relevant Explain a= nalyze plan for the same. >=20 > Thanks=20 >=20 >> On Mon, Sep 21, 2020 at 10:21 AM Shrikant Bhende wrote: >> Hello,=20 >>=20 >> Below is the explain analyze after setting random_page_cost to 1; >>=20 >> Limit (cost=3D11638306.20..11638311.56 rows=3D102 width=3D209) (actual t= ime=3D73231.121..73231.122 rows=3D1 loops=3D1) >> -> Unique (cost=3D11638306.20..11638329.83 rows=3D450 width=3D209) (a= ctual time=3D73231.120..73231.120 rows=3D1 loops=3D1) >> -> Sort (cost=3D11638306.20..11638307.33 rows=3D450 width=3D209= ) (actual time=3D73231.120..73231.120 rows=3D1 loops=3D1) >> Sort Key: m.mem_id, m.member_type_id, m.lname, m.fname, m.e= mail, m.addr, m.zip, m.join_date, p.product_desc, b.product_name, c.cln_name= , m.member_end_date, (CASE mc2.member_cancel_type_id WHEN '2'::numeric THEN m= c2.member_cancel_type_id ELSE mc1.member_cancel_type_id END), m.active_fl, (= CASE WHEN (ca.mem_id IS NULL) THEN NULL::integer ELSE 66 END), (CASE WHEN (g= mc.mem_id IS NOT NULL) THEN 1 ELSE 0 END), (CASE WHEN (ccpa.mem_id IS NOT NU= LL) THEN 1 ELSE 0 END), gmc.declassification_start_date, gmc.anonymization_d= ate, gmc.request_source >> Sort Method: quicksort Memory: 25kB >> -> Nested Loop Left Join (cost=3D1.46..11638286.37 rows=3D= 450 width=3D209) (actual time=3D73231.063..73231.098 rows=3D1 loops=3D1) >> Join Filter: (m.mem_id =3D ccpa.mem_id) >> -> Nested Loop Left Join (cost=3D1.46..11638267.69 r= ows=3D450 width=3D213) (actual time=3D73231.053..73231.088 rows=3D1 loops=3D= 1) >> Join Filter: (m.mem_id =3D gmc.mem_id) >> -> Nested Loop Left Join (cost=3D1.46..116372= 36.72 rows=3D450 width=3D167) (actual time=3D73231.048..73231.083 rows=3D1 l= oops=3D1) >> -> Nested Loop (cost=3D1.02..11636127.2= 2 rows=3D450 width=3D162) (actual time=3D73231.037..73231.071 rows=3D1 loops= =3D1) >> Join Filter: (s.sol_template_id =3D= st.sol_template_id) >> Rows Removed by Join Filter: 5565 >> -> Nested Loop (cost=3D1.02..1159= 8155.50 rows=3D515 width=3D167) (actual time=3D73228.007..73228.038 rows=3D1= loops=3D1) >> -> Nested Loop Left Join (c= ost=3D0.73..11597971.55 rows=3D515 width=3D174) (actual time=3D73227.994..73= 228.024 rows=3D1 loops=3D1) >> -> Nested Loop Left Jo= in (cost=3D0.29..11596701.79 rows=3D515 width=3D169) (actual time=3D73227.9= 69..73227.999 rows=3D1 loops=3D1) >> Join Filter: (ca.= mem_id =3D m.mem_id) >> -> Nested Loop (= cost=3D0.29..11582996.41 rows=3D515 width=3D151) (actual time=3D73227.962..7= 3227.991 rows=3D1 loops=3D1) >> Join Filter= : (websites.wbs_client_id =3D c.cln_client_id) >> Rows Remove= d by Join Filter: 2343 >> -> Seq Sca= n on clients c (cost=3D0.00..46.75 rows=3D2375 width=3D21) (actual time=3D0= .006..0.184 rows=3D2344 loops=3D1) >> -> Materia= lize (cost=3D0.29..11564604.07 rows=3D515 width=3D142) (actual time=3D31.24= 0..31.240 rows=3D1 loops=3D2344) >> -> N= ested Loop (cost=3D0.29..11564601.50 rows=3D515 width=3D142) (actual time=3D= 73226.808..73226.836 rows=3D1 loops=3D1) >> = Join Filter: (campaigns.cam_website_id =3D websites.wbs_website_id) >> = Rows Removed by Join Filter: 3721 >> = -> Seq Scan on websites (cost=3D0.00..97.68 rows=3D3768 width=3D12) (actu= al time=3D0.002..0.350 rows=3D3722 loops=3D1) >> = -> Materialize (cost=3D0.29..11535397.31 rows=3D515 width=3D142) (actual t= ime=3D11.071..19.674 rows=3D1 loops=3D3722) >> = -> Nested Loop (cost=3D0.29..11535394.73 rows=3D515 width=3D142) (a= ctual time=3D41204.676..73224.912 rows=3D1 loops=3D1) >> = -> Nested Loop (cost=3D0.00..11534442.93 rows=3D515 width=3D1= 30) (actual time=3D41204.658..73224.892 rows=3D1 loops=3D1) >> = Join Filter: (m.brn_id =3D b.brn_id) >> = Rows Removed by Join Filter: 202 >> = -> Nested Loop (cost=3D0.00..11532858.96 rows=3D515 wid= th=3D116) (actual time=3D41204.622..73224.764 rows=3D1 loops=3D1) >> = Join Filter: (m.product_id =3D p.product_id) >> = Rows Removed by Join Filter: 114 >> = -> Seq Scan on member m (cost=3D0.00..11531974.88= rows=3D515 width=3D100) (actual time=3D41204.577..73224.687 rows=3D1 loops=3D= 1) >> = Filter: ((lower(unaccent_string((lname)::text= )) ~~ 'info%'::text) AND (lower(unaccent_string((fname)::text)) ~~ 'travel%'= ::text)) >> = Rows Removed by Filter: 20595444 >> = -> Materialize (cost=3D0.00..3.71 rows=3D114 widt= h=3D27) (actual time=3D0.012..0.054 rows=3D115 loops=3D1) >> = -> Seq Scan on product p (cost=3D0.00..3.14= rows=3D114 width=3D27) (actual time=3D0.006..0.021 rows=3D115 loops=3D1) >> = -> Materialize (cost=3D0.00..24.03 rows=3D202 width=3D2= 6) (actual time=3D0.006..0.088 rows=3D203 loops=3D1) >> = -> Seq Scan on brand b (cost=3D0.00..23.02 rows=3D= 202 width=3D26) (actual time=3D0.002..0.054 rows=3D203 loops=3D1) >> = -> Index Scan using pk_campaigns on campaigns (cost=3D0.29..1= .84 rows=3D1 width=3D12) (actual time=3D0.013..0.014 rows=3D1 loops=3D1) >> = Index Cond: (cam_campaign_id =3D m.sol_id) >> -> Materialize (= cost=3D0.00..36.55 rows=3D1770 width=3D18) (actual time=3D0.007..0.007 rows=3D= 0 loops=3D1) >> -> Seq Sca= n on iot_2009_ca_member ca (cost=3D0.00..27.70 rows=3D1770 width=3D18) (act= ual time=3D0.002..0.002 rows=3D0 loops=3D1) >> -> Index Scan using pk= _member_cancel on member_cancel mc1 (cost=3D0.44..2.46 rows=3D1 width=3D11)= (actual time=3D0.020..0.020 rows=3D0 loops=3D1) >> Index Cond: (m.me= m_id =3D mem_id) >> Filter: (member_c= ancel_type_id =3D '1'::numeric) >> -> Index Scan using pk_solic= itation on solicitation s (cost=3D0.29..0.35 rows=3D1 width=3D11) (actual t= ime=3D0.009..0.010 rows=3D1 loops=3D1) >> Index Cond: (sol_id =3D= campaigns.cam_campaign_id) >> -> Materialize (cost=3D0.00..285.= 92 rows=3D4880 width=3D5) (actual time=3D0.018..2.349 rows=3D5566 loops=3D1)= >> -> Seq Scan on solicitation_= template st (cost=3D0.00..261.52 rows=3D4880 width=3D5) (actual time=3D0.00= 9..1.547 rows=3D5566 loops=3D1) >> Filter: (country_id =3D= ANY ('{1,2,3,121,121,4,5,6,7,8,9,10,11,12,13,14}'::numeric[])) >> -> Index Scan using pk_member_cancel on m= ember_cancel mc2 (cost=3D0.44..2.46 rows=3D1 width=3D11) (actual time=3D0.0= 06..0.006 rows=3D0 loops=3D1) >> Index Cond: (m.mem_id =3D mem_id) >> Filter: (member_cancel_type_id =3D '= 2'::numeric) >> -> Materialize (cost=3D0.00..25.59 rows=3D149= width=3D46) (actual time=3D0.004..0.004 rows=3D0 loops=3D1) >> -> Seq Scan on gdpr_member_classificatio= n gmc (cost=3D0.00..24.85 rows=3D149 width=3D46) (actual time=3D0.001..0.00= 1 rows=3D0 loops=3D1) >> Filter: ((declassification_start_da= te IS NOT NULL) AND (current_fl =3D 1) AND (date_trunc('day'::text, declassi= fication_start_date) <=3D to_date('9/17/2020'::text, 'MM/DD/YYYY'::text)) AN= D ((declassification_end_date IS NULL) OR (date_trunc('day'::text, declassif= ication_end_date) > to_date('9/17/2020'::text, 'MM/DD/YYYY'::text)))) >> -> Materialize (cost=3D0.00..10.81 rows=3D1 width=3D= 18) (actual time=3D0.003..0.003 rows=3D0 loops=3D1) >> -> Seq Scan on ccpa_member_classification ccpa= (cost=3D0.00..10.80 rows=3D1 width=3D18) (actual time=3D0.003..0.003 rows=3D= 0 loops=3D1) >> Filter: ((anonymization_date IS NOT NULL)= AND (current_fl =3D 1) AND (date_trunc('day'::text, anonymization_date) <=3D= to_date('9/17/2020'::text, 'MM/DD/YYYY'::text))) >> Planning time: 7.622 ms >> Execution time: 73231.463 ms >>=20 >>=20 >> Also I have tried to add a GIN index for better text search as below,=20 >>=20 >> CREATE INDEX idx_fnmae_lname_gin_composite ON wldbowner.member USING gin=20= >> (lower((((fname)::text || ' '::text) || (lname)::text)) rdsadmin.gin_trgm= _ops); >>=20 >>=20 >> Thanks=20 >>=20 >>> On Sun, Sep 20, 2020 at 7:25 PM Avinash Kumar wrote: >>>=20 >>>=20 >>>> On Sun., Sep. 20, 2020, 10:48 a.m. Shrikant Bhende, wrote: >>>> Hello, >>>>=20 >>>> show seq_page_cost; 1 >>>>=20 >>>> show random_page_cost; 4 >>>=20 >>> Set this to 1 and rerun the explain analyze and let us know what you see= .=20 >>> You are using an SSD and the value of 4 should not be apt.=20 >>>>=20 >>>> storage type : SSD=20 >>>>=20 >>>> Table size : 39 GB=20 >>>>=20 >>>> There are no columns added, also I haven't found anything where we need= to update the data for older rows. >>>>=20 >>>> Thanks=20 >>>>=20 >>>>> On Sun, Sep 20, 2020 at 6:55 PM Avinash Kumar wrote: >>>>>=20 >>>>>=20 >>>>>> On Sun., Sep. 20, 2020, 10:19 a.m. Shrikant Bhende, wrote: >>>>>> Hello all,=20 >>>>>>=20 >>>>>> I am facing issues with one of the queries running on Amazon Aurora (= PG VERSION 9.6) which is taking more than a minute to complete. As per the i= nitial investigation I assume that one part of the query is taking a sequent= ial scan on a table("wldbowner.member") which is consuming most of the execu= tion time. I tried to use index (Btree and GIN ) as well but none of them we= re helping to get the query to run faster. Attached is the query along with t= he explain analyze of the same, any help on the same would be appreciated.=20= >>>>>>=20 >>>>>> NOTE : I have done the vacuum on the table already to get rid of bloa= t issues. >>>>>> Reindex and test with more work mem is also helping. >>>>>=20 >>>>> Just wanted to clarify one thing before looking at the query.=20 >>>>>=20 >>>>> What is random_page_cost set to ?=20 >>>>>=20 >>>>>>=20 >>>>>> Thanks and regards >>>>>>=20 >>>>>>=20 --Apple-Mail-C5A772D3-0AC8-44F7-9F2B-9C38AF532E8B Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable If you can , avoid using DISTINCT and the s= orting this invokes

Sent f= rom my iPhone

On 21 Sep 2020, at 15:04, Shrikant B= hende <shrikantpostgresql= @gmail.com> wrote:

Hello, 

Configuration of inst= ance : 
Type : Amazon Aurora 
DB instance : db= .r4Large
Vcpu :  2 
ECU : 7 
Total Memory : 1= 5.25 GB 

Shared buffers are set to 75% of tota= l RAM as per the AWS recommendations and random_page_cost is already altered= to 1 and shared the relevant Explain analyze plan for the same.
<= br>
Thanks 

On Mon, Sep 21, 2020 at 10:21 AM Shrikant Bhe= nde <shrikantpostgresql@g= mail.com> wrote:
Hello, 

Below is the explain a= nalyze after setting random_page_cost to 1;

Limit &= nbsp;(cost=3D11638306.20..11638311.56 rows=3D102 width=3D209) (actual time=3D= 73231.121..73231.122 rows=3D1 loops=3D1)
  ->  Unique  = (cost=3D11638306.20..11638329.83 rows=3D450 width=3D209) (actual time=3D7323= 1.120..73231.120 rows=3D1 loops=3D1)
        -> &n= bsp;Sort  (cost=3D11638306.20..11638307.33 rows=3D450 width=3D209) (act= ual time=3D73231.120..73231.120 rows=3D1 loops=3D1)
      &= nbsp;       Sort Key: m.mem_id, m.member_type_id, m.lname, m.= fname, m.email, m.addr, m.zip, m.join_date, p.product_desc, b.product_name, c= .cln_name, m.member_end_date, (CASE mc2.member_cancel_type_id WHEN '2'::nume= ric THEN mc2.member_cancel_type_id ELSE mc1.member_cancel_type_id END), m.ac= tive_fl, (CASE WHEN (ca.mem_id IS NULL) THEN NULL::integer ELSE 66 END), (CA= SE WHEN (gmc.mem_id IS NOT NULL) THEN 1 ELSE 0 END), (CASE WHEN (ccpa.mem_id= IS NOT NULL) THEN 1 ELSE 0 END), gmc.declassification_start_date, gmc.anony= mization_date, gmc.request_source
          &nbs= p;   Sort Method: quicksort  Memory: 25kB
      &= nbsp;       ->  Nested Loop Left Join  (cost=3D1= .46..11638286.37 rows=3D450 width=3D209) (actual time=3D73231.063..73231.098= rows=3D1 loops=3D1)
              &nb= sp;     Join Filter: (m.mem_id =3D ccpa.mem_id)
    &= nbsp;               ->  Nested Lo= op Left Join  (cost=3D1.46..11638267.69 rows=3D450 width=3D213) (actual= time=3D73231.053..73231.088 rows=3D1 loops=3D1)
      &nb= sp;                   Join Filt= er: (m.mem_id =3D gmc.mem_id)
            &= nbsp;             ->  Nested Loop Left= Join  (cost=3D1.46..11637236.72 rows=3D450 width=3D167) (actual time=3D= 73231.048..73231.083 rows=3D1 loops=3D1)
        &nbs= p;                     &nb= sp; ->  Nested Loop  (cost=3D1.02..11636127.22 rows=3D450 width= =3D162) (actual time=3D73231.037..73231.071 rows=3D1 loops=3D1)
  &n= bsp;                     &= nbsp;             Join Filter: (s.sol_template= _id =3D st.sol_template_id)
            &nb= sp;                     &n= bsp;   Rows Removed by Join Filter: 5565
       =                      = ;         ->  Nested Loop  (cost=3D1.02..11= 598155.50 rows=3D515 width=3D167) (actual time=3D73228.007..73228.038 rows=3D= 1 loops=3D1)
                &nbs= p;                     &nb= sp;     ->  Nested Loop Left Join  (cost=3D0.73..1159= 7971.55 rows=3D515 width=3D174) (actual time=3D73227.994..73228.024 rows=3D1= loops=3D1)
                 = ;                     &nbs= p;           ->  Nested Loop Left Join &nbs= p;(cost=3D0.29..11596701.79 rows=3D515 width=3D169) (actual time=3D73227.969= ..73227.999 rows=3D1 loops=3D1)
           =                      = ;                     &nbs= p; Join Filter: (ca.mem_id =3D m.mem_id)
        &nbs= p;                     &nb= sp;                     &n= bsp;   ->  Nested Loop  (cost=3D0.29..11582996.41 rows=3D5= 15 width=3D151) (actual time=3D73227.962..73227.991 rows=3D1 loops=3D1)
&= nbsp;                     &= nbsp;                     &= nbsp;                 Join Filter: (= websites.wbs_client_id =3D c.cln_client_id)
        &= nbsp;                     &= nbsp;                     &= nbsp;         Rows Removed by Join Filter: 2343
 = ;                     &nbs= p;                     &nb= sp;                 ->  Seq S= can on clients c  (cost=3D0.00..46.75 rows=3D2375 width=3D21) (actual t= ime=3D0.006..0.184 rows=3D2344 loops=3D1)
        &nb= sp;                     &n= bsp;                     &= nbsp;         ->  Materialize  (cost=3D0.29= ..11564604.07 rows=3D515 width=3D142) (actual time=3D31.240..31.240 rows=3D1= loops=3D2344)
                &n= bsp;                     &= nbsp;                     &= nbsp;       ->  Nested Loop  (cost=3D0.29..11564= 601.50 rows=3D515 width=3D142) (actual time=3D73226.808..73226.836 rows=3D1 l= oops=3D1)
                  &= nbsp;                     &= nbsp;                     &= nbsp;           Join Filter: (campaigns.cam_website= _id =3D websites.wbs_website_id)
           = ;                     &nbs= p;                     &nb= sp;                   Rows Remo= ved by Join Filter: 3721
             =                      = ;                     &nbs= p;                 ->  Seq S= can on websites  (cost=3D0.00..97.68 rows=3D3768 width=3D12) (actual ti= me=3D0.002..0.350 rows=3D3722 loops=3D1)
        &nbs= p;                     &nb= sp;                     &n= bsp;                     -= >  Materialize  (cost=3D0.29..11535397.31 rows=3D515 width=3D14= 2) (actual time=3D11.071..19.674 rows=3D1 loops=3D3722)
    &nb= sp;                     &n= bsp;                     &= nbsp;                     &= nbsp;         ->  Nested Loop  (cost=3D0.29= ..11535394.73 rows=3D515 width=3D142) (actual time=3D41204.676..73224.912 ro= ws=3D1 loops=3D1)
               =                      = ;                     &nbs= p;                     &nb= sp;     ->  Nested Loop  (cost=3D0.00..11534442.93 ro= ws=3D515 width=3D130) (actual time=3D41204.658..73224.892 rows=3D1 loops=3D1= )
                    &= nbsp;                     &= nbsp;                     &= nbsp;                     &= nbsp;     Join Filter: (m.brn_id =3D b.brn_id)
    &n= bsp;                     &= nbsp;                     &= nbsp;                     &= nbsp;                     R= ows Removed by Join Filter: 202
           =                      = ;                     &nbs= p;                     &nb= sp;               ->  Nested Loop=  (cost=3D0.00..11532858.96 rows=3D515 width=3D116) (actual time=3D4120= 4.622..73224.764 rows=3D1 loops=3D1)
          &= nbsp;                     &= nbsp;                     &= nbsp;                     &= nbsp;                     J= oin Filter: (m.product_id =3D p.product_id)
        &= nbsp;                     &= nbsp;                     &= nbsp;                     &= nbsp;                     &= nbsp; Rows Removed by Join Filter: 114
         =                      = ;                     &nbs= p;                     &nb= sp;                     &n= bsp; ->  Seq Scan on member m  (cost=3D0.00..11531974.88 rows=3D= 515 width=3D100) (actual time=3D41204.577..73224.687 rows=3D1 loops=3D1)
=                      =                      = ;                     &nbs= p;                     &nb= sp;                 Filter: ((lower(= unaccent_string((lname)::text)) ~~ 'info%'::text) AND (lower(unaccent_string= ((fname)::text)) ~~ 'travel%'::text))
          &= nbsp;                     &= nbsp;                     &= nbsp;                     &= nbsp;                     &= nbsp;     Rows Removed by Filter: 20595444
     =                      = ;                     &nbs= p;                     &nb= sp;                     &n= bsp;     ->  Materialize  (cost=3D0.00..3.71 rows=3D1= 14 width=3D27) (actual time=3D0.012..0.054 rows=3D115 loops=3D1)
  &= nbsp;                     &= nbsp;                     &= nbsp;                     &= nbsp;                     &= nbsp;             ->  Seq Scan on prod= uct p  (cost=3D0.00..3.14 rows=3D114 width=3D27) (actual time=3D0.006..= 0.021 rows=3D115 loops=3D1)
            &nb= sp;                     &n= bsp;                     &= nbsp;                     &= nbsp;             ->  Materialize &nbs= p;(cost=3D0.00..24.03 rows=3D202 width=3D26) (actual time=3D0.006..0.088 row= s=3D203 loops=3D1)
               = ;                     &nbs= p;                     &nb= sp;                     &n= bsp;                 ->  Seq= Scan on brand b  (cost=3D0.00..23.02 rows=3D202 width=3D26) (actual ti= me=3D0.002..0.054 rows=3D203 loops=3D1)
         = ;                     &nbs= p;                     &nb= sp;                     &n= bsp;           ->  Index Scan using pk_camp= aigns on campaigns  (cost=3D0.29..1.84 rows=3D1 width=3D12) (actual tim= e=3D0.013..0.014 rows=3D1 loops=3D1)
          &= nbsp;                     &= nbsp;                     &= nbsp;                     &= nbsp;               Index Cond: (cam_camp= aign_id =3D m.sol_id)
              &n= bsp;                     &= nbsp;                   -> &= nbsp;Materialize  (cost=3D0.00..36.55 rows=3D1770 width=3D18) (actual t= ime=3D0.007..0.007 rows=3D0 loops=3D1)
         =                      = ;                     &nbs= p;         ->  Seq Scan on iot_2009_ca_member ca=  (cost=3D0.00..27.70 rows=3D1770 width=3D18) (actual time=3D0.002..0.0= 02 rows=3D0 loops=3D1)
              &= nbsp;                     &= nbsp;             ->  Index Scan using= pk_member_cancel on member_cancel mc1  (cost=3D0.44..2.46 rows=3D1 wid= th=3D11) (actual time=3D0.020..0.020 rows=3D0 loops=3D1)
    &n= bsp;                     &= nbsp;                     &= nbsp;       Index Cond: (m.mem_id =3D mem_id)
   = ;                     &nbs= p;                     &nb= sp;         Filter: (member_cancel_type_id =3D '1'::nume= ric)
                   = ;                     &nbs= p;   ->  Index Scan using pk_solicitation on solicitation s &nb= sp;(cost=3D0.29..0.35 rows=3D1 width=3D11) (actual time=3D0.009..0.010 rows=3D= 1 loops=3D1)
                &nbs= p;                     &nb= sp;           Index Cond: (sol_id =3D campaigns.cam= _campaign_id)
                &nb= sp;                     -&= gt;  Materialize  (cost=3D0.00..285.92 rows=3D4880 width=3D5) (act= ual time=3D0.018..2.349 rows=3D5566 loops=3D1)
       = ;                     &nbs= p;               ->  Seq Scan on s= olicitation_template st  (cost=3D0.00..261.52 rows=3D4880 width=3D5) (a= ctual time=3D0.009..1.547 rows=3D5566 loops=3D1)
      &nb= sp;                     &n= bsp;                     Fi= lter: (country_id =3D ANY ('{1,2,3,121,121,4,5,6,7,8,9,10,11,12,13,14}'::num= eric[]))
                  &= nbsp;             ->  Index Scan using= pk_member_cancel on member_cancel mc2  (cost=3D0.44..2.46 rows=3D1 wid= th=3D11) (actual time=3D0.006..0.006 rows=3D0 loops=3D1)
    &n= bsp;                     &= nbsp;           Index Cond: (m.mem_id =3D mem_id)                    &nbs= p;                 Filter: (member_c= ancel_type_id =3D '2'::numeric)
           =               ->  Materialize &n= bsp;(cost=3D0.00..25.59 rows=3D149 width=3D46) (actual time=3D0.004..0.004 r= ows=3D0 loops=3D1)
               = ;                 ->  Seq Sc= an on gdpr_member_classification gmc  (cost=3D0.00..24.85 rows=3D149 wi= dth=3D46) (actual time=3D0.001..0.001 rows=3D0 loops=3D1)
    &= nbsp;                     &= nbsp;           Filter: ((declassification_start_da= te IS NOT NULL) AND (current_fl =3D 1) AND (date_trunc('day'::text, declassi= fication_start_date) <=3D to_date('9/17/2020'::text, 'MM/DD/YYYY'::text))= AND ((declassification_end_date IS NULL) OR (date_trunc('day'::text, declas= sification_end_date) > to_date('9/17/2020'::text, 'MM/DD/YYYY'::text))))<= br>                    -&g= t;  Materialize  (cost=3D0.00..10.81 rows=3D1 width=3D18) (actual t= ime=3D0.003..0.003 rows=3D0 loops=3D1)
         =                 ->  Seq Sca= n on ccpa_member_classification ccpa  (cost=3D0.00..10.80 rows=3D1 widt= h=3D18) (actual time=3D0.003..0.003 rows=3D0 loops=3D1)
    &nb= sp;                     &n= bsp;     Filter: ((anonymization_date IS NOT NULL) AND (current_fl= =3D 1) AND (date_trunc('day'::text, anonymization_date) <=3D to_date('9/= 17/2020'::text, 'MM/DD/YYYY'::text)))
Planning time: 7.622 ms
Executio= n time: 73231.463 ms


Also I have= tried to add a GIN index for better text search as below, 
<= br>
CREATE INDEX idx_fnmae_lname_gin_composite ON wldbowner.member= USING gin
(lower((((fname)::text || ' '::text) || (lname)::text)) rdsad= min.gin_trgm_ops);


Thanks <= /div>

On Sun, Sep 20, 2020 at 7:25 PM Avinash Kumar <avinash.vallarapu@gmail.com&g= t; wrote:


On Sun., Sep. 20, 2020, 10:48 a.m. Shrikant Bhende, <shrikantpostgresql@g= mail.com> wrote:
Hello,

show seq_page_cost;   1=

show random_page_cost; 4
Set this to 1 and rerun the explain analyze and let us know w= hat you see. 
You are using an SSD and the value= of 4 should not be apt. 
=
storage type : SSD 

Table size : 39 GB=  

There are no columns added, also I haven't f= ound anything where we need to update the data for older rows.
Thanks 

On Sun, Sep 20, 2020 at 6:55 PM Avinash Kumar <= ;avinash.vallarapu@gmail.com> wrote:


On Sun., Sep. 20, 2020, 1= 0:19 a.m. Shrikant Bhende, <shrikantpostgresql@gmail.com>= wrote:
Hello all, 

I am facing issues with one of the= queries running on Amazon Aurora (PG VERSION 9.6) which is taking mo= re than a minute to complete. As per the initial investigation I a= ssume that one part of the query is taking a sequential scan on a table= ("wldbowner.member") which is consuming most of the execution time. I tried t= o use index (Btree and GIN ) as well but none of them were helping to get th= e query to run faster. Attached is the query along with the explain analyze o= f the same, any help on the same would be appreciated. 

<= /div>
NOTE : I have done the vacuum on the table already to get rid of b= loat issues.
             Reind= ex and test with more work mem is also helping.
Just wanted to clarify one thing before looking at= the query. 

What is= random_page_cost set to ? 


Thanks and regards

= --Apple-Mail-C5A772D3-0AC8-44F7-9F2B-9C38AF532E8B--