Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1r41Uf-0072jP-5X for pgsql-general@arkaria.postgresql.org; Fri, 17 Nov 2023 16:14:57 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1r41Ud-00Ef0j-4I for pgsql-general@arkaria.postgresql.org; Fri, 17 Nov 2023 16:14:55 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1r41Ub-00Ef0b-Fy for pgsql-general@lists.postgresql.org; Fri, 17 Nov 2023 16:14:54 +0000 Received: from wout4-smtp.messagingengine.com ([64.147.123.20]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1r41UW-006vc0-QR for pgsql-general@lists.postgresql.org; Fri, 17 Nov 2023 16:14:52 +0000 Received: from compute6.internal (compute6.nyi.internal [10.202.2.47]) by mailout.west.internal (Postfix) with ESMTP id 5507C3200AAE; Fri, 17 Nov 2023 11:14:45 -0500 (EST) Received: from mailfrontend2 ([10.202.2.163]) by compute6.internal (MEProxy); Fri, 17 Nov 2023 11:14:45 -0500 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=aklaver.com; h= cc:content-transfer-encoding:content-type:content-type:date:date :from:from:in-reply-to:in-reply-to:message-id:mime-version :references:reply-to:sender:subject:subject:to:to; s=fm3; t= 1700237684; x=1700324084; bh=m3Rf6o7LoUQCmCPOtVlJBvyktGjdV2jTV9N Qkw9umNw=; b=sMmyqLVu6aEWyipKNxCDqhinAr+3y2zvlqGCIiofgi5w06jN2fn htLFr1nPGwfd4gkniLsXkptiN17AMoHMpEZtRZaOzpc64TTUO6X/cjxlwgcEK92s AYpWcBdFXs+aDwZGjJMzqDzb5MM2XcqhaJZeR2X4f3kKOTreQx3bYff7PMiYsdM7 CwpBmEu1m44S8ApEMVf1GOSy7PnYhpg2f3PqWX+wHqI1wPeKS2r2bV/N5HzhiV22 LLFqYVXNBZa2mZdgQjeNdz4zA4yWrYnJBd0O77qekKHuunfozh2MCtEOPBGRsK5Q kAVUf+8RipFEMQSJqW62/MYIXCGJ9z7aTZg== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:content-transfer-encoding:content-type :content-type:date:date:feedback-id:feedback-id:from:from :in-reply-to:in-reply-to:message-id:mime-version:references :reply-to:sender:subject:subject:to:to:x-me-proxy:x-me-proxy :x-me-sender:x-me-sender:x-sasl-enc; s=fm1; t=1700237684; x= 1700324084; bh=m3Rf6o7LoUQCmCPOtVlJBvyktGjdV2jTV9NQkw9umNw=; b=y 7dnKxDlDaURE+nt3CUPoO7/Ml0iSXQSdUgn5COHX+8yJSGacs1DD+qYEiUjnNi8r f+a9/A9V4V3rKbIs93VRT2Ov692b3LPWz17lLMn6SrWCJJHaD96FvWVvqkf9QS+x rrQ48Tw36ZDdR/ecvi9ZDcHeLJBia0HwTuL7IVeSegDP93IYyrExnFxfARmduz18 tR3vf7Wdm4K2PPcNuU7NXFyn6JQhfO4szQtBTAefhfgFvzwNMU5pn90rILjHiBtQ 3a0cVJ+fAAyzZvd9zI2ZZcOtidMEc1FNlPTNgrnCzssdxmFA19o700OFAEoxN4Ud MZIH2OA/Mcgm5QddPHp+g== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvkedrudegtddgkeeiucetufdoteggodetrfdotf fvucfrrhhofhhilhgvmecuhfgrshhtofgrihhlpdfqfgfvpdfurfetoffkrfgpnffqhgen uceurghilhhouhhtmecufedttdenucenucfjughrpefkffggfgfuvfhfhfgjtgfgsehtke ertddtvdejnecuhfhrohhmpeetughrihgrnhcumfhlrghvvghruceorggurhhirghnrdhk lhgrvhgvrhesrghklhgrvhgvrhdrtghomheqnecuggftrfgrthhtvghrnhepleegveekke ekueeigfdtveeileeuhfefudefteekjeffkeejueejheegheegkedtnecuffhomhgrihhn pehpohhsthhgrhgvshhqlhdrohhrghenucevlhhushhtvghrufhiiigvpedtnecurfgrrh grmhepmhgrihhlfhhrohhmpegrughrihgrnhdrkhhlrghvvghrsegrkhhlrghvvghrrdgt ohhm X-ME-Proxy: Feedback-ID: i76984098:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Fri, 17 Nov 2023 11:14:44 -0500 (EST) Message-ID: Date: Fri, 17 Nov 2023 08:14:43 -0800 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: PostgreSQL statistics To: Paulo Silva , pgsql-general@lists.postgresql.org References: Content-Language: en-US From: Adrian Klaver In-Reply-To: Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 11/17/23 02:25, Paulo Silva wrote: > Hi, > > I have a table in a PostgreSQL 11 server that isn't being used a lot. > > If I run a manual ANALYSE on it the last_analyse statistic isn't being > update (I've waited enough time for it to be updated): > > # SELECT * FROM pg_catalog.pg_stat_user_tables WHERE schemaname='s1' and > relname='t1'; >    relid   |      schemaname      |      relname      | seq_scan | > seq_tup_read | idx_scan | idx_tup_fetch | n_tup_ins | n_tup_upd | > n_tup_del | n_tup_hot_upd | n_live_tup | n_dead_tup | > n_mod_since_analyze | last_vacuum |        last_autovacuum        | > last_analyze | last_autoanalyze | vacuum_count | autovacuum_count | > analyze_count | autoanalyze_count > -----------+----------------------+-------------------+----------+--------------+----------+---------------+-----------+-----------+-----------+---------------+------------+------------+---------------------+-------------+-------------------------------+--------------+------------------+--------------+------------------+---------------+------------------- >  237177743 | s1 | t1 |        0 |            0 |        0 | > 0 |         0 |         0 |         0 |             0 |       4820 | >      0 |                   0 |             | 2023-11-03 > 13:34:23.725802+00 |              |                  |            0 | >              1 |             0 |                 0 > (1 row) > > But if I run a "SELECT * FROM s1.t1"  I see the seq_scan increase to 1 > (as expected) and after that I can issue a manual ANALYZE and the > last_analyze gets updated. > > Is this behaviour normal? Are there some parameters that are verified > that prevent the ANALYSE from running? The oldest Postgres instance I have access to is version 12 and I see the update to pg_stat_user_tables when I run ANALYZE in psql in autocommit. Are you maybe seeing the effects of this?: https://www.postgresql.org/docs/11/monitoring-stats.html "Another important point is that when a server process is asked to display any of these statistics, it first fetches the most recent report emitted by the collector process and then continues to use this snapshot for all statistical views and functions until the end of its current transaction. So the statistics will show static information as long as you continue the current transaction. Similarly, information about the current queries of all sessions is collected when any such information is first requested within a transaction, and the same information will be displayed throughout the transaction." As in: begin; select relname, last_analyze from pg_stat_user_tables where relname = 'cell_per'; -[ RECORD 1 ]+------------------------------ relname | cell_per last_analyze | 2023-11-17 08:11:18.614987-08 analyze cell_per ; ANALYZE select relname, last_analyze from pg_stat_user_tables where relname = 'cell_per'; -[ RECORD 1 ]+------------------------------ relname | cell_per last_analyze | 2023-11-17 08:11:18.614987-08 commit; select relname, last_analyze from pg_stat_user_tables where relname = 'cell_per'; -[ RECORD 1 ]+------------------------------ relname | cell_per last_analyze | 2023-11-17 08:13:02.969537-08 > > Regards > -- > Paulo Silva > -- Adrian Klaver adrian.klaver@aklaver.com