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

From: Klint Gore <kgore4(at)une(dot)edu(dot)au>
To: Ow Mun Heng <Ow(dot)Mun(dot)Heng(at)wdc(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: LIKE not using indexes (due to locale issue?)
Date: 2008-06-25 04:58:30
Message-ID: 4861D076.3060206@une.edu.au
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Ow Mun Heng 2008-06-25 06:15:26 Re: LIKE not using indexes (due to locale issue?)
Previous Message Marcelo Martins 2008-06-25 04:13:14 pg_dump estimation