X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id AE3C75445B for ; Thu, 3 Mar 2005 17:36:15 +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 42046-02 for ; Thu, 3 Mar 2005 17:36:06 +0000 (GMT) Received: from davinci.ethosmedia.com (server227.ethosmedia.com [209.128.84.227]) by svr1.postgresql.org (Postfix) with ESMTP id C8BD252A8D for ; Thu, 3 Mar 2005 17:36:05 +0000 (GMT) X-EthosMedia-Virus-Scanned: no infections found Received: from [63.195.55.98] (account josh@agliodbs.com HELO spooky) by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.1.8) with ESMTP id 7088298; Thu, 03 Mar 2005 09:37:53 -0800 From: Josh Berkus Organization: Aglio Database Solutions To: "Ken Egervari" Subject: Re: Help with tuning this query (with explain analyze finally) Date: Thu, 3 Mar 2005 09:35:14 -0800 User-Agent: KMail/1.6.2 Cc: References: <001001c51ef4$3849eeb0$6a01a8c0@KEN> <200503022152.13211.josh@agliodbs.com> <004601c51fbe$828bbb50$6a01a8c0@KEN> In-Reply-To: <004601c51fbe$828bbb50$6a01a8c0@KEN> MIME-Version: 1.0 Content-Disposition: inline Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Message-Id: <200503030935.14537.josh@agliodbs.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.011 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200503/57 X-Sequence-Number: 10762 Ken, Well, I'm a bit stumped on troubleshooting the actual query since Windows' poor time resolution makes it impossible to trust the actual execution times. Obviously this is something we need to look into for the Win32 port for 8.1 .. > shared_buffers = 1000 This may be slowing up that merge join. Try resetting it to 6000. I'm not sure what system settings you might have to do on Windows to get it to support higher shared buffers; see the docs. > #work_mem = 1024 Up this to 4096 for testing purposes; your production value will vary depending on several factors; see link below. > #effective_cache_size = 1000 Increase this to the actual amount of RAM you have available, about 750MB (you do the math) > #random_page_cost = 4 Leave this for now. See www.powerpostgresql.com/PerfList for more information. -- Josh Berkus Aglio Database Solutions San Francisco