Hi there,
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?
A:
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.
Patrick
2009-10-08 21:28:59
+1
A:
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.
HLGEM
2009-10-08 21:30:04
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).
Sergej Andrejev
2009-10-08 21:38:53
I don't know if POSITION uses an index: position(substring in string), http://www.postgresql.org/docs/current/static/functions-string.html
OMG Ponies
2009-10-08 21:49:16
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.
HLGEM
2009-10-08 22:10:02
A:
Use some kind of "full text" search index, for example PostGres looks like it has some support built in here.
1800 INFORMATION
2009-10-08 21:30:05
+3
A:
You need full text index. This may help http://wiki.postgresql.org/wiki/Full%5FText%5FIndexing%5Fwith%5FPostgreSQL
Alex Reitbort
2009-10-08 21:30:36
This won't work if searching for word fragments is also required. Then an inverse trigram index would probably be best.
Ants Aasma
2009-10-09 05:56:59
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)?
Sergej Andrejev
2009-10-09 08:46:10
A:
Generally like '%something%' is not indexable.
But.
There are couple of issues:
- 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.
- 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.
- You can try to use trigrams for your search (module pg_trgm in contrib)
- You can also try wildspeed, but be warned that it makes huge indexes.
depesz
2009-10-09 10:39:24