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

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.

The blacklist table is:
exim=# \d blacklist
Table "public.blacklist"
Column | Type | Modifiers
-------------+-----------------------------+--------------------------
insert_when | timestamp(0) with time zone | default now()
insert_who | text | default "current_user"()
domain | text |
message | text |
Indexes:
"blacklist_dom_idx" btree ("domain")

exim=#

And contains records like:

exim=# select * from blacklist limit 1;
insert_when | insert_who | domain | message
------------------------+------------+----------+---------------------------
------
2003-12-22 21:02:49-06 | ler | 008\.net | 127.0.0.1 MX, SPAMMER
(008.net)
(1 row)

exim=#

Thanks!

LER

--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 972-414-9812 E-Mail: ler(at)lerctr(dot)org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749