From: Joel Fradkin <jfradkin@wazagua.com>
To: 'Tom Lane' <tgl@sss.pgh.pa.us>
Cc: pgsql-sql@postgresql.org
Subject: Re: MSSQL versus Postgres timing
Date: Tue, 1 Feb 2005 17:09:13 -0500
Message-ID: <000e01c508aa$aab7c540$797ba8c0@jfradkin> (raw)
In-Reply-To: <19308.1107294803@sss.pgh.pa.us>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jfradkin@wazagua.com, tgl@sss.pgh.pa.us
Subject: Re: MSSQL versus Postgres timing
In-Reply-To: <000e01c508aa$aab7c540$797ba8c0@jfradkin>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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