Re: MSSQL versus Postgres timing - Mailing list pgsql-sql
From | Joel Fradkin |
---|---|
Subject | Re: MSSQL versus Postgres timing |
Date | |
Msg-id | 000e01c508aa$aab7c540$797ba8c0@jfradkin Whole thread Raw |
In response to | Re: MSSQL versus Postgres timing (Tom Lane <tgl@sss.pgh.pa.us>) |
List | 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.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