Re: Creating an index-type for LIKE '%value%'

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@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83