Thread: Drop statistics?
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. It's possible that the existing stats were just not optimal, and i won't be able to get that back. But I just want to verify that what i've done is the only real option that I have? am i missing anything else that I could try? (I'm on PG9.1) Thanks. Dave
David Kerr <dmk@mr-paradox.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
On Fri, Jun 22, 2012 at 01:27:51PM -0400, Tom Lane wrote: - David Kerr <dmk@mr-paradox.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! Dave
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@mr-paradox.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@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
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@mr-paradox.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