Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id DD3D29FBACD for ; Mon, 14 May 2007 17:53:19 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 96190-04 for ; Mon, 14 May 2007 17:53:14 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from an-out-0708.google.com (an-out-0708.google.com [209.85.132.244]) by postgresql.org (Postfix) with ESMTP id 358DE9FB30A for ; Mon, 14 May 2007 17:53:14 -0300 (ADT) Received: by an-out-0708.google.com with SMTP id d17so449242and for ; Mon, 14 May 2007 13:53:11 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=Iu+lAn+KI+hApWjXRUw+ImEuT3CsUnqYD63N5A1OwG7ei972+MKIT+vJeFNdt4QG1iD+pkCpmg9yMiJR4mlxU/DiqqfxMnVoY0cNeM1AOXxDiRMDnUlmFK/6MKnlbL0zJpffOFThkO1aSLE2nVcj+QMBKLu6HmFugxqnqflUoWc= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=peA8A117KtLuyjFrcshjcjCTHCfzBICH+8xS5C/vETHyN4Uxxpo31gm/aPOZceymCv/Vciw21nA+gfwSqlEHsGtAkLaUTb48mBV3q0GqSSFLRYp7+xJGNgNnwPlZDfcr+KSd4QQhT5r62vPC8oyWB0gUAyGC5DPv7jMp8v2svik= Received: by 10.100.250.7 with SMTP id x7mr4866230anh.1179175991159; Mon, 14 May 2007 13:53:11 -0700 (PDT) Received: by 10.100.6.20 with HTTP; Mon, 14 May 2007 13:53:10 -0700 (PDT) Message-ID: Date: Mon, 14 May 2007 13:53:10 -0700 From: "Y Sidhu" To: "Jim C. Nasby" Subject: Re: pg_stats how-to? Cc: pgsql-performance@postgresql.org In-Reply-To: <20070514190748.GY69517@nasby.net> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_10002_6228454.1179175990929" References: <4647B0A9.7080304@gmail.com> <20070514162610.GO69517@nasby.net> <20070514174558.GS69517@decibel.org> <20070514183615.GW69517@nasby.net> <20070514190748.GY69517@nasby.net> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200705/258 X-Sequence-Number: 24648 ------=_Part_10002_6228454.1179175990929 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline My immediate problem is to decrease vacuum times. Yudhvir ======= On 5/14/07, Jim C. Nasby wrote: > > On Mon, May 14, 2007 at 12:02:03PM -0700, Y Sidhu wrote: > > I am sorry about this Jim, please understand that I am a newbie and am > > trying to solve long vacuum time problems and get a handle on speeding > up > > queries/reports. I was pointed to pg_stats and that's where I am at now. > I > > Well, I have no idea what that person was trying to convey then. What > are you trying to look up? Better yet, what's your actual problem? > > > have added this into my conf file: > > stats_start_collector TRUE stats_reset_on_server_start FALSE > > stats_command_string TRUE > > However, these being production servers, I have not enabled these: > > stats_row_level stats_block_level > FYI, stats_command_string has a far larger performance overhead than any > of the other stats commands prior to 8.2. > > > Yes, I have re-started the server(s). It seems like I query tables to > get > > the info. If so, are there any queries written that I can use? > > > > Thanks for following up on this with me. > > > > Yudhvir > > > > === > > On 5/14/07, Jim C. Nasby wrote: > > > > > >On Mon, May 14, 2007 at 11:09:21AM -0700, Y Sidhu wrote: > > >> The stats_block_level and stats_row_level are NOT enabled. The > question > > >is > > >> how to use pg_stats. Do I access/see them via the ANALYZE command? or > > >using > > >> SQL. I cannot find any document which will get me started on this. > > > > > >Ok, we're both confused I think... I thought you were talking about the > > >pg_stat* views, which depend on the statistics collector (that's what > > >the stats_* parameters control). > > > > > >That actually has nothing at all to do with pg_stats or pg_statistics. > > >Those deal with statistics about the data in the database, and not > about > > >statistics from the engine (which is what the pg_stat* views do...). > > > > > >If you want to know about pg_stats, take a look at > > >http://www.postgresql.org/docs/8.2/interactive/view-pg-stats.html ... > > >but normally you shouldn't need to worry yourself about that. Are you > > >trying to debug something? > > > > > >Information about the backend statistics can be found at > > >http://www.postgresql.org/docs/8.2/interactive/monitoring.html > > >-- > > >Jim Nasby decibel@decibel.org > > >EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) > > > > > > > > > > > -- > > Yudhvir Singh Sidhu > > 408 375 3134 cell > > -- > Jim Nasby decibel@decibel.org > EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) > -- Yudhvir Singh Sidhu 408 375 3134 cell ------=_Part_10002_6228454.1179175990929 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline My immediate problem is to decrease vacuum times.

Yudhvir
=======

On 5/14/07, Jim C. Nasby <decibel@decibel.org> wrote:
On Mon, May 14, 2007 at 12:02:03PM -0700, Y Sidhu wrote:
> I am sorry about this Jim, please understand that I am a newbie and am
> trying to solve long vacuum time problems and get a handle on speeding up
> queries/reports. I was pointed to pg_stats and that's where I am at now. I

Well, I have no idea what that person was trying to convey then. What
are you trying to look up? Better yet, what's your actual problem?

> have added this into my conf file:
>  stats_start_collector TRUE  stats_reset_on_server_start FALSE
> stats_command_string TRUE
> However, these being production servers, I have not enabled these:
>  stats_row_level  stats_block_level
FYI, stats_command_string has a far larger performance overhead than any
of the other stats commands prior to 8.2.

> Yes, I have re-started the server(s). It seems like I query tables to get
> the info. If so, are there any queries written that I can use?
>
> Thanks for following up on this with me.
>
> Yudhvir
>
> ===
> On 5/14/07, Jim C. Nasby < decibel@decibel.org> wrote:
> >
> >On Mon, May 14, 2007 at 11:09:21AM -0700, Y Sidhu wrote:
> >> The stats_block_level and stats_row_level are NOT enabled. The question
> >is
> >> how to use pg_stats. Do I access/see them via the ANALYZE command? or
> >using
> >> SQL. I cannot find any document which will get me started on this.
> >
> >Ok, we're both confused I think... I thought you were talking about the
> >pg_stat* views, which depend on the statistics collector (that's what
> >the stats_* parameters control).
> >
> >That actually has nothing at all to do with pg_stats or pg_statistics.
> >Those deal with statistics about the data in the database, and not about
> >statistics from the engine (which is what the pg_stat* views do...).
> >
> >If you want to know about pg_stats, take a look at
> >http://www.postgresql.org/docs/8.2/interactive/view-pg-stats.html ...
> >but normally you shouldn't need to worry yourself about that. Are you
> >trying to debug something?
> >
> >Information about the backend statistics can be found at
> >http://www.postgresql.org/docs/8.2/interactive/monitoring.html
> >--
> >Jim Nasby                                      decibel@decibel.org
> >EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)
> >
>
>
>
> --
> Yudhvir Singh Sidhu
> 408 375 3134 cell

--
Jim Nasby                                      decibel@decibel.org
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)



--
Yudhvir Singh Sidhu
408 375 3134 cell ------=_Part_10002_6228454.1179175990929--