Re: LIKE search and performance
От | Richard Huxton |
---|---|
Тема | Re: LIKE search and performance |
Дата | |
Msg-id | 4654632C.4020005@archonet.com обсуждение исходный текст |
Ответ на | LIKE search and performance ("Andy" <frum@ar-sd.net>) |
Список | pgsql-performance |
Andy wrote: > SELECT * FROM table > WHERE name like '%john%' or street like '%srt%' > > Anyway, the query planner always does seq scan on the whole table and that > takes some time. How can this be optimized or made in another way to be > faster? > > I tried to make indexes on the columns but no success. None of the normal indexes will work for finding text in the middle of a string. If you do think of a simple way of solving this, drop a short letter explaining your idea to your local patent office followed by the Nobel prize committee. However, one of the contrib packages is "tsearch2" which is designed to do keyword searches on text for you. It'll also handle stemming (e.g. "search" will match "searching" etc.) with the right choice of dictionary. Loads of other clever stuff in it too. It's one of the optional packages with most Linux packaging systems and on the Windows one too. If you install from source see the contrib/ directory for details. -- Richard Huxton Archonet Ltd
В списке pgsql-performance по дате отправления: