Re: Can LIKE use indexes or not?

If you use an exact = does it use the index?
e.g. explain select ... where lower(f)='xxxxxxxx'

If so it could be your locale setting. On some versions of Postgresql like
is disabled on non-C locales. On some versions of Postgresql on some
platforms the default is a non-C locale. With version 7.4 you can
workaround that:
http://www.postgresql.org/docs/current/static/indexes-opclass.html

Hope that helps,

At 03:30 PM 2/5/2004 +0700, David Garamond wrote:
>Reading the archives and the FAQ, it seems to be implied that LIKE can use
>index (and ILIKE can't; so to do case-insensitive search you need to
>create a functional index on LOWER(field) and say: LOWER(field) LIKE 'foo%').
>
>However, EXPLAIN always says seq scan for the test data I'm using. I've
>done 'set enable_seqscan to off' and it still says seq scan. I was curious
>as to how the index will help this query:
>
>db1=> set enable_seqscan to off;
>SET
>Time: 5.732 ms
>db1=> explain select * from t where f like 'xx%';
> QUERY PLAN
>-------------------------------------------------------------------
> Seq Scan on t (cost=100000000.00..100002698.90 rows=89 width=14)
> Filter: (f ~~ 'xx%'::text)
>(2 rows)
>
>db1=> explain select * from t where lower(f) like 'xx%';
> QUERY PLAN
>--------------------------------------------------------------------
> Seq Scan on t (cost=100000000.00..100002893.68 rows=390 width=14)
> Filter: (lower(f) ~~ 'xx%'::text)
>(2 rows)