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

Nice! thanks!

Dave