Date: Tue, 27 Sep 2016 14:01:53 +0000 (UTC)
From: Glyn Astill <glynastill@yahoo.co.uk>
Reply-To: Glyn Astill <glynastill@yahoo.co.uk>
To: Thomas SIMON <tsimon@neteven.com>,
	"pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
Message-ID: <1136485488.10643672.1474984913094@mail.yahoo.com>
In-Reply-To: <a028c320-32bc-612a-e656-30ee153994b3@neteven.com>
References: <a028c320-32bc-612a-e656-30ee153994b3@neteven.com>
Subject: Re: Wrong stat on pg_stat_user_tables
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit
Content-Length: 1502
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org


> From: Thomas SIMON <tsimon@neteven.com>
>To: "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org> 
>Sent: Tuesday, 27 September 2016, 9:20
>Subject: [ADMIN] Wrong stat on pg_stat_user_tables
> 
>
>Hi all,
>
>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. (25M 
>vs 111M)
>
>
>  - select n_live_tup, last_autoanalyze from pg_stat_user_tables where 
>relname = 'items'
>
>n_live_tup : 111 191 791
>last_autoanalyze : 2016-09-27 04:03:35.09233+02
>
>
>  - select count (1) from items;
>   count
>----------
>  25307071
>
>
>I've comparated my 10 biggest tables, only this table gave me wrong results.
>
>Does anyone have an idea about it ?
>

Do you have any long running transactions?  The value returned by n_live_tup is an estimate of all the live rows in the table, vs the value you see from count is the number rows visible to the current transaction.


How long between sending the email and running the queries? What output do you see if you analyze the table? And what pg version is this?

I think (I may be wrong) the value you see in pg_stat_user_tables is only an estimate based on a sample of tuples per page in the table, so if you've an uneven distribution of live rows you could see an incorrect value.


What do the planner stats show you? Try:

   select reltuples::integer from pg_class where oid = 'items'::regclass;


You might get a better response from the pgsql-general list.

Glyn


-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin