How to create an index to speed up an aggregate LIKE query on an expression?
There is no index support for LIKE / ILIKE in PostgreSQL 8.4 - except for left anchored search terms.

Since PostgreSQL 9.1 the additional module pg_trgm provides operator classes for GIN and GiST trigram indices supporting LIKE / ILIKE or regular expressions (operators ~ and friends). Install once per database:

CREATE EXTENSION pg_trgm;


Example GIN index:

CREATE INDEX tbl_col_gin_trgm_idx ON tbl USING gin (col gin_trgm_ops);


Related:


How is LIKE implemented?
Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL