Re: MSSQL versus Postgres timing

View and table creates

CREATE TABLE tblassociate
(
clientnum varchar(16) NOT NULL,
associateid int4 NOT NULL,
associatenum varchar(10),
firstname varchar(50),
middleinit varchar(5),
lastname varchar(50),
ssn varchar(18),
dob timestamp,
address varchar(100),
city varchar(50),
state varchar(50),
country varchar(50),
zip varchar(10),
homephone varchar(14),
cellphone varchar(14),
pager varchar(14),
associateaccount varchar(50),
doh timestamp,
dot timestamp,
rehiredate timestamp,
lastdayworked timestamp,
staffexecid int4,
jobtitleid int4,
locationid int4,
deptid int4,
positionnum int4,
worktypeid int4,
sexid int4,
maritalstatusid int4,
ethnicityid int4,
weight float8,
heightfeet int4,
heightinches int4,
haircolorid int4,
eyecolorid int4,
isonalarmlist bool NOT NULL DEFAULT false,
isactive bool NOT NULL DEFAULT true,
ismanager bool NOT NULL DEFAULT false,
issecurity bool NOT NULL DEFAULT false,
createdbyid int4,
isdeleted bool NOT NULL DEFAULT false,
militarybranchid int4,
militarystatusid int4,
patrontypeid int4,
identificationtypeid int4,
workaddress varchar(200),
testtypeid int4,
testscore int4,
pin int4,
county varchar(50),
CONSTRAINT pk_tblassociate PRIMARY KEY (clientnum, associateid),
CONSTRAINT ix_tblassociate UNIQUE (clientnum, associatenum)
)
CREATE TABLE tbllocation
(
clientnum varchar(16) NOT NULL,
locationid int4 NOT NULL,
districtid int4 NOT NULL,
regionid int4 NOT NULL,
divisionid int4 NOT NULL,
locationnum varchar(8),
name varchar(50),
clientlocnum varchar(50),
address varchar(100),
address2 varchar(100),
city varchar(50),
state varchar(2) NOT NULL DEFAULT 'zz'::character varying,
zip varchar(10),
countryid int4,
phone varchar(15),
fax varchar(15),
payname varchar(40),
contact char(36),
active bool NOT NULL DEFAULT true,
coiprogram text,
coilimit text,
coiuser varchar(255),
coidatetime varchar(32),
ec_note_field varchar(1050),
locationtypeid int4,
open_time timestamp,
close_time timestamp,
insurance_loc_id varchar(50),
lpregionid int4,
sic int4,
CONSTRAINT pk_tbllocation PRIMARY KEY (clientnum, locationid),
CONSTRAINT ix_tbllocation_1 UNIQUE (clientnum, locationnum, name),
CONSTRAINT ix_tbllocation_unique_number UNIQUE (clientnum, divisionid,
regionid, districtid, locationnum)
)

CREATE TABLE tbljobtitle
(
clientnum varchar(16) NOT NULL,
id int4 NOT NULL,
value varchar(50),
code varchar(16),
isdeleted bool DEFAULT false,
presentationid int4 NOT NULL DEFAULT 1,
CONSTRAINT pk_tbljobtitle PRIMARY KEY (clientnum, id, presentationid)
)

CREATE OR REPLACE VIEW viwassoclist AS
SELECT a.clientnum, a.associateid, a.associatenum, a.lastname, a.firstname,
jt.value AS jobtitle, l.name AS "location", l.locationid AS mainlocationid,
l.divisionid, l.regionid, l.districtid, (a.lastname::text || ', '::text) ||
a.firstname::text AS assocname, a.isactive, a.isdeleted
FROM tblassociate a
LEFT JOIN tbljobtitle jt ON a.jobtitleid = jt.id AND jt.clientnum::text =
a.clientnum::text AND 1 = jt.presentationid
JOIN tbllocation l ON a.locationid = l.locationid AND l.clientnum::text =
a.clientnum::text;

-----Original Message-----
From: Michael Fuhr [mailto:mike(at)fuhr(dot)org]
Sent: Tuesday, February 01, 2005 12:07 PM
To: Joel Fradkin
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: [SQL] MSSQL versus Postgres timing

On Tue, Feb 01, 2005 at 11:54:11AM -0500, Joel Fradkin wrote:
>
> A table with 645,000 records for associates has view (basically select *
> from tblassociates where clientnum = 'test')
>
> This is taking 13 seconds in postgres and 3 seconds in MSSQL.

Please post the EXPLAIN ANALYZE output for the slow query, once
with enable_seqscan on and once with it off. For example:

SET enable_seqscan TO on; -- if not already on
EXPLAIN ANALYZE SELECT * FROM tblassociates WHERE clientnum = 'test';

SET enable_seqscan TO off;
EXPLAIN ANALYZE SELECT * FROM tblassociates WHERE clientnum = 'test';

> Be glad to provide the view and tables etc.

Please do -- it might help us spot something that could be improved.
What version of PostgreSQL are you using?

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/