From: | Jeff Janes <jeff(dot)janes(at)gmail(dot)com> |
---|---|
To: | Shrikant Bhende <shrikantpostgresql(at)gmail(dot)com> |
Cc: | Avinash Kumar <avinash(dot)vallarapu(at)gmail(dot)com>, dbatoCloud Solution <dbatocloud17(at)gmail(dot)com>, pgsql-admin(at)lists(dot)postgresql(dot)org |
Subject: | Re: Query taking seq scan on a table |
Date: | 2020-09-21 14:31:44 |
Message-ID: | CAMkU=1zeEd2Q1yLKBDaKC1DW1UVxtYJiAX+ifp6mwodTOGqCnw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Aizenberg | 2020-09-21 14:43:31 | How do I configure pgAdmin to use a fixed port? |
Previous Message | zaid khan | 2020-09-21 11:15:43 | Issue in streaming replication after increasing swap. |