Re: indexing and LIKE

On Fri, 12 Oct 2001, Patrik Kudo wrote:

> kudo=# select version();
> version
> --------------------------------------------------------------
> PostgreSQL 7.1.3 on i386--freebsd4.3, compiled by GCC 2.95.3
> (1 row)
>
> kudo=# create index person_lower_lname_idx on person (lower(last_name));
> CREATE
> kudo=# vacuum analyze person;
> VACUUM
> kudo=# explain select userid, first_name, last_name from person where lower(last_name) like 'kud%';
> NOTICE: QUERY PLAN:
>
> Seq Scan on person (cost=0.00..217.44 rows=70 width=36)
>
> EXPLAIN
> kudo=# explain select userid, first_name, last_name from person where lower(last_name) = 'kudo';
> NOTICE: QUERY PLAN:
>
> Index Scan using person_lower_lname_idx on person (cost=0.00..57.82 rows=70 width=36)
>
> As you can see, the functional index is working fine when using the
> "normal" = operator. However, it is not used when using the "like"
> operator, which I need. I understand that a pattern-matched query probably
> can't be made as effective as a query with =, but I think it, at least
> theoretically, should be possible to use a btree-index to find matches in
> the first query above.
>
> Am I totaly wrong here? What is possible/impossible with Postgres?

Are you running with locale support turned on, and if so what locale?
IIRC, if it's not C locale Postgres won't use the index.