Re: type-casting and LIKE queries

On Sat, Mar 15, 2003 at 02:00:35PM +0800, Lincoln Yeoh wrote:
> Are you using the ? character as a wildcard? AFAIK _ and % are the wildcard
> characters for LIKE.

Sorry, that was a typo. My application allows searching with * and ?
characters, but it translates them to % and _ when creating the sql
statement.

> Would having two indexes cover enough cases for you? One that allows
> indexed LIKE '407%'. And one that allows '%4820'.
>
> For the latter just reverse the string and index it, and do a search on
> LIKE '0284%'.

Hey that's an interesting idea!

> Maybe you could create a function that reverses strings, not sure how that
> would work tho - could look messy since you probably don't want to reverse
> the % too.

So I guess that trick wouldn't work if the search string has a % both
at the beginning and the end...

> Not sure if Postgresql would be able to productively use both indexes to
> find a substring in the middle of text, given a suitable query. If the
> substring is in a fixed position in the middle I think it can.

I can't guarantee fixed positions though, because I also have to be able
to run arbitrary searches on varchar columns too... For instance, I
have a column 'email' of type varchar(255), which has a unique index on
lower(email). Some typical searches might be:

*(at)hotmail(dot)com
johndoe(at)*
*unix*

The first two are no problem if I use the 'reverse index' trick you
described. But I don't see how the indexes can get used in the third
case?

However, I went ahead and created the a 'reverse_lc' function to see what
kind of performace I would get. The planner isn't using my index
though. :(

test=> CREATE UNIQUE INDEX test_email_lc_idx ON test (lower(email));
CREATE INDEX
test=> CREATE UNIQUE INDEX test_email_revlc_idx ON test (reverse_lc(email));
CREATE INDEX
test=> VACUUM ANALYZE test ;
VACUUM
test=> EXPLAIN ANALYZE SELECT * FROM test WHERE lower(email) LIKE 'asdf%';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Index Scan using test_email_lc_idx on test (cost=0.00..125.62 rows=38 width=45) (actual time=0.50..0.50 rows=0 loops=1)
Index Cond: ((lower((email)::text) >= 'asdf'::text) AND (lower((email)::text) < 'asdg'::text))
Filter: (lower((email)::text) ~~ 'asdf%'::text)
Total runtime: 0.64 msec
(4 rows)

test=> EXPLAIN ANALYZE SELECT * FROM test WHERE reverse_lc(email) LIKE '%asdf';
QUERY PLAN
------------------------------------------------------------------------------------------------------
Seq Scan on test (cost=0.00..193.56 rows=38 width=45) (actual time=5852.42..5852.42 rows=0 loops=1)
Filter: (reverse_lc((email)::text) ~~ '%asdf'::text)
Total runtime: 5852.54 msec
(3 rows)

test=> SELECT reverse_lc('%asdf');
reverse_lc
------------
fdsa%
(1 row)

Here's the code for it:

CREATE FUNCTION reverse_lc(text) RETURNS text IMMUTABLE AS '
DECLARE
old_str ALIAS FOR $1;
low text;
new_str text;
len integer;
i integer;
BEGIN
len := length(old_str);
low := lower(old_str);
new_str := '''';
i := 0;
WHILE i < len LOOP
new_str := rpad(new_str, i+1, substr(old_str, len-i, 1));
i := i+1;
END LOOP;
RETURN new_str;
END;
' LANGUAGE 'plpgsql';

Did I forget to do something?