Re: Query taking seq scan on a table

[Shrikant Bhende <shrikantpostgresql@xxxxxxxxx>]

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@xxxxxxxxx> 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.janes@xxxxxxxxx> wrote:

On Mon, Sep 21, 2020 at 12:51 AM Shrikant Bhende <shrikantpostgresql@xxxxxxxxx> 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