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)
---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
