Обсуждение: MSSQL versus Postgres timing

On Tue, 2005-02-01 at 10:54, Joel Fradkin wrote:
> All is moving along well.
>
> I have all my views and data and am testing things out a bit.
>
> A table with 645,000 records for associates has view (basically select
> * from tblassociates where clientnum = ‘test’)

What does

explain analyze select * from tblassociates where clientnum = ‘test’

say?

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/

Michael Fuhr wrote:
> 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?
> 

Also, is clientnum a string datatype, or are you doing implicit type conversion?

QUERY PLAN
"Merge Join  (cost=47489.81..47975.65 rows=3758 width=111) (actual
time=27167.305..29701.080 rows=85694 loops=1)"
"  Merge Cond: (""outer"".locationid = ""inner"".locationid)"
"  ->  Sort  (cost=1168.37..1169.15 rows=312 width=48) (actual
time=261.096..262.410 rows=402 loops=1)"
"        Sort Key: l.locationid"
"        ->  Index Scan using ix_tbllocation on tbllocation l
(cost=0.00..1155.44 rows=312 width=48) (actual time=213.107..259.160
rows=402 loops=1)"
"              Index Cond: ('SAKS'::text = (clientnum)::text)"
"  ->  Sort  (cost=46321.45..46535.47 rows=85611 width=74) (actual
time=26906.148..27689.258 rows=85695 loops=1)"
"        Sort Key: a.locationid"
"        ->  Merge Right Join  (cost=38119.24..39307.55 rows=85611 width=74)
(actual time=22236.915..25384.945 rows=99139 loops=1)"
"              Merge Cond: (((""outer"".clientnum)::text =
""inner"".""?column10?"") AND (""outer"".id = ""inner"".jobtitleid))"
"              ->  Index Scan using ix_tbljobtitle_id on tbljobtitle jt
(cost=0.00..338.90 rows=6337 width=37) (actual time=164.976..2290.760
rows=5662 loops=1)"
"                    Filter: (1 = presentationid)"
"              ->  Sort  (cost=38119.24..38333.26 rows=85611 width=52)
(actual time=20667.645..21031.627 rows=99139 loops=1)"
"                    Sort Key: (a.clientnum)::text, a.jobtitleid"
"                    ->  Seq Scan on tblassociate a  (cost=0.00..31105.34
rows=85611 width=52) (actual time=14.768..16024.395 rows=99139 loops=1)"
"                          Filter: ((clientnum)::text = 'SAKS'::text)"
"Total runtime: 30319.859 ms"

Joel Fradkin

-----Original Message-----
From: Scott Marlowe [mailto:smarlowe@g2switchworks.com]
Sent: Tuesday, February 01, 2005 11:59 AM
To: Joel Fradkin
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] MSSQL versus Postgres timing

On Tue, 2005-02-01 at 10:54, Joel Fradkin wrote:
> All is moving along well.
>
> I have all my views and data and am testing things out a bit.
>
> A table with 645,000 records for associates has view (basically select
> * from tblassociates where clientnum = 'test')

What does

explain analyze select * from tblassociates where clientnum = 'test'

say?

With seq scan on.
-----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/

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

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/

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

Try removing those ::text casts...and creating two column indexes on the columns you use in your Join

"Joel Fradkin" <jfradkin@wazagua.com> writes:
> "              ->  Sort  (cost=38119.24..38333.26 rows=85611 width=52) (actual time=20667.645..21031.627 rows=99139
loops=1)"
> "                    Sort Key: (a.clientnum)::text, a.jobtitleid"
> "                    ->  Seq Scan on tblassociate a  (cost=0.00..31105.34 rows=85611 width=52) (actual
time=14.768..16024.395rows=99139 loops=1)"
 
> "                          Filter: ((clientnum)::text = 'SAKS'::text)"

The sort steps seem slower than they ought to be.  I suspect you ought
to raise sort_mem ... try 10MB instead of 1MB.  Also, if you are running
in a locale other than C and don't have a good reason for doing so, it
would be worth trying C locale instead.

The results with enable_seqscan off also suggest that random_page_cost
may be too high for your environment.

BTW, the schema you posted does not match these plans --- there are
indexes referenced in the plans that do not appear in the schema.
        regards, tom lane

I have added indexes for clientnum (and clientnum and unique identifier like
jobtitleid for jobtitle table) to see if it would help sorry about it not
matching. I gave you the definition outlined in PGadmin table window (I can
add the indexes if it will help).

It is still running slower even when I force an indexed scan.

I will look at the other ideas you mentioned as we have added indexes to
another view with the same results (slower then MSSQL)

I did not put in the ::text it did that in PGadmin the original text I ran
to create the view was.

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 ||
', ' || a.firstname as assocname, a.isactive, a.isdeleted
from         tblassociate a left outer join                     tbljobtitle jt on a.jobtitleid = jt.id and
jt.clientnum = a.clientnum   and 1= jt.presentationid inner join                     tbllocation l on a.locationid =
l.locationidand
 
l.clientnum = a.clientnum

;




Joel Fradkin
Wazagua, Inc.
2520 Trailmate Dr
Sarasota, Florida 34243
Tel.  941-753-7111 ext 305
jfradkin@wazagua.com
www.wazagua.com
Powered by Wazagua
Providing you with the latest Web-based technology & advanced tools.
C 2004. WAZAGUA, Inc. All rights reserved. WAZAGUA, IncThis email message is for the use of the intended recipient(s)
andmay
 
contain confidential and privileged information.  Any unauthorized review,
use, disclosure or distribution is prohibited.  If you are not the intended
recipient, please contact the sender by reply email and delete and destroy
all copies of the original message, including attachments.


-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us] 
Sent: Tuesday, February 01, 2005 4:53 PM
To: Joel Fradkin
Cc: 'Michael Fuhr'; pgsql-sql@postgresql.org
Subject: Re: [SQL] MSSQL versus Postgres timing 

"Joel Fradkin" <jfradkin@wazagua.com> writes:
> "              ->  Sort  (cost=38119.24..38333.26 rows=85611 width=52)
(actual time=20667.645..21031.627 rows=99139 loops=1)"
> "                    Sort Key: (a.clientnum)::text, a.jobtitleid"
> "                    ->  Seq Scan on tblassociate a  (cost=0.00..31105.34
rows=85611 width=52) (actual time=14.768..16024.395 rows=99139 loops=1)"
> "                          Filter: ((clientnum)::text = 'SAKS'::text)"

The sort steps seem slower than they ought to be.  I suspect you ought
to raise sort_mem ... try 10MB instead of 1MB.  Also, if you are running
in a locale other than C and don't have a good reason for doing so, it
would be worth trying C locale instead.

The results with enable_seqscan off also suggest that random_page_cost
may be too high for your environment.

BTW, the schema you posted does not match these plans --- there are
indexes referenced in the plans that do not appear in the schema.
        regards, tom lane