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
