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

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.

As a side note, the ^ in the string above has no special meaning for like.

klint.

--
Klint Gore
Database Manager
Sheep CRC
A.G.B.U.
University of New England
Armidale NSW 2350

Ph: 02 6773 3789
Fax: 02 6773 3266
EMail: kgore4(at)une(dot)edu(dot)au