From: Glyn Astill <glynastill@yahoo.co.uk>
To: Thomas SIMON <tsimon@neteven.com>
To: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: Re: Wrong stat on pg_stat_user_tables
Date: Tue, 27 Sep 2016 14:01:53 +0000 (UTC)
Message-ID: <1136485488.10643672.1474984913094@mail.yahoo.com> (raw)
In-Reply-To: <a028c320-32bc-612a-e656-30ee153994b3@neteven.com>
References: <a028c320-32bc-612a-e656-30ee153994b3@neteven.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
> 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
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 using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: glynastill@yahoo.co.uk, tsimon@neteven.com
Subject: Re: Wrong stat on pg_stat_user_tables
In-Reply-To: <1136485488.10643672.1474984913094@mail.yahoo.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