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);
  • I have executed, create extension pg_trgm; then, tried to create the index. But its showing, operator class "gist_trgm_ops" does not exist for access method "gin". Have i missed some thing? – Spike Jul 26 '16 at 6:48
  • sorry, copy & paste error. For a gin index it needs to be gin_trgm_ops – a_horse_with_no_name Jul 26 '16 at 6:50
  • Thanks. working fine. i can see the improvement. It can be applied to huge table also,? like, 10 million records are there in a table where i will use the same index and query. – Spike Jul 26 '16 at 6:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.