From: Thomas SIMON <tsimon@neteven.com>
To: Glyn Astill <glynastill@yahoo.co.uk>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: Re: Wrong stat on pg_stat_user_tables
Date: Wed, 5 Oct 2016 12:14:36 +0200
Message-ID: <f48c07da-8069-dd3b-f383-3cead96318be@neteven.com> (raw)
In-Reply-To: <31468.1474984668@sss.pgh.pa.us>
References: <a028c320-32bc-612a-e656-30ee153994b3@neteven.com>
<31468.1474984668@sss.pgh.pa.us>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
Hi guys,
Thanks for your replies, and sorry for the late answer.
I have grouped your messages to have more visibility.
> 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.
no, I havent got particulary long transactions on this table, or at
least not more than on other tables
> 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?
If I analyse table, I have following output :
analyse verbose items;
INFO: analyzing "public.items"
INFO: "items": scanned 30000 of 11676017 pages, containing 65181 live
rows and 132481 dead rows; 30000 rows in sample, 89400829 estimated
total rows
ANALYZE
It is 9.5 postgresql version
>
> 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.
I think you hit something, indeed this is a sample, and indeed when I
see postgres's answer, I see something wrong, I see way too much dead
rows, and planner is probably cheated by this.
I've made some search about analyze, and seen that we can tell to
analyze to use greater sample, so I tried (x4) but I have
approximatively the same result.
#analyse verbose items;
INFO: analyzing "public.items"
INFO: "items": scanned 30000 of 11676017 pages, containing 65853 live
rows and 133172 dead rows; 30000 rows in sample, 89236981 estimated
total rows
ANALYZE
# ALTER TABLE items ALTER item_id SET STATISTICS 400;
ALTER TABLE
# analyse verbose items;
INFO: analyzing "public.items"
INFO: "items": scanned 120000 of 11676017 pages, containing 260685 live
rows and 527336 dead rows; 120000 rows in sample, 88580538 estimated
total rows
ANALYZE
>
>
> What do the planner stats show you? Try:
>
> select reltuples::integer from pg_class where oid = 'items'::regclass;
I'm far away from reality too.
# select reltuples::integer from pg_class where oid = 'items'::regclass;
-[ RECORD 1 ]-------
reltuples | 89400832
>
>
> You might get a better response from the pgsql-general list.
>
> Glyn
>
------------
> 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?
Indeed I have special autovacuum parameters on this table (
autovacuum_vacuum_scale_factor=0.05 ), because there are lot of updates
on this table, and I did not want it to bloat too much. And so
autovacuum is almost always running on this table. But I don't
understand why it should change something ?
Should i downgrade scale_factor ?
>
> 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.
I understand your meaning about inheritance, but in my case select count
(25m) is lower than pgstats (110m).
Thanks
--
Thomas
--
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: tsimon@neteven.com, glynastill@yahoo.co.uk, tgl@sss.pgh.pa.us
Subject: Re: Wrong stat on pg_stat_user_tables
In-Reply-To: <f48c07da-8069-dd3b-f383-3cead96318be@neteven.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