pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedMSSQL versus Postgres timing
11+ messages / 6 participants
[nested] [flat]
* MSSQL versus Postgres timing
@ 2005-02-01 16:54 Joel Fradkin <jfradkin@wazagua.com>
2005-02-01 16:59 ` Re: MSSQL versus Postgres timing Scott Marlowe <smarlowe@g2switchworks.com>
2005-02-01 17:06 ` Re: MSSQL versus Postgres timing Michael Fuhr <mike@fuhr.org>
0 siblings, 2 replies; 11+ messages in thread
From: Joel Fradkin @ 2005-02-01 16:54 UTC (permalink / raw)
To: pgsql-sql
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')
This is taking 13 seconds in postgres and 3 seconds in MSSQL.
I tried making an index on clientnum (there is one on clinetnum, associateID
in MSSQL).
I did an analyze.
I did a set seq scan off
Is there a possibility postgres is this much slower, or (my hope) I am
missing some key concept.
Be glad to provide the view and tables etc.
Joel Fradkin
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: MSSQL versus Postgres timing
2005-02-01 16:54 MSSQL versus Postgres timing Joel Fradkin <jfradkin@wazagua.com>
@ 2005-02-01 16:59 ` Scott Marlowe <smarlowe@g2switchworks.com>
2005-02-01 19:23 ` Re: MSSQL versus Postgres timing Joel Fradkin <jfradkin@wazagua.com>
1 sibling, 1 reply; 11+ messages in thread
From: Scott Marlowe @ 2005-02-01 16:59 UTC (permalink / raw)
To: Joel Fradkin <jfradkin@wazagua.com>; +Cc: pgsql-sql
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?
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: MSSQL versus Postgres timing
2005-02-01 16:54 MSSQL versus Postgres timing Joel Fradkin <jfradkin@wazagua.com>
2005-02-01 16:59 ` Re: MSSQL versus Postgres timing Scott Marlowe <smarlowe@g2switchworks.com>
@ 2005-02-01 19:23 ` Joel Fradkin <jfradkin@wazagua.com>
0 siblings, 0 replies; 11+ messages in thread
From: Joel Fradkin @ 2005-02-01 19:23 UTC (permalink / raw)
To: 'Scott Marlowe' <smarlowe@g2switchworks.com>; +Cc: pgsql-sql
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?
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: MSSQL versus Postgres timing
2005-02-01 16:54 MSSQL versus Postgres timing Joel Fradkin <jfradkin@wazagua.com>
@ 2005-02-01 17:06 ` Michael Fuhr <mike@fuhr.org>
2005-02-01 17:25 ` Re: MSSQL versus Postgres timing Bricklen Anderson <BAnderson@PresiNET.com>
2005-02-01 19:26 ` Re: MSSQL versus Postgres timing Joel Fradkin <jfradkin@wazagua.com>
2005-02-01 20:19 ` Re: MSSQL versus Postgres timing Joel Fradkin <jfradkin@wazagua.com>
2005-02-01 20:19 ` Re: MSSQL versus Postgres timing Joel Fradkin <jfradkin@wazagua.com>
1 sibling, 4 replies; 11+ messages in thread
From: Michael Fuhr @ 2005-02-01 17:06 UTC (permalink / raw)
To: Joel Fradkin <jfradkin@wazagua.com>; +Cc: pgsql-sql
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/
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: MSSQL versus Postgres timing
2005-02-01 16:54 MSSQL versus Postgres timing Joel Fradkin <jfradkin@wazagua.com>
2005-02-01 17:06 ` Re: MSSQL versus Postgres timing Michael Fuhr <mike@fuhr.org>
@ 2005-02-01 17:25 ` Bricklen Anderson <BAnderson@PresiNET.com>
3 siblings, 0 replies; 11+ messages in thread
From: Bricklen Anderson @ 2005-02-01 17:25 UTC (permalink / raw)
To: ; +Cc: Joel Fradkin <jfradkin@wazagua.com>; pgsql-sql
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?
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: MSSQL versus Postgres timing
2005-02-01 16:54 MSSQL versus Postgres timing Joel Fradkin <jfradkin@wazagua.com>
2005-02-01 17:06 ` Re: MSSQL versus Postgres timing Michael Fuhr <mike@fuhr.org>
@ 2005-02-01 19:26 ` Joel Fradkin <jfradkin@wazagua.com>
2005-02-01 21:53 ` Re: MSSQL versus Postgres timing Tom Lane <tgl@sss.pgh.pa.us>
3 siblings, 1 reply; 11+ messages in thread
From: Joel Fradkin @ 2005-02-01 19:26 UTC (permalink / raw)
To: 'Michael Fuhr' <mike@fuhr.org>; +Cc: pgsql-sql
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/
Attachments:
explainanalyzeviwassoclist.txt (text/plain, 1.6K) download
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"
* Re: MSSQL versus Postgres timing
2005-02-01 16:54 MSSQL versus Postgres timing Joel Fradkin <jfradkin@wazagua.com>
2005-02-01 17:06 ` Re: MSSQL versus Postgres timing Michael Fuhr <mike@fuhr.org>
2005-02-01 19:26 ` Re: MSSQL versus Postgres timing Joel Fradkin <jfradkin@wazagua.com>
@ 2005-02-01 21:53 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-02-01 22:09 ` Re: MSSQL versus Postgres timing Joel Fradkin <jfradkin@wazagua.com>
0 siblings, 1 reply; 11+ messages in thread
From: Tom Lane @ 2005-02-01 21:53 UTC (permalink / raw)
To: Joel Fradkin <jfradkin@wazagua.com>; +Cc: 'Michael Fuhr' <mike@fuhr.org>; pgsql-sql
"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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: MSSQL versus Postgres timing
2005-02-01 16:54 MSSQL versus Postgres timing Joel Fradkin <jfradkin@wazagua.com>
2005-02-01 17:06 ` Re: MSSQL versus Postgres timing Michael Fuhr <mike@fuhr.org>
2005-02-01 19:26 ` Re: MSSQL versus Postgres timing Joel Fradkin <jfradkin@wazagua.com>
2005-02-01 21:53 ` Re: MSSQL versus Postgres timing Tom Lane <tgl@sss.pgh.pa.us>
@ 2005-02-01 22:09 ` Joel Fradkin <jfradkin@wazagua.com>
0 siblings, 0 replies; 11+ messages in thread
From: Joel Fradkin @ 2005-02-01 22:09 UTC (permalink / raw)
To: 'Tom Lane' <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
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.locationid and
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, Inc
This email message is for the use of the intended recipient(s) and may
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: MSSQL versus Postgres timing
2005-02-01 16:54 MSSQL versus Postgres timing Joel Fradkin <jfradkin@wazagua.com>
2005-02-01 17:06 ` Re: MSSQL versus Postgres timing Michael Fuhr <mike@fuhr.org>
@ 2005-02-01 20:19 ` Joel Fradkin <jfradkin@wazagua.com>
2005-02-01 21:47 ` Re: MSSQL versus Postgres timing PFC <lists@boutiquenumerique.com>
3 siblings, 1 reply; 11+ messages in thread
From: Joel Fradkin @ 2005-02-01 20:19 UTC (permalink / raw)
To: pgsql-sql
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@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/
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: MSSQL versus Postgres timing
2005-02-01 16:54 MSSQL versus Postgres timing Joel Fradkin <jfradkin@wazagua.com>
2005-02-01 17:06 ` Re: MSSQL versus Postgres timing Michael Fuhr <mike@fuhr.org>
2005-02-01 20:19 ` Re: MSSQL versus Postgres timing Joel Fradkin <jfradkin@wazagua.com>
@ 2005-02-01 21:47 ` PFC <lists@boutiquenumerique.com>
0 siblings, 0 replies; 11+ messages in thread
From: PFC @ 2005-02-01 21:47 UTC (permalink / raw)
To: Joel Fradkin <jfradkin@wazagua.com>; pgsql-sql
> 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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: MSSQL versus Postgres timing
2005-02-01 16:54 MSSQL versus Postgres timing Joel Fradkin <jfradkin@wazagua.com>
2005-02-01 17:06 ` Re: MSSQL versus Postgres timing Michael Fuhr <mike@fuhr.org>
@ 2005-02-01 20:19 ` Joel Fradkin <jfradkin@wazagua.com>
3 siblings, 0 replies; 11+ messages in thread
From: Joel Fradkin @ 2005-02-01 20:19 UTC (permalink / raw)
To: pgsql-sql
-----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/
Attachments:
explainanalyzeviwassoclist_noseqscan.txt (text/plain, 1.6K) download
QUERY PLAN
"Merge Join (cost=109454.32..109940.16 rows=3758 width=111) (actual time=11646.990..13474.449 rows=85694 loops=1)"
" Merge Cond: (""outer"".locationid = ""inner"".locationid)"
" -> Sort (cost=1168.37..1169.15 rows=312 width=48) (actual time=35.359..36.651 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=29.811..33.415 rows=402 loops=1)"
" Index Cond: ('SAKS'::text = (clientnum)::text)"
" -> Sort (cost=108285.96..108499.98 rows=85611 width=74) (actual time=11611.560..11995.898 rows=85695 loops=1)"
" Sort Key: a.locationid"
" -> Merge Right Join (cost=100083.75..101272.06 rows=85611 width=74) (actual time=7758.824..10598.571 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=0.089..30.193 rows=5662 loops=1)"
" Filter: (1 = presentationid)"
" -> Sort (cost=100083.75..100297.77 rows=85611 width=52) (actual time=7709.988..8543.451 rows=99139 loops=1)"
" Sort Key: (a.clientnum)::text, a.jobtitleid"
" -> Index Scan using ix_associate_clientnum on tblassociate a (cost=0.00..93069.85 rows=85611 width=52) (actual time=0.281..2046.482 rows=99139 loops=1)"
" Index Cond: ((clientnum)::text = 'SAKS'::text)"
"Total runtime: 13899.614 ms"
=
end of thread, other threads:[~2005-02-01 22:09 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-02-01 16:54 MSSQL versus Postgres timing Joel Fradkin <jfradkin@wazagua.com>
2005-02-01 16:59 ` Scott Marlowe <smarlowe@g2switchworks.com>
2005-02-01 19:23 ` Joel Fradkin <jfradkin@wazagua.com>
2005-02-01 17:06 ` Michael Fuhr <mike@fuhr.org>
2005-02-01 17:25 ` Bricklen Anderson <BAnderson@PresiNET.com>
2005-02-01 19:26 ` Joel Fradkin <jfradkin@wazagua.com>
2005-02-01 21:53 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-02-01 22:09 ` Joel Fradkin <jfradkin@wazagua.com>
2005-02-01 20:19 ` Joel Fradkin <jfradkin@wazagua.com>
2005-02-01 21:47 ` PFC <lists@boutiquenumerique.com>
2005-02-01 20:19 ` Joel Fradkin <jfradkin@wazagua.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox