Is there a way to index in postgres to quickly find a substring

Question

I have a database and you want the table to be able to find something like this in the search: select * from the table where the column is of the type "abc% def% ghi" or select * from the table where the column is of the type "% def% ghi "Is there a way to index a column so that it is not too slow?

Edit: May I also clarify that the database is read-only and will not be updated frequently.

Answer 1

Text search and indexing options include:

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

• text_pattern_ops indexes to support prefix string matches, for example LIKE 'abc%' , but not infix searches, such as %blah% You can use the reverse() d index to search for suffixes.

• pg_tgrm trigram indices on later versions as shown in this recent dba.stackexchange.com .

• An external search and index tool such as Apache Solr .

From the minimum information given above, I would say that only a trigger index can help you, since you are performing an infix search on a string and are not looking for dictionary words. Unfortunately, trigram indices are huge and quite inefficient; Do not expect any magical performance improvements, and keep in mind that they take on a lot of work to create the database and keep it up to date.

Answer 2

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

 create index test_index on test_table (col varchar_pattern_ops); 

This will only work if the pattern does not start with % , in which case a different strategy is required.

Answer 3

If you just need to, for example, get unique substrings in the entire table, you can create a substring index:

 CREATE INDEX i_test_sbstr ON tablename (substring(columname, 5, 3)); -- start at position 5, go for 3 characters It is important that the substring() parameters in the index definition are the same as you use in your query. 

ref: http://www.postgresql.org/message-id/ [e-mail]