Index question with LIKE keyword

Поиск
Список
Период
Сортировка
От Eric Theis
Тема Index question with LIKE keyword
Дата
Msg-id NDBBLIDKJMKHODHJGCCOEEMPFGAA.eric_theis@gensler.com
обсуждение исходный текст
Ответы Re: Index question with LIKE keyword  (Stephan Szabo <sszabo@megazone23.bigpanda.com>)
Список 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 по дате отправления:

Предыдущее
От: Jochem van Dieten
Дата:
Сообщение: Re: Is a better way to have the same result of this query?
Следующее
От: Stephan Szabo
Дата:
Сообщение: Re: Index question with LIKE keyword