Re: Query taking seq scan on a table - Mailing list pgsql-admin

From Shrikant Bhende
Subject Re: Query taking seq scan on a table
Date
Msg-id CAMTQpJCguViOXL5kY=h90u40iS3MkRzLS39umnNYcQDLoFZ9oA@mail.gmail.com
Whole thread Raw
In response to Re: Query taking seq scan on a table  (Jeff Janes <jeff.janes@gmail.com>)
Responses Re: Query taking seq scan on a table  (Shrikant Bhende <shrikantpostgresql@gmail.com>)
Re: Query taking seq scan on a table  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-admin
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

pgsql-admin by date:

Previous
From: "JOIGNY Michael @Neteven"
Date:
Subject: Re: Cannot allocate memory
Next
From: Hannah Huang
Date:
Subject: Re: Cannot allocate memory