I'm pretty sure '%John%' uses the index. explain analyze verbose SELECT name FROM wai_users WHERE lower(name) LIKE '%john%';
QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------ Bitmap Heap Scan on public.wai_users (cost=53.45..1345.46 rows=900 width=14) (actual time=18.474..32.093 rows=1596 loops=1) Output: name Recheck Cond: (lower((wai_users.name)::text) ~~ '%john%'::text) -> Bitmap Index Scan on idx_user_name (cost=0.00..53.41 rows=900 width=0) (actual time=18.227..18.227 rows=1596 loops=1) Index Cond: (lower((wai_users.name)::text) ~~ '%john%'::text) Total runtime: 33.662 ms (6 rows) On Thu, Dec 18, 2014 at 10:00 AM, Giuseppe Broccolo < giuseppe.brocc...@2ndquadrant.it> wrote: > > I'm not sure about the '%' operator, but I'm sure that the GIST index will > never be used in the > > SELECT * FROM users WHERE lower(name) LIKE '%john%'; > > query; it is used for left or right anchored search, such as 'john%' or > '%john'. > > Giuseppe. > -- > Giuseppe Broccolo - 2ndQuadrant Italy > PostgreSQL Training, Services and Support > giuseppe.brocc...@2ndquadrant.it | www.2ndQuadrant.it >