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
[EMAIL PROTECTED]
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:[EMAIL PROTECTED]
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" <[EMAIL PROTECTED]> 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
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
