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

On Mon, Feb 07, 2005 at 03:16:51PM -0600, Larry Rosenman wrote:
> 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)

A functional btree index on reverse(domain) might get you what you're
looking for.

<digs in the Abacus source code...>

CREATE OR REPLACE FUNCTION reverse(text) RETURNS text AS '
DECLARE
original alias for $1;
reverse_str text;
i int4;
BEGIN
reverse_str = '''';
FOR i IN REVERSE LENGTH(original)..1 LOOP
reverse_str = reverse_str || substr(original,i,1);
END LOOP;
return reverse_str;
END;'
LANGUAGE 'plpgsql' IMMUTABLE;

Then do

CREATE INDEX foo_idx ON blacklist(reverse(domain));

SELECT * FROM blacklist WHERE reverse(domain) LIKE reverse(bar) || '%';

Cheers,
Steve