Обсуждение: Query taking seq scan on a table

On Sun., Sep. 20, 2020, 10: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 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

On Sun., Sep. 20, 2020, 10:48 a.m. Shrikant Bhende, <shrikantpostgresql@gmail.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.vallarapu@gmail.com> wrote:

On Sun., Sep. 20, 2020, 10: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 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

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.vallarapu@gmail.com> wrote:

On Sun., Sep. 20, 2020, 10:48 a.m. Shrikant Bhende, <shrikantpostgresql@gmail.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.vallarapu@gmail.com> wrote:

On Sun., Sep. 20, 2020, 10: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 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

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

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@gmail.com> wrote:

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

Shrikant Bhende <shrikantpostgresql@gmail.com> writes:
> *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 ?

No.  What you're trying to optimize is

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

so you need one or both of

create index on member (lower(unaccent_string(lname)) text_pattern_ops);
create index on member (lower(unaccent_string(fname)) text_pattern_ops);

If one of those two conditions is reliably more selective than the
other, perhaps just one index would do.

                        regards, tom lane