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

[Larry Rosenman <ler@xxxxxxxxxx>]

On Mon, 7 Feb 2005, Steve Atkins wrote:
> A functional btree index on reverse(domain) might get you what you're
> looking for.

[snip]

I wound up doing the following:

--
-- Name: reverse(text); Type: FUNCTION; Schema: public; Owner: ler
--

CREATE 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;


ALTER FUNCTION public.reverse(text) OWNER TO ler;

--
-- Name: update_new_domain2(); Type: FUNCTION; Schema: public; Owner: ler
--

CREATE FUNCTION update_new_domain2() RETURNS "trigger"
    AS $$
BEGIN
IF TG_OP = 'DELETE'
THEN RETURN OLD;
END IF;
IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE'
THEN NEW.new_domain2 :=  (reverse(lower('%' || NEW.domain)) );
     RETURN NEW;
END IF;
END;
$$
    LANGUAGE plpgsql IMMUTABLE;


ALTER FUNCTION public.update_new_domain2() OWNER TO ler;

--
-- Name: blacklist; Type: TABLE; Schema: public; Owner: ler; Tablespace: 
--

CREATE TABLE blacklist (
    insert_when timestamp(0) with time zone DEFAULT now(),
    insert_who text DEFAULT "current_user"(),
    message text NOT NULL,
    "domain" text NOT NULL,
    new_domain2 text NOT NULL
);
ALTER TABLE ONLY blacklist ALTER COLUMN "domain" SET STATISTICS 100;
ALTER TABLE ONLY blacklist ALTER COLUMN new_domain2 SET STATISTICS 100;


ALTER TABLE public.blacklist OWNER TO ler;
--
-- Name: blk_new_idx3; Type: INDEX; Schema: public; Owner: ler; Tablespace: 
--

CREATE INDEX blk_new_idx3 ON blacklist USING btree (new_domain2);

ALTER TABLE blacklist CLUSTER ON blk_new_idx3;


ALTER INDEX public.blk_new_idx3 OWNER TO ler;

--
-- Name: blacklist_domain; Type: TRIGGER; Schema: public; Owner: ler
--

CREATE TRIGGER blacklist_domain
    BEFORE INSERT OR DELETE OR UPDATE ON blacklist
    FOR EACH ROW
    EXECUTE PROCEDURE update_new_domain2();

It doesn't yet use the index with the 254 domains I have in my fecal roster, but
it's also about 5x as fast as the other REGEX lookup.

Thanks for the ideas!

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 3: if posting/reading through Usenet, please send an appropriate
     subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your
     message can get through to the mailing list cleanly