Re: default_statistics_target

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(at)2ndQuadrant(dot)com www.2ndQuadrant.us