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(dot)zakirov(at)gmail(dot)com>:

> The following bug has been logged on the website:
>
> Bug reference: 14032
> Logged by: Ruslan
> Email address: ruslan(dot)zakirov(at)gmail(dot)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(at)postgresql(dot)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