Re: Query taking seq scan on a table

Hello,

Configuration of instance :
Type : Amazon Aurora
DB instance : db.r4Large
Vcpu : 2
ECU : 7
Total Memory : 15.25 GB

Shared buffers are set to 75% of total 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.

Thanks

On Mon, Sep 21, 2020 at 10:21 AM Shrikant Bhende <
shrikantpostgresql(at)gmail(dot)com> wrote:

> Hello,
>
> Below is the explain analyze after setting random_page_cost to 1;
>
> Limit (cost=11638306.20..11638311.56 rows=102 width=209) (actual
> time=73231.121..73231.122 rows=1 loops=1)
> -> Unique (cost=11638306.20..11638329.83 rows=450 width=209) (actual
> time=73231.120..73231.120 rows=1 loops=1)
> -> Sort (cost=11638306.20..11638307.33 rows=450 width=209)
> (actual time=73231.120..73231.120 rows=1 loops=1)
> 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'::numeric THEN mc2.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 (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.anonymization_date, gmc.request_source
> Sort Method: quicksort Memory: 25kB
> -> Nested Loop Left Join (cost=1.46..11638286.37 rows=450
> width=209) (actual time=73231.063..73231.098 rows=1 loops=1)
> Join Filter: (m.mem_id = ccpa.mem_id)
> -> Nested Loop Left Join (cost=1.46..11638267.69
> rows=450 width=213) (actual time=73231.053..73231.088 rows=1 loops=1)
> Join Filter: (m.mem_id = gmc.mem_id)
> -> Nested Loop Left Join
> (cost=1.46..11637236.72 rows=450 width=167) (actual
> time=73231.048..73231.083 rows=1 loops=1)
> -> Nested Loop (cost=1.02..11636127.22
> rows=450 width=162) (actual time=73231.037..73231.071 rows=1 loops=1)
> Join Filter: (s.sol_template_id =
> st.sol_template_id)
> Rows Removed by Join Filter: 5565
> -> Nested Loop
> (cost=1.02..11598155.50 rows=515 width=167) (actual
> time=73228.007..73228.038 rows=1 loops=1)
> -> Nested Loop Left Join
> (cost=0.73..11597971.55 rows=515 width=174) (actual
> time=73227.994..73228.024 rows=1 loops=1)
> -> Nested Loop Left
> Join (cost=0.29..11596701.79 rows=515 width=169) (actual
> time=73227.969..73227.999 rows=1 loops=1)
> Join Filter:
> (ca.mem_id = m.mem_id)
> -> Nested Loop
> (cost=0.29..11582996.41 rows=515 width=151) (actual
> time=73227.962..73227.991 rows=1 loops=1)
> Join Filter:
> (websites.wbs_client_id = c.cln_client_id)
> Rows Removed
> by Join Filter: 2343
> -> Seq Scan
> on clients c (cost=0.00..46.75 rows=2375 width=21) (actual
> time=0.006..0.184 rows=2344 loops=1)
> ->
> Materialize (cost=0.29..11564604.07 rows=515 width=142) (actual
> time=31.240..31.240 rows=1 loops=2344)
> ->
> Nested Loop (cost=0.29..11564601.50 rows=515 width=142) (actual
> time=73226.808..73226.836 rows=1 loops=1)
>
> Join Filter: (campaigns.cam_website_id = websites.wbs_website_id)
>
> Rows Removed by Join Filter: 3721
>
> -> Seq Scan on websites (cost=0.00..97.68 rows=3768 width=12) (actual
> time=0.002..0.350 rows=3722 loops=1)
>
> -> Materialize (cost=0.29..11535397.31 rows=515 width=142) (actual
> time=11.071..19.674 rows=1 loops=3722)
>
> -> Nested Loop (cost=0.29..11535394.73 rows=515 width=142) (actual
> time=41204.676..73224.912 rows=1 loops=1)
>
> -> Nested Loop (cost=0.00..11534442.93 rows=515 width=130)
> (actual time=41204.658..73224.892 rows=1 loops=1)
>
> Join Filter: (m.brn_id = b.brn_id)
>
> Rows Removed by Join Filter: 202
>
> -> Nested Loop (cost=0.00..11532858.96 rows=515
> width=116) (actual time=41204.622..73224.764 rows=1 loops=1)
>
> Join Filter: (m.product_id = p.product_id)
>
> Rows Removed by Join Filter: 114
>
> -> Seq Scan on member m (cost=0.00..11531974.88
> rows=515 width=100) (actual time=41204.577..73224.687 rows=1 loops=1)
>
> Filter:
> ((lower(unaccent_string((lname)::text)) ~~ 'info%'::text) AND
> (lower(unaccent_string((fname)::text)) ~~ 'travel%'::text))
>
> Rows Removed by Filter: 20595444
>
> -> Materialize (cost=0.00..3.71 rows=114
> width=27) (actual time=0.012..0.054 rows=115 loops=1)
>
> -> Seq Scan on product p (cost=0.00..3.14
> rows=114 width=27) (actual time=0.006..0.021 rows=115 loops=1)
>
> -> Materialize (cost=0.00..24.03 rows=202 width=26)
> (actual time=0.006..0.088 rows=203 loops=1)
>
> -> Seq Scan on brand b (cost=0.00..23.02 rows=202
> width=26) (actual time=0.002..0.054 rows=203 loops=1)
>
> -> Index Scan using pk_campaigns on campaigns
> (cost=0.29..1.84 rows=1 width=12) (actual time=0.013..0.014 rows=1 loops=1)
>
> Index Cond: (cam_campaign_id = m.sol_id)
> -> Materialize
> (cost=0.00..36.55 rows=1770 width=18) (actual time=0.007..0.007 rows=0
> loops=1)
> -> Seq Scan
> on iot_2009_ca_member ca (cost=0.00..27.70 rows=1770 width=18) (actual
> time=0.002..0.002 rows=0 loops=1)
> -> Index Scan using
> pk_member_cancel on member_cancel mc1 (cost=0.44..2.46 rows=1 width=11)
> (actual time=0.020..0.020 rows=0 loops=1)
> Index Cond:
> (m.mem_id = mem_id)
> Filter:
> (member_cancel_type_id = '1'::numeric)
> -> Index Scan using
> pk_solicitation on solicitation s (cost=0.29..0.35 rows=1 width=11)
> (actual time=0.009..0.010 rows=1 loops=1)
> Index Cond: (sol_id =
> campaigns.cam_campaign_id)
> -> Materialize (cost=0.00..285.92
> rows=4880 width=5) (actual time=0.018..2.349 rows=5566 loops=1)
> -> Seq Scan on
> solicitation_template st (cost=0.00..261.52 rows=4880 width=5) (actual
> time=0.009..1.547 rows=5566 loops=1)
> Filter: (country_id =
> 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
> member_cancel mc2 (cost=0.44..2.46 rows=1 width=11) (actual
> time=0.006..0.006 rows=0 loops=1)
> Index Cond: (m.mem_id = mem_id)
> Filter: (member_cancel_type_id =
> '2'::numeric)
> -> Materialize (cost=0.00..25.59 rows=149
> width=46) (actual time=0.004..0.004 rows=0 loops=1)
> -> Seq Scan on gdpr_member_classification
> gmc (cost=0.00..24.85 rows=149 width=46) (actual time=0.001..0.001 rows=0
> loops=1)
> Filter:
> ((declassification_start_date IS NOT NULL) AND (current_fl = 1) AND
> (date_trunc('day'::text, declassification_start_date) <=
> to_date('9/17/2020'::text, 'MM/DD/YYYY'::text)) AND
> ((declassification_end_date IS NULL) OR (date_trunc('day'::text,
> declassification_end_date) > to_date('9/17/2020'::text,
> 'MM/DD/YYYY'::text))))
> -> Materialize (cost=0.00..10.81 rows=1 width=18)
> (actual time=0.003..0.003 rows=0 loops=1)
> -> Seq Scan on ccpa_member_classification ccpa
> (cost=0.00..10.80 rows=1 width=18) (actual time=0.003..0.003 rows=0
> loops=1)
> Filter: ((anonymization_date IS NOT NULL)
> AND (current_fl = 1) AND (date_trunc('day'::text, anonymization_date) <=
> to_date('9/17/2020'::text, 'MM/DD/YYYY'::text)))
> Planning time: 7.622 ms
> Execution time: 73231.463 ms
>
>
> Also I have tried to add a GIN index for better text search as below,
>
> CREATE INDEX idx_fnmae_lname_gin_composite ON wldbowner.member USING gin
> (lower((((fname)::text || ' '::text) || (lname)::text))
> rdsadmin.gin_trgm_ops);
>
>
> Thanks
>
> On Sun, Sep 20, 2020 at 7:25 PM Avinash Kumar <avinash(dot)vallarapu(at)gmail(dot)com>
> wrote:
>
>>
>>
>> On Sun., Sep. 20, 2020, 10:48 a.m. Shrikant Bhende, <
>> shrikantpostgresql(at)gmail(dot)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 what 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 found anything where we need
>>> to update the data for older rows.
>>>
>>> Thanks
>>>
>>> On Sun, Sep 20, 2020 at 6:55 PM Avinash Kumar <
>>> avinash(dot)vallarapu(at)gmail(dot)com> wrote:
>>>
>>>>
>>>>
>>>> On Sun., Sep. 20, 2020, 10:19 a.m. Shrikant Bhende, <
>>>> shrikantpostgresql(at)gmail(dot)com> wrote:
>>>>
>>>>> Hello all,
>>>>>
>>>>> 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 initial investigation I assume 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 to use index (Btree and GIN ) as well but none
>>>>> of them were helping to get the query to run faster. Attached is the query
>>>>> along with the explain analyze of the same, any help on the same would be
>>>>> appreciated.
>>>>>
>>>>> NOTE : I have done the vacuum on the table already to get rid of bloat
>>>>> issues.
>>>>> Reindex 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
>>>>>
>>>>>
>>>>>