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)