Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1bosv6-0005I2-7Z for pgsql-admin@arkaria.postgresql.org; Tue, 27 Sep 2016 13:59:40 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1bosv5-0000zR-1f for pgsql-admin@arkaria.postgresql.org; Tue, 27 Sep 2016 13:59:39 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1bostO-0006lO-B9 for pgsql-admin@postgresql.org; Tue, 27 Sep 2016 13:57:54 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1bostL-0002ea-MV for pgsql-admin@postgresql.org; Tue, 27 Sep 2016 13:57:53 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.4/8.14.4) with ESMTP id u8RDvm1T031469; Tue, 27 Sep 2016 09:57:49 -0400 From: Tom Lane To: Thomas SIMON cc: "pgsql-admin@postgresql.org" Subject: Re: Wrong stat on pg_stat_user_tables In-reply-to: References: Comments: In-reply-to Thomas SIMON message dated "Tue, 27 Sep 2016 10:20:59 +0200" Date: Tue, 27 Sep 2016 09:57:48 -0400 Message-ID: <31468.1474984668@sss.pgh.pa.us> X-Pg-Spam-Score: -4.9 (----) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-admin Precedence: bulk Sender: pgsql-admin-owner@postgresql.org Thomas SIMON writes: > I have strange behavior with a stat of pg_stat_user_tables (explained > below), where number of live tuples is not the real number at all. > ... > I've comparated my 10 biggest tables, only this table gave me wrong results. The pgstats stats are only approximate, but ideally they'd converge towards reality over time when they're wrong. Seems like maybe this one table has a usage pattern that's odd enough to throw things off. Maybe you have unusual autovacuum/autoanalyze parameters for it, for example? Is there anything strange about the way you insert/update/delete in it? A simpler explanation might be that this table has inheritance children --- the pgstats count would consider only the table itself, but "select count(1) from items" would scan the child tables too. regards, tom lane -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin