Re: MSSQL versus Postgres timing - Mailing list pgsql-sql

View and table creates

CREATE TABLE tblassociate
( clientnum varchar(16) NOT NULL, associateid int4 NOT NULL, associatenum varchar(10), firstname varchar(50),
middleinitvarchar(5), lastname varchar(50), ssn varchar(18), dob timestamp, address varchar(100), city varchar(50),
statevarchar(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,
staffexecidint4, jobtitleid int4, locationid int4, deptid int4, positionnum int4, worktypeid int4, sexid int4,
maritalstatusidint4, 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,
testscoreint4, pin int4, county varchar(50), CONSTRAINT pk_tblassociate PRIMARY KEY (clientnum, associateid),
CONSTRAINTix_tblassociate UNIQUE (clientnum, associatenum) 
)
CREATE TABLE tbllocation
( clientnum varchar(16) NOT NULL, locationid int4 NOT NULL, districtid int4 NOT NULL, regionid int4 NOT NULL,
divisionidint4 NOT NULL, locationnum varchar(8), name varchar(50), clientlocnum varchar(50), address varchar(100),
address2varchar(100), city varchar(50), state varchar(2) NOT NULL DEFAULT 'zz'::character varying, zip varchar(10),
countryidint4, 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),
locationtypeidint4, open_time timestamp, close_time timestamp, insurance_loc_id varchar(50), lpregionid int4, sic int4,
CONSTRAINTpk_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,
presentationidint4 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@fuhr.org]
Sent: Tuesday, February 01, 2005 12:07 PM
To: Joel Fradkin
Cc: pgsql-sql@postgresql.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/