Re: LIKE not using indexes (due to locale issue?)

On Wed, 2008-06-25 at 14:58 +1000, Klint Gore wrote:
> Ow Mun Heng wrote:
> > explain select * from d_trr where revision like '^B2.%.SX'
> > --where ast_revision  = 'B2.M.SX'
> >
> > Seq Scan on d_trr  (cost=0.00..2268460.98 rows=1 width=16)
> >   Filter: ((revision)::text ~~ '^B2.%.SX'::text)
> >
> > show lc_collate;
> > en_US.UTF-8
> >
> > Is it that this is handled by tsearch2? Or I need to do the locale to
> > "C" for this to function?
> >
> See http://www.postgresql.org/docs/8.3/interactive/indexes-opclass.html.
> It tells you how to create an index that like might use in non-C locales.

Just more information. This columns is created with the varchar type.

original index is created using

CREATE INDEX idx_d_ast
  ON xmms.d_trh
  USING btree
  (revision varchar_pattern_ops);


CREATE INDEX idx_d_ast2
  ON xmms.d_trh
  USING btree
  (revision);

after creating it, seems like it is still doing the seq_scan.
So what gives? Can I get more clues here?