Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 3B9286435F1 for ; Mon, 15 Mar 2010 10:18:57 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 36554-01 for ; Mon, 15 Mar 2010 13:18:46 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from moutng.kundenserver.de (moutng.kundenserver.de [212.227.17.8]) by mail.postgresql.org (Postfix) with ESMTP id 8C81D643630 for ; Mon, 15 Mar 2010 10:18:46 -0300 (ADT) Received: from [192.168.1.6] (pool-96-244-14-10.bltmmd.fios.verizon.net [96.244.14.10]) by mrelayeu.kundenserver.de (node=mrbap1) with ESMTP (Nemesis) id 0Lo3Ka-1NBUiH0NkO-00frmk; Mon, 15 Mar 2010 14:18:42 +0100 Message-ID: <4B9E33AF.2020608@2ndquadrant.com> Date: Mon, 15 Mar 2010 09:18:39 -0400 From: Greg Smith User-Agent: Thunderbird 2.0.0.23 (X11/20090817) MIME-Version: 1.0 To: Carlo Stonebanks CC: pgsql-performance@postgresql.org Subject: Re: default_statistics_target References: In-Reply-To: Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Provags-ID: V01U2FsdGVkX18etc85h6zrH5SgODWSYWcg7E4KyWO96rIaA4U dslI3nTPWvcvwusWnvarrww61FuJ1/FSsLvsc8JivoH5m52tNi PnYSqVwpEbt8XT+0fhJbGR/1C9cWpHC X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.599 tagged_above=-10 required=5 tests=BAYES_00=-2.599 X-Spam-Level: X-Archive-Number: 201003/139 X-Sequence-Number: 37964 Carlo Stonebanks wrote: > The whole topic of messing with stats makes my head spin but I am > concerned about some horridly performing queries that have had bad > rows estimates and others which always choose seq scans when indexes > are available. Reading up on how to improve planner estimates, I have > seen references to default_statistics_target being changed from the > default of 10 to 100. > > Our DB is large, with thousands of tables Stop right there for a second. Are you sure autovacuum is working well here? With thousands of tables, it wouldn't surprise me to discover your planner estimates are wrong because there hasn't been a recent enough ANALYZE on the relevant tables. If you haven't already, take a look at pg_stat_user_tables and make sure that tables that have the bad estimates have actually been analyzed recently. A look at the live/dead row counts there should be helpful as well. If all that's recent, but you're still getting bad estimates, only then would I suggest trying an increase to default_statistics_target. In the situation where autovacuum isn't keeping up with some tables because you have thousands of them, increasing the stats target can actually make the problem worse, because the tables that are getting analyzed will take longer to process--more statistics work to be done per table. Given that it looks like you're running 8.3 from past messages I've seen from you, I'd also be concerned that you've overrun your max_fsm_pages, so that VACUUM is growing increasing ineffective for you, and that's contributing to your headache. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support greg@2ndQuadrant.com www.2ndQuadrant.us