Is there a way to index in postgres for fast substring searches

Question

I have a database and want to be able to look up in a table a search that's something like: select * from table where column like "abc%def%ghi" or select * from table where column like "%def%ghi" Is there a way to index the column so that this isn't too slow?

Edit: Can I also clarify that the database is read only and won't be updated often.

Answer 1

Options for text search and indexing include:

• full-text indexing with dictionary based search, including support for prefix-search, eg to_tsvector(mycol) @@ to_tsquery('search:*')

• text_pattern_ops indexes to support prefix string matches eg LIKE 'abc%' but not infix searches like %blah%;. A reverse()d index may be used for suffix searching.

• pg_tgrm trigram indexes on newer versions as demonstrated in this recent dba.stackexchange.com post.

• An external search and indexing tool like Apache Solr.

From the minimal information given above, I'd say that only a trigram index will be able to help you, since you're doing infix searches on a string and not looking for dictionary words. Unfortunately, trigram indexes are huge and rather inefficient; don't expect some kind of magical performance boost, and keep in mind that they take a lot of work for the database engine to build and keep up to date.

Answer 2

For the like operator use one of the operator classes varchar_pattern_ops or text_pattern_ops

create index test_index on test_table (col varchar_pattern_ops); 

That will only work if the pattern does not start with a % in which case another strategy is required.