Re: Drop statistics?

On Jul 3, 2012, at 10:16 AM, Bruce Momjian wrote:

> 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  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 
>> - 
>> - > 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.
> 

Nice!  thanks!

Dave