3

We have a Postgres database which contains 2 millions entries. We have tried using equality search and it works instantly (SELECT * FROM a WHERE b = "asd")

But we would like "LIKE '%asd%'" operation to be fast too. How do we do that?

Improve this question
2
  • Add an index if one doesn't already exist... Commented Oct 8, 2009 at 21:26
  • Ah, right - because of the leading "%" Commented Oct 8, 2009 at 21:33

5 Answers 5

Reset to default
3

You need full text index. This may help http://wiki.postgresql.org/wiki/Full_Text_Indexing_with_PostgreSQL

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

This won't work if searching for word fragments is also required. Then an inverse trigram index would probably be best.
If I understood full-text search right then it indexing by creating different word suffixes and search for them in indexed table. Will this work for a column containing only simple non-english words (items codes actually)?
3

Generally like '%something%' is not indexable.

But.

There are couple of issues:

  1. Are you absolutely sure that you need full substring match? Perhaps you could do with "any word (whitespace separated) starts with something"? - it's close to %something% and definitely indexable.
  2. You might want to use full text searches, but they usually work on words, so it's even less applicable to your solution if you positively really need substring search.
  3. You can try to use trigrams for your search (module pg_trgm in contrib)
  4. You can also try wildspeed, but be warned that it makes huge indexes.
Improve this answer

Comments

1

You won't be able to optimize this as it stands.

Because of the wildcard at the front of the search, it is required to scan the entire table for matches, meaning it can't use indexes.

Improve this answer

Comments

1

You can't really speed it up because that syntax will not allow the indexes to be used. If at all possible you should never use a wildcard as the first part of a LIKE. Without knowing the first character of the field, there is no way possible to use the index, hence you get a table scan which is slow.

Personally I never let my users do a search without giving me the begining of what they are searching for. In SQL server if you must do this, you can set up a full-text search but I don't know if Postgres has that.

Improve this answer

3 Comments

Thanks. This actually improved performance dramatically, but maybe you could help me find a way to search for the text in the middle of the data. Maybe there is some other way which doesn't involve LIKE statement. The data is a in that column is not a huge text only 3-10 symbols long (item code in warehouse).
I don't know if POSITION uses an index: position(substring in string), postgresql.org/docs/current/static/functions-string.html
or you could try searching first for the like with no wildcard as the first character ((or the exact search with =) and only doing the wildcard for the first character if the other search came up empty. Don't know if that would work in your case but would speed up many of your searches.
0

Use some kind of "full text" search index, for example PostGres looks like it has some support built in here.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.