Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 51DB21337B8B for ; Wed, 13 Apr 2011 11:32:45 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 32821-06 for ; Wed, 13 Apr 2011 14:32:30 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from gw.wicourts.gov (gwmta.wicourts.gov [165.219.244.99]) by mail.postgresql.org (Postfix) with ESMTP id DE1901337B67 for ; Wed, 13 Apr 2011 11:32:27 -0300 (ADT) Received: from Courts-MTA by gw.wicourts.gov with Novell_GroupWise; Wed, 13 Apr 2011 09:32:28 -0500 Message-Id: <4DA56DA8020000250003C783@gw.wicourts.gov> X-Mailer: Novell GroupWise Internet Agent 8.0.1 Date: Wed, 13 Apr 2011 09:32:24 -0500 From: "Kevin Grittner" To: "Ogden" ,"Tomas Vondra" Cc: Subject: Re: Performance References: <20110412171855.GA14292@tux> <4DA496FA.3070908@fuzzy.cz> <8F22D592-23C1-4A3C-94A5-48363332ADD3@darkstatic.com> <4DA4BFA2.5060601@fuzzy.cz> <4DA4D40A.4010200@fuzzy.cz> In-Reply-To: Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit Content-Disposition: inline X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.91 tagged_above=-10 required=5 tests=BAYES_00=-1.9, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201104/186 X-Sequence-Number: 43248 Ogden wrote: > In conclusion, I should keep my random_page_cost (3.0) to a value > more than seq_page_cost (1.0)? Is this bad practice or will this > suffice for my setup (where the database is much bigger than the > RAM in the system)? The idea is to adjust the costing factors to model the actual relative costs of the various actions in your environment with your workload. The best way to determine whether your settings are good is to gauge how happy the those using the database are with performance. :-) The degree of caching has a large effect on the page costs. We've managed to keep the active portion of our databases cached to a degree that we have always benefited by reducing the random_page_cost to 2 or less. Where the entire database is cached, we get the best plans with seq_page_cost and random_page_cost set to equal values in the 0.1 to 0.05 range. We've occasionally needed to bump the cpu_tuple_cost up a bit relative to other cpu costs, too. On the other hand, I've seen reports of people who have found it necessary to increase random_page_cost to get good plans. These have been people with large databases where the entire database is "active" (versus our databases where recent, active data is accessed much more heavily than, say, 20 year old data). If you model the costing to reflect the reality on your server, good plans will be chosen. -Kevin