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

On Mon, 7 Feb 2005, Larry Rosenman wrote:

> Oleg Bartunov wrote:
>> Read
>> http://www.sai.msu.su/~megera/postgres/gist/pg_trgm/README.pg_trgm
>>
>> Oleg
>> On Mon, 7 Feb 2005, Martijn van Oosterhout wrote:
> Would you have a suggestion to index the following query:
>
> SELECT domain,message,'1' as truth FROM blacklist
> WHERE somedomain ~* '(?:.+\.|)' || domain || '\$')
>
> The somedomain is actually a constant passed in from Exim (it's the sender's
> righthand
> Side of an E-Mail address).
>
> I'm looking to see if the domain name is in my blacklist.
>
> I may just be SOL, but I figured I'd ask.

Larry, I pointed you to pg_trgm module mostly following Martijn's
suggestions. Now, I see you need another our module - ltree,
see http://www.sai.msu.su/~megera/postgres/gist/ltree/
for details.

Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83