Read http://www.sai.msu.su/~megera/postgres/gist/pg_trgm/README.pg_trgm
Oleg
On Mon, 7 Feb 2005, Martijn van Oosterhout wrote:
On Mon, Feb 07, 2005 at 09:28:24AM -0800, CG wrote:
As I was exploring ways to optimize my application's use of the database, which
has to run the horrid "SELECT * FROM table WHERE field LIKE '%value%';" in
places, I thought this solution could be built upon to allow for an easier
deployment.
<snip>
AFAICT, the "right" way to do this would be to create an index type which would
take care of splitting the varchar field, and to have the query planner use the
index in a SELECT when a "LIKE '%value%'" is used in the WHERE clause.
Tsearch2 is fantastic, but it works best for fields that contain words. I have
to sift through alphanumeric identification numbers.
Seems to me to depends quite a bit or your problem domain. How big are
the string's you're searching. If you're not searching on word
boundaries like tsearch, you'd need to split on every char. Say you
split on three character blocks. So the string "Hello World" would need
entries for:
"Hel", "ell", "llo", "lo ", "o W", " Wo", "Wor", "orl", "rld"
For N character strings you'd need N-2 entries. If you're storing
entire documents it's not practical. But if all your strings are maybe
15 characters long (maybe serial numbers), it might be practical.
I havn't looked at tsearch but maybe you can customise it to your
needs. If you can redefine the split function you could make it work
appropriately. Then you can define the ~~ operator (which is LIKE) to
call tsearch.
This in just off the top of my head, but maybe it can work.
Hope this helps,
Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@xxxxxxxxxx, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
