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@xxxxxxxxxx US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749 ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to majordomo@xxxxxxxxxxxxxx
