ilike search really uses its index? I have a column called nickname and have created index for it.

 create index test_idx on test_table using btree (nickname);


My query is,

 select * from test_table where nickname ilike '%pga%';


test_table is having around 100k records. when I'm doing explain, i am getting some cost which is same as before creating the index. Why is that.?

(using Postgresql 9.2)

Edit 1:

nickname column is character varying(100)
LIKE with a wildcard at the start of the expression can't use a btree index. In order to make that query use an index, you need to create a trigram index for which you need to install the pg_trgm extension first.

create index on test_table using gin (nickname gin_trgm_ops);