Re: Can LIKE use indexes or not?

Lincoln Yeoh wrote:
> 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