Re: type-casting and LIKE queries

On Sun, Mar 16, 2003 at 07:34:37PM -0500, valerian wrote:
> 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)

Never mind, I just realized that I forgot to reverse the search key
also...

test=> EXPLAIN ANALYZE SELECT * FROM test WHERE reverse_lc(email) LIKE 'fdsa%';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Index Scan using test_email_revlc_idx on test (cost=0.00..125.62 rows=38 width=45) (actual time=0.39..0.39 rows=0 loops=1)
Index Cond: ((reverse_lc((email)::text) >= 'fdsa'::text) AND (reverse_lc((email)::text) < 'fdsb'::text))
Filter: (reverse_lc((email)::text) ~~ 'fdsa%'::text)
Total runtime: 0.53 msec
(4 rows)

So that takes care of the first two types of queries, but not the one
that has a % both at the beginning and end of the search key.

Any ideas on how to handle those?