Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.92) (envelope-from ) id 1j5wsm-00034R-Ay for pgsql-general@arkaria.postgresql.org; Sun, 23 Feb 2020 19:25:41 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1j5wsj-0003fW-Lj for pgsql-general@arkaria.postgresql.org; Sun, 23 Feb 2020 19:25:37 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1j5wsh-0003eJ-Ee; Sun, 23 Feb 2020 19:25:37 +0000 Received: from wout2-smtp.messagingengine.com ([64.147.123.25]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1j5wsd-0000QD-Aa; Sun, 23 Feb 2020 19:25:33 +0000 Received: from compute6.internal (compute6.nyi.internal [10.202.2.46]) by mailout.west.internal (Postfix) with ESMTP id 887C05D0; Sun, 23 Feb 2020 14:25:29 -0500 (EST) Received: from mailfrontend2 ([10.202.2.163]) by compute6.internal (MEProxy); Sun, 23 Feb 2020 14:25:29 -0500 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=aklaver.com; h= subject:from:to:cc:references:message-id:date:mime-version :in-reply-to:content-type:content-transfer-encoding; s=fm2; bh=k AAv0z95SZurqDOVO3edhT5mc7Y16W+CufTJNIWgmw0=; b=mwGn8c1WZkGDj+Svw 86pJBThV4Z251uBTO1DrGSedIqASToQB7YjlHLO8w23S11Qir1rGIa+coTaUVjBA t5KYd6oSZXImX1vR7RlYpe0SHPP9eDftZQ+z/T4EQA2uP/ZXoVvSg6lfJFH54mTG 5Crocf+JVslyJZueTidG7qpH+IsZt9JtHuM7QufCIw7FnKOwQyw3nBoti2A2r9q7 vzZyAnTvGJNGhORYNF4TrIojFKLCkGVpXSkUjAwOOr13692FdkvtCJDzwFjWQuvv 96hgonprNsodPh72SMxhV+r1wOAkFBggH3IF6RCWVVX8tAwL7AArVbN6+OFFo70D TilNg== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:content-transfer-encoding:content-type :date:from:in-reply-to:message-id:mime-version:references :subject:to:x-me-proxy:x-me-proxy:x-me-sender:x-me-sender :x-sasl-enc; s=fm2; bh=kAAv0z95SZurqDOVO3edhT5mc7Y16W+CufTJNIWgm w0=; b=v+j8aSY5cmodIEaAOtx994WpxkMmD5B+TMYjcjwJmTpQofFfsv2nfKze1 fbZsKKPsBIVVFdjdwQucn1lVagE3cE0CEZzJ7hT3bLmzkOGdnvOmmJoMQHDzzMvK XlH3He/TToznMvrQW7Q9SLJgqUQfWzSy2Zzj/RP+9qD7ANmCbGgCYWDZC9XJFY7J qugePGiBZON2554LC1BynGPcYpqUdFeyfgYQWHK6nYOWCvXhdgFxO4o2qsg46hyP 8Qme+pzcIamEKGJDfhUpfwgJUTRSfqCZhUGb17xk7I2qUxxnNJeM6H2S9toPF/Mx hpJmrWid5HRZNg25ybgURA6b+ol3g== X-ME-Sender: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedugedrkeekgdduvdegucetufdoteggodetrfdotf fvucfrrhhofhhilhgvmecuhfgrshhtofgrihhlpdfqfgfvpdfurfetoffkrfgpnffqhgen uceurghilhhouhhtmecufedttdenucesvcftvggtihhpihgvnhhtshculddquddttddmne cujfgurhepuffhvfhfkffffgggjggtgfesthekredttdefjeenucfhrhhomheptegurhhi rghnucfmlhgrvhgvrhcuoegrughrihgrnhdrkhhlrghvvghrsegrkhhlrghvvghrrdgtoh hmqeenucffohhmrghinhepphhoshhtghhrvghsqhhlrdhorhhgnecukfhppeejuddrvddu vddruddvledrudelfeenucevlhhushhtvghrufhiiigvpedtnecurfgrrhgrmhepmhgrih hlfhhrohhmpegrughrihgrnhdrkhhlrghvvghrsegrkhhlrghvvghrrdgtohhm X-ME-Proxy: Received: from [192.168.1.10] (unknown [71.212.129.193]) by mail.messagingengine.com (Postfix) with ESMTPA id 764CF3060D1A; Sun, 23 Feb 2020 14:25:28 -0500 (EST) Subject: Re: Reset DB stats suggestion pg_stat_reset() From: Adrian Klaver To: postggen2020 s Cc: pgsql-general@postgresql.org, pgsql-admin@postgresql.org References: Message-ID: <78fe4c74-0dfe-aea8-9d27-9d5c06c3f5c1@aklaver.com> Date: Sun, 23 Feb 2020 11:25:27 -0800 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:68.0) Gecko/20100101 Thunderbird/68.5.0 MIME-Version: 1.0 In-Reply-To: Content-Type: text/plain; charset=utf-8; format=flowed Content-Language: en-US Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk 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 >> > 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 >> > > -- Adrian Klaver adrian.klaver@aklaver.com