X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id D827253239 for ; Thu, 7 Apr 2005 16:13:52 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 05825-04 for ; Thu, 7 Apr 2005 15:13:50 +0000 (GMT) Received: from vms044pub.verizon.net (vms044pub.verizon.net [206.46.252.44]) by svr1.postgresql.org (Postfix) with ESMTP id 22E24537F0 for ; Thu, 7 Apr 2005 16:13:47 +0100 (BST) Received: from jfradkin ([66.15.127.235]) by vms044.mailsrvcs.net (Sun Java System Messaging Server 6.2 HotFix 0.04 (built Dec 24 2004)) with ESMTPA id <0IEL0078Z0YX8L5B@vms044.mailsrvcs.net> for pgsql-performance@postgresql.org; Thu, 07 Apr 2005 10:13:46 -0500 (CDT) Date: Thu, 07 Apr 2005 11:13:57 -0400 From: "Joel Fradkin" Subject: Any way to speed this up? To: "PostgreSQL Perform" Message-id: <005301c53b84$6b83f8a0$797ba8c0@jfradkin> MIME-version: 1.0 X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 X-Mailer: Microsoft Outlook, Build 10.0.6626 Content-type: multipart/alternative; boundary="----=_NextPart_000_0054_01C53B62.E47258A0" Importance: Normal X-Priority: 3 (Normal) X-MSMail-priority: Normal X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=1.413 tagged_above=0 required=5 tests=AWL, HTML_MESSAGE X-Spam-Level: * X-Archive-Number: 200504/155 X-Sequence-Number: 11552 This is a multi-part message in MIME format. ------=_NextPart_000_0054_01C53B62.E47258A0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Running this explain on windows box, but production on linux both 8.0.1 The MSSQL is beating me out for some reason on this query. The linux box is much more powerful, I may have to increase the cache, = but I am pretty sure its not an issue yet. It has 8 gig internal memory any recommendation on the cache size to = use? =20 explain analyze select * from viwassoclist where clientnum =3D 'SAKS' =20 "Merge Join (cost=3D59871.79..60855.42 rows=3D7934 width=3D112) (actual time=3D46906.000..48217.000 rows=3D159959 loops=3D1)" " Merge Cond: ("outer".locationid =3D "inner".locationid)" " -> Sort (cost=3D393.76..394.61 rows=3D338 width=3D48) (actual time=3D62.000..62.000 rows=3D441 loops=3D1)" " Sort Key: l.locationid" " -> Index Scan using ix_location on tbllocation l (cost=3D0.00..379.56 rows=3D338 width=3D48) (actual = time=3D15.000..62.000 rows=3D441 loops=3D1)" " Index Cond: ('SAKS'::text =3D (clientnum)::text)" " -> Sort (cost=3D59478.03..59909.58 rows=3D172618 width=3D75) = (actual time=3D46844.000..46985.000 rows=3D159960 loops=3D1)" " Sort Key: a.locationid" " -> Merge Right Join (cost=3D0.00..39739.84 rows=3D172618 = width=3D75) (actual time=3D250.000..43657.000 rows=3D176431 loops=3D1)" " Merge Cond: ((("outer".clientnum)::text =3D ("inner".clientnum)::text) AND ("outer".id =3D "inner".jobtitleid))" " -> Index Scan using ix_tbljobtitle_id on tbljobtitle jt (cost=3D0.00..194.63 rows=3D6391 width=3D37) (actual = time=3D32.000..313.000 rows=3D5689 loops=3D1)" " Filter: (1 =3D presentationid)" " -> Index Scan using ix_tblassoc_jobtitleid on = tblassociate a (cost=3D0.00..38218.08 rows=3D172618 width=3D53) (actual = time=3D31.000..41876.000 rows=3D176431 loops=3D1)" " Index Cond: ((clientnum)::text =3D 'SAKS'::text)" "Total runtime: 48500.000 ms" =20 CREATE OR REPLACE VIEW viwassoclist AS=20 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 =3D jt.id AND = jt.clientnum::text =3D a.clientnum::text AND 1 =3D jt.presentationid JOIN tbllocation l ON a.locationid =3D l.locationid AND = l.clientnum::text =3D a.clientnum::text; =20 =20 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) ) =20 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) ) =20 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) ) =20 Joel Fradkin =20 =20 ------=_NextPart_000_0054_01C53B62.E47258A0 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Running this explain on windows box, but production = on linux both 8.0.1

The MSSQL is beating me out for some reason on this = query.

The linux box is much more powerful, I may have to = increase the cache, but I am pretty sure its not an issue yet.

It has 8 gig internal memory any recommendation on = the cache size to use?

 

explain analyze select * from viwassoclist where = clientnum =3D 'SAKS'

 

"Merge Join  (cost=3D59871.79..60855.42 = rows=3D7934 width=3D112) (actual time=3D46906.000..48217.000 rows=3D159959 = loops=3D1)"

"  Merge Cond: = ("outer".locationid =3D "inner".locationid)"

"  ->  Sort  = (cost=3D393.76..394.61 rows=3D338 width=3D48) (actual time=3D62.000..62.000 rows=3D441 = loops=3D1)"

"        Sort = Key: l.locationid"

"        = ->  Index Scan using ix_location on tbllocation l  (cost=3D0.00..379.56 = rows=3D338 width=3D48) (actual time=3D15.000..62.000 rows=3D441 = loops=3D1)"

"        =       Index Cond: ('SAKS'::text =3D (clientnum)::text)"

"  ->  Sort  = (cost=3D59478.03..59909.58 rows=3D172618 width=3D75) (actual time=3D46844.000..46985.000 = rows=3D159960 loops=3D1)"

"        Sort = Key: a.locationid"

"        = ->  Merge Right Join  (cost=3D0.00..39739.84 rows=3D172618 width=3D75) = (actual time=3D250.000..43657.000 rows=3D176431 = loops=3D1)"

"        =       Merge Cond: ((("outer".clientnum)::text =3D = ("inner".clientnum)::text) AND ("outer".id =3D = "inner".jobtitleid))"

"        =       ->  Index Scan using ix_tbljobtitle_id on tbljobtitle jt  (cost=3D0.00..194.63 rows=3D6391 width=3D37) (actual = time=3D32.000..313.000 rows=3D5689 loops=3D1)"

"        =             Filter: (1 =3D presentationid)"

"        =       ->  Index Scan using ix_tblassoc_jobtitleid on tblassociate = a  (cost=3D0.00..38218.08 rows=3D172618 width=3D53) (actual = time=3D31.000..41876.000 rows=3D176431 loops=3D1)"

"        =             Index Cond: ((clientnum)::text =3D 'SAKS'::text)"

"Total runtime: 48500.000 = ms"

 

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 = =3D jt.id AND jt.clientnum::text =3D a.clientnum::text AND 1 =3D = jt.presentationid

   JOIN tbllocation l ON a.locationid =3D = l.locationid AND l.clientnum::text =3D a.clientnum::text;

 

 

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 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 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)

)

 

Joel Fradkin

 

 

------=_NextPart_000_0054_01C53B62.E47258A0--