Re: Drop statistics?

On Fri, Jun 22, 2012 at 11:04:36AM -0700, David Kerr wrote:
> On Fri, Jun 22, 2012 at 01:27:51PM -0400, Tom Lane wrote:
> - David Kerr <dmk(at)mr-paradox(dot)net> writes:
> - > I'm trying to work through a root cause on a performance problem. I'd like to
> - > be able to "show" that a problem was fixed by analyzing the table.
> -
> - > what i've done is
> - > set default_statistics_target=1
> - > analyze <Table>
> -
> - > That gets rid of most of the rows in pg_stats, but i'm still getting decent performance.
> -
> - I usually do something like
> -
> - DELETE FROM pg_statistic WHERE starelid = 'foo'::regclass;
> -
> - (you need to be superuser to be allowed to do this).
> -
> - You may need to keep an eye on whether auto-analyze is coming along and
> - undoing what you did, too.
> -
> - regards, tom lane
> -
>
> Awesome, thanks!

One cool trick I have seen is to do the DELETE pg_statistic in a multi-statement
transaction and then run query query, and roll it back. This allows the
statistics to be preserved, and for only your query to see empty
pg_statistic values for the table.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +