Index question with LIKE keyword

From: "Eric Theis" <eric_theis(at)gensler(dot)com>
To: <pgsql-performance(at)postgresql(dot)org>
Subject: Index question with LIKE keyword
Date: 2002-12-05 16:34:14
Message-ID: NDBBLIDKJMKHODHJGCCOEEMPFGAA.eric_theis@gensler.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

I have a question about some strange behavior on what should be a rather
easy issue.
I am not getting the query plan that I expect given the query and the
indexes.

I have a table with the following structure:
CREATE TABLE tblCompany(
intCmpID serial NOT NULL,
vchCmpName varchar(60) NOT NULL,
vchCmpAltName varchar(100) NULL,
vchCmpPrevName varchar(60) NULL,
intCmpParentID int NULL,
intCmpOwnerEmpID int NOT NULL,
dateCmpMaintained datetime NOT NULL,
chrCmpStatus char(1) NOT NULL,
intModifiedBy int NOT NULL,
dateModifiedOn datetime NOT NULL,
CONSTRAINT pkCompany PRIMARY KEY (intCmpID)
) ;

It has the following index:
CREATE INDEX idxCompany1 ON tblCompany(vchCmpName);

When I run the following query in Postgres, I get the results I expect:
CRMDB=> explain select * from tblCompany where vchCmpName = 'Gensler';
NOTICE: QUERY PLAN:

Index Scan using idxcompany1 on tblcompany (cost=0.00..5.21 rows=1
width=212)

EXPLAIN

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.

TIA
Eric

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Stephan Szabo 2002-12-05 16:47:29 Re: Index question with LIKE keyword
Previous Message Jochem van Dieten 2002-12-05 10:01:15 Re: Is a better way to have the same result of this query?