Re: Query taking seq scan on a table

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
>>>>
>>>>
>>>>