Re: BUG #14032: trigram index is not used for '=' operator

Hello,

Unfortunately, pg_trgm can not support '=' operator. If I am not mistaken
it is GiST and GIN limitation.

You can read the documentation
http://www.postgresql.org/docs/current/static/pgtrgm.html

The pg_trgm module provides GiST and GIN index operator classes that allow
> you to create an index over a text column for the purpose of very fast
> similarity searches. These index types support the above-described
> similarity operators, and additionally support trigram-based index searches
> for LIKE, ILIKE, ~ and ~* queries. (These indexes do not support equality
> nor simple comparison operators, so you may need a regular B-tree index
> too.)
>

2016-03-18 13:04 GMT+03:00 <ruslan.zakirov@gmail.com>:

> The following bug has been logged on the website:
>
> Bug reference:      14032
> Logged by:          Ruslan
> Email address:      ruslan.zakirov@gmail.com
> PostgreSQL version: 9.4.6
> Operating system:   linux
> Description:
>
> Hi,
>
> Have table with the following index:
>
> "tags_local_name_trg" gin (lower(name::text) gin_trgm_ops)
>
> Was surprised that I have to use LIKE op to activate index:
>
> sports=> explain analyze select id from tags_local where lower(name) =
> 'xx';
>                                                  QUERY PLAN
>
>
> -------------------------------------------------------------------------------------------------------------
>  Seq Scan on tags_local  (cost=0.00..8647.17 rows=729 width=4) (actual
> time=188.669..188.669 rows=0 loops=1)
>    Filter: (lower((name)::text) = 'xx'::text)
>    Rows Removed by Filter: 145887
>  Planning time: 0.298 ms
>  Execution time: 188.695 ms
> (5 rows)
>
> sports=> explain analyze select id from tags_local where lower(name) like
> 'xx';
>                                                           QUERY PLAN
>
>
>
------------------------------------------------------------------------------------------------------------------------------
>  Bitmap Heap Scan on tags_local  (cost=9.01..122.71 rows=729 width=4)
> (actual time=1.014..1.014 rows=0 loops=1)
>    Recheck Cond: (lower((name)::text) ~~ 'xx'::text)
>    ->  Bitmap Index Scan on tags_local_name_trg  (cost=0.00..8.82 rows=729
> width=0) (actual time=1.013..1.013 rows=0 loops=1)
>          Index Cond: (lower((name)::text) ~~ 'xx'::text)
>  Planning time: 0.546 ms
>  Execution time: 1.076 ms
> (6 rows)
>
>
>
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>



--
Artur Zakirov
Postgres Professional: http://www.postgrespro.com
Russian Postgres Company