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@lerctr.org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749