Re: Index question with LIKE keyword

On Thu, 5 Dec 2002, Eric Theis wrote:

> This work under both Windows and Linux.
>
> When I run the following query under Windows, I get what I expect:
> CRMDB=> explain select * from tblCompany where vchcmpname like 'Gensler%';
> NOTICE: QUERY PLAN:
>
> Index Scan using idxcompany1 on tblcompany (cost=0.00..17.07 rows=1
> width=201)
>
> EXPLAIN
>
> However, when I run the same query under Linux, I get this:
> CRMDB=> explain select * from tblCompany where vchCmpName like 'Gensler%';
> NOTICE: QUERY PLAN:
>
> Seq Scan on tblcompany (cost=100000000.00..100000002.01 rows=1 width=212)
>
> EXPLAIN
>
> I really don't understand why this is happening, but I am hoping that
> someone on this list has an idea. The versions of Postgres that I am using
> are Windows 7.2.2 and Linux 7.2.1 and 7.2.2. The Windows version is the
> compiled version that comes with Cygwin and the Linux versions are the RPMs
> that come with Redhat 7.3, Mandrake 9.0 and the Redhat 7.3 RPM from the
> Postgres site.
>
> If anyone has an ideas suggestions I would really appreciate it.

The linux box is probably not running in "C" locale (or at least initdb
wasn't run in "C" locale). The optimization for using indexes on like
currently only works in that locale (because there are issues in some/many
other locales that makes the transformation invalid). There's been talk
about this issue on (I think) -general (or if not there then -hackers)
recently.