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

On Mon, Feb 07, 2005 at 03:16:51PM -0600, 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 not sure, but this might be what ltree was designed for. After all,
they're just stems and you want to match any domain ending in that
stem...

Hope this helps,

--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.