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. ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
