X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 489308B9B86 for ; Tue, 1 Feb 2005 21:53:35 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 17266-02 for ; Tue, 1 Feb 2005 21:53:23 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 2AE238B9BD8 for ; Tue, 1 Feb 2005 21:53:24 +0000 (GMT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j11LrNd5019309; Tue, 1 Feb 2005 16:53:23 -0500 (EST) To: "Joel Fradkin" Cc: "'Michael Fuhr'" , pgsql-sql@postgresql.org Subject: Re: MSSQL versus Postgres timing In-reply-to: <000101c50893$e25da5d0$797ba8c0@jfradkin> References: <000101c50893$e25da5d0$797ba8c0@jfradkin> Comments: In-reply-to "Joel Fradkin" message dated "Tue, 01 Feb 2005 14:26:07 -0500" Date: Tue, 01 Feb 2005 16:53:23 -0500 Message-ID: <19308.1107294803@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.006 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200502/35 X-Sequence-Number: 20146 "Joel Fradkin" 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