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