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?