Re: Query taking seq scan on a table

Hello,

I have created indexes on both columns and tried again but still the same
result,
below is the explain analyze plan after the btree index,

Limit (cost=11638346.65..11638352.00 rows=102 width=209) (actual
time=74248.165..74248.166 rows=1 loops=1)
-> Unique (cost=11638346.65..11638370.27 rows=450 width=209) (actual
time=74248.163..74248.163 rows=1 loops=1)
-> Sort (cost=11638346.65..11638347.77 rows=450 width=209)
(actual time=74248.163..74248.163 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..11638326.81 rows=450
width=209) (actual time=74248.077..74248.114 rows=1 loops=1)
Join Filter: (m.mem_id = ccpa.mem_id)
-> Nested Loop Left Join (cost=1.46..11638308.14
rows=450 width=213) (actual time=74248.069..74248.106 rows=1 loops=1)
Join Filter: (m.mem_id = gmc.mem_id)
-> Nested Loop Left Join
(cost=1.46..11637277.16 rows=450 width=167) (actual
time=74248.067..74248.103 rows=1 loops=1)
-> Nested Loop (cost=1.02..11636167.66
rows=450 width=162) (actual time=74248.058..74248.094 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..11598195.94 rows=515 width=167) (actual
time=74245.083..74245.113 rows=1 loops=1)
-> Nested Loop Left Join
(cost=0.73..11598011.99 rows=515 width=174) (actual
time=74245.067..74245.097 rows=1 loops=1)
-> Nested Loop Left Join
(cost=0.29..11596742.22 rows=515 width=169) (actual
time=74245.050..74245.080 rows=1 loops=1)
Join Filter:
(ca.mem_id = m.mem_id)
-> Nested Loop
(cost=0.29..11583036.85 rows=515 width=151) (actual
time=74245.023..74245.052 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.005..0.193 rows=2344 loops=1)
->
Materialize (cost=0.29..11564644.51 rows=515 width=142) (actual
time=31.674..31.674 rows=1 loops=2344)
->
Nested Loop (cost=0.29..11564641.94 rows=515 width=142) (actual
time=74243.846..74243.876 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.005..0.345 rows=3722 loops=1)

-> Materialize (cost=0.29..11535437.75 rows=515 width=142) (actual
time=8.316..19.947 rows=1 loops=3722)

-> Nested Loop (cost=0.29..11535435.17 rows=515 width=142) (actual
time=30952.952..74241.900 rows=1 loops=1)

-> Nested Loop (cost=0.00..11534483.37 rows=515 width=130)
(actual time=30952.931..74241.878 rows=1 loops=1)

Join Filter: (m.brn_id = b.brn_id)

Rows Removed by Join Filter: 202

-> Nested Loop (cost=0.00..11532899.40 rows=515
width=116) (actual time=30952.895..74241.751 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..11532015.32
rows=515 width=100) (actual time=30952.851..74241.676 rows=1 loops=1)

Filter: ((lower(unaccent_string((lname)::text))
~~ 'info%'::text) AND (lower(unaccent_string((fname)::text)) ~~
'travel%'::text))

Rows Removed by Filter: 20595516

-> Materialize (cost=0.00..3.71 rows=114 width=27)
(actual time=0.013..0.050 rows=115 loops=1)

-> Seq Scan on product p (cost=0.00..3.14
rows=114 width=27) (actual time=0.007..0.019 rows=115 loops=1)

-> Materialize (cost=0.00..24.03 rows=202 width=26)
(actual time=0.005..0.100 rows=203 loops=1)

-> Seq Scan on brand b (cost=0.00..23.02 rows=202
width=26) (actual time=0.003..0.060 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.025..0.025 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.014..0.014 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.011..0.011 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.016..2.278 rows=5566 loops=1)
-> Seq Scan on
solicitation_template st (cost=0.00..261.52 rows=4880 width=5) (actual
time=0.011..1.500 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.001..0.001 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: 11.217 ms
Execution time: 74248.572 ms

On Tue, Sep 22, 2020 at 6:29 PM Shrikant Bhende <
shrikantpostgresql(at)gmail(dot)com> wrote:

> Hi Jeff,
>
> Thanks for the update.
>
>
> *create index concurrently lname_test_btree_txt_pat_ops on
> wldbowner.member (lname text_pattern_ops) where fname like
> LOWER(unaccent_string(lname) || '%')*
>
> Is this the correct way to create a b-tree index with text_pattern_ops for
> my requirement ?
>
> Thanks
>
> On Mon, Sep 21, 2020 at 8:01 PM Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:
>
>> On Mon, Sep 21, 2020 at 12:51 AM Shrikant Bhende <
>> shrikantpostgresql(at)gmail(dot)com> wrote:
>>
>>> 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);
>>>
>>
>>
>> You index does not match your query:
>>
>> ((lower(unaccent_string((lname)::text)) ~~ 'info%'::text) AND
>> (lower(unaccent_string((fname)::text)) ~~ 'travel%'::text))
>>
>> Your index is not passing the columns through unaccent_string, and it is
>> concatenating the columns while the query is treating them separately. You
>> need to make the index (or indexes, as you might want one for each column)
>> match the query.
>>
>> If the wildcard is always at the end of the search-pattern strings, you
>> could instead use btree indexes with text_pattern_ops.
>>
>> Cheers,
>>
>> Jeff
>>
>