CODEWITHSUNDEEP
postgresql
dan
dan

Reputation: 1615

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

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.

Upvotes: 35

Views: 32498

Answers (3)

rogerdpack
rogerdpack

Reputation: 66711

If you need just to, for instance, get unique substrings in an 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/[email protected]

Upvotes: 11

Craig Ringer
Craig Ringer

Reputation: 324325

Options for text search and indexing include:

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.

Upvotes: 34

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125204

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.

Upvotes: 7

Related Questions