From: Greg Smith <greg@2ndquadrant.com>
To: Carlo Stonebanks <stonec.register@sympatico.ca>
Cc: pgsql-performance@postgresql.org
Subject: Re: default_statistics_target
Date: Mon, 15 Mar 2010 09:18:39 -0400
Message-ID: <4B9E33AF.2020608@2ndquadrant.com> (raw)
In-Reply-To: <hnjrcs$2trl$1@news.hub.org>
References: <hnjrcs$2trl$1@news.hub.org>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: greg@2ndquadrant.com, stonec.register@sympatico.ca
Subject: Re: default_statistics_target
In-Reply-To: <4B9E33AF.2020608@2ndquadrant.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox