Index question with LIKE keyword - Mailing list pgsql-performance

From Eric Theis
Subject Index question with LIKE keyword
Date
Msg-id NDBBLIDKJMKHODHJGCCOEEMPFGAA.eric_theis@gensler.com
Whole thread Raw
Responses Re: Index question with LIKE keyword  (Stephan Szabo <sszabo@megazone23.bigpanda.com>)
List 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


pgsql-performance by date:

Previous
From: Jochem van Dieten
Date:
Subject: Re: Is a better way to have the same result of this query?
Next
From: Stephan Szabo
Date:
Subject: Re: Index question with LIKE keyword