If you use an exact = does it use the index?> e.g. explain select ... where lower(f)='xxxxxxxx'
Yes it does.
If so it could be your locale setting. On some versions of Postgresql like is disabled on non-C locales.
I'm using 7.4.1. These are the lines in postgresql.conf (it's basically pristine from the one created by initdb).
# These settings are initialized by initdb -- they may be changed lc_messages = 'en_US.iso885915' #locale for system error message strings lc_monetary = 'en_US.iso885915' #locale for monetary formatting lc_numeric = 'en_US.iso885915' #locale for number formatting lc_time = 'en_US.iso885915' #locale for time formatting
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
Yes, that was the cause of the problem. I've now recreated the index using the varchar_pattern_ops:
db1=> create unique index i1 on t(i varchar_pattern_ops); db1=> create unique index i2 on t(lower(i) varchar_pattern_ops);
and now EXPLAIN tells me the query uses Index scan:
db1=> explain select * from t where f like 'xx%';
QUERY PLAN
--------------------------------------------------------------------------------------
Index Scan using i1 on t (cost=0.00..6.01 rows=322 width=14)
Index Cond: ((f ~>=~ 'xx'::character varying) AND (f ~<~ 'xy'::character varying))
Filter: (f ~~ 'xx%'::text)
(3 rows)
db1=> explain select * from t where lower(f) like 'xx%';
QUERY PLAN
----------------------------------------------------------------------------------------------------
Index Scan using i2 on t (cost=0.00..4049.64 rows=1421 width=14)
Index Cond: ((lower(f) ~>=~ 'xx'::character varying) AND (lower(f) ~<~ 'xy'::character varying))
Filter: (lower(f) ~~ 'xx%'::text)
(3 rows)
Hope that helps,
Yes it does, thanks. Apparently using the index does improve the speed:
db1=> select * from t where f like 'xx%';
f
------------
xxAGRrXrXr
xxAwScNpWh
...
xxyuFyyDtn
(98 rows)Time: 9.679 ms
db1=> select * from t where lower(f) like 'xx%';
f
------------
xxaAvoarIZ
XXadJWnXcK
...
xXzynzWllI
(413 rows)Time: 8.626 ms
-- dave
---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
