pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Adrian Klaver <adrian.klaver@aklaver.com>
To: postggen2020 s <postggen2020@gmail.com>
Cc: pgsql-general@postgresql.org
Cc: pgsql-admin@postgresql.org
Subject: Re: Reset DB stats suggestion pg_stat_reset()
Date: Sun, 23 Feb 2020 11:25:27 -0800
Message-ID: <78fe4c74-0dfe-aea8-9d27-9d5c06c3f5c1@aklaver.com> (raw)
In-Reply-To: <ca20d549-5ad1-b83a-b169-1f436a9e10e2@aklaver.com>
References: <CAJ=omAg4ZaCpC4gaE8GM8w7vHf+gVF1z_TYCjV=koAaqaF23Cw@mail.gmail.com>
	<e9e9e02d-7df5-60e9-c101-3208716bc00d@aklaver.com>
	<CAJ=omAhbyW3V5vxjcHqeD9MPkGipsvJcviVEjALamwN5C2BvMg@mail.gmail.com>
	<ca20d549-5ad1-b83a-b169-1f436a9e10e2@aklaver.com>

On 2/23/20 10:19 AM, Adrian Klaver wrote:
> On 2/22/20 6:40 PM, postggen2020 s wrote:
>> Thanks Adrian.
>> I am aware about the functions. Here need is, can we use this?.or is 
>> there any known effects after firing the functions?.
> 
> Yes the stats counters will be reset to 0, which means the planner will 
> not have accurate statistics to work with. That can be corrected with an 
> ANALYZE. 

I should have listened to the little voice that told me to confirm the 
above. The reset will reset the counters in the tables listed here:

https://www.postgresql.org/docs/12/monitoring-stats.html#MONITORING-STATS-VIEWS

It does not change the statistics here:

https://www.postgresql.org/docs/12/catalog-pg-statistic.html

Therefore an ANALYZE is not strictly required.

The questions below still hold though.

The questions I have are:
> 
> What is the issue you are trying to deal with?
> 
> Also why do you think resetting the stats will fix it?
> 
> 
>>
>> Thanks,
>> Postgann.
>>
>> On Sat, Feb 22, 2020 at 10:47 PM Adrian Klaver 
>> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:
>>
>>     On 2/22/20 12:26 AM, postggen2020 s wrote:
>>      > Hi Team,
>>      >
>>      > Good Afternoon,
>>      >
>>      > We have seen some deadlocks and tempfile count in
>>     pg_stat_database view.
>>      > We are trying to reset the stats.
>>      > Can we use pg_stat_reset() function to reset these stats 
>> without any
>>      > impact stats of databases.
>>      > Please advise the process to reset the stats.
>>
>>     See here:
>>
>>     https://www.postgresql.org/docs/12/monitoring-stats.html
>>
>>     Table 27.20. Additional Statistics Functions
>>
>>     It details what your options are.
>>
>>      >
>>      > Thanks.
>>      > Postggen.
>>
>>
>>     --     Adrian Klaver
>>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>>
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com





view thread (6+ messages)  latest in thread

Message-ID: <78fe4c74-0dfe-aea8-9d27-9d5c06c3f5c1@aklaver.com>
Permalink:  ../78fe4c74-0dfe-aea8-9d27-9d5c06c3f5c1@aklaver.com/
Also on:    postgresql.org/message-id/78fe4c74-0dfe-aea8-9d27-9d5c06c3f5c1@aklaver.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: adrian.klaver@aklaver.com, postggen2020@gmail.com, pgsql-general@postgresql.org
  Subject: Re: Reset DB stats suggestion pg_stat_reset()
  In-Reply-To: <78fe4c74-0dfe-aea8-9d27-9d5c06c3f5c1@aklaver.com>
  References: <CAJ=omAg4ZaCpC4gaE8GM8w7vHf+gVF1z_TYCjV=koAaqaF23Cw@mail.gmail.com> <e9e9e02d-7df5-60e9-c101-3208716bc00d@aklaver.com> <CAJ=omAhbyW3V5vxjcHqeD9MPkGipsvJcviVEjALamwN5C2BvMg@mail.gmail.com> <ca20d549-5ad1-b83a-b169-1f436a9e10e2@aklaver.com> <78fe4c74-0dfe-aea8-9d27-9d5c06c3f5c1@aklaver.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox