Is there a way to index in postgres for fast substring searches
postgresql
Solution
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.
Problem
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.