I think that something is wrong because some tables has a value in last_autoanalyze and some dont while most of them were analyzed. For example :
relname | n_live_tup | n_dead_tup | last_autovacuum | last_autoanalyze
-----------------------------+------------+------------+-------------------------------+-------------------------------
tbl1 | 975 | 37 | | 2018-11-26 16:15:51.557115+02
tbl2 | 4283798 | 739345 | 2018-11-26 17:43:46.663709+02 | 2018-11-26 17:44:38.234908+02
tbl3 | 3726015 | 596362 | 2018-11-26 17:37:33.726438+02 | 2018-11-26 17:48:30.126623+02
pg_language | 0 | 0 | |
pg_toast_19018 | 0 | 0 | |
pg_toast_13115 | 0 | 0 | |
tbl4 | 0 | 0 | |
log data :
[root@pg_log]# cat postgresql-Sun.log | grep automatic | awk {'print $6 $7,$10'} | grep db1 | sort -n | uniq -c
1 automaticanalyze "db1.public.attributes"
4 automaticanalyze "db1.public.tbl3"
3 automaticanalyze "db1.public.tbl3"
11 automaticanalyze "db1.public.tbl2"
1 automaticanalyze "db1.public.tbl4"
5 automaticanalyze "db1.public.tbl1"
1 automaticvacuum "db1.pg_catalog.pg_statistic":
5 automaticvacuum "db1.pg_toast.pg_toast_19239":
1 automaticvacuum "db1.pg_toast.pg_toast_19420":
2 automaticvacuum "db1.pg_toast.pg_toast_2619":
3 automaticvacuum "db1.public.tbl3":
9 automaticvacuum "db1.public.tbl2":
1 automaticvacuum "db1.public.tbl4":
I just changed the name of the tables but all other values are accurate.
Firewall rules should block all stats not just some of them right ? Something is weird...
בתאריך יום ב׳, 26 בנוב׳ 2018 ב-17:25 מאת Tom Lane <tgl@xxxxxxxxxxxxx>:
Mariel Cherkassky <mariel.cherkassky@xxxxxxxxx> writes:
> I checked pg_stat_all_tables and I see that the last_autovacuum is empty
> for all the tables but in the database`s log I see that autovacuum was
> running and deleted records(not all of them but still deleted...).
> Can someone explain why the pg_stat_all_tables doesnt show the real data ?
Hm, are you sure the stats collector is working at all? Are other fields
in the pg_stat data updating?
(If not, you may have a problem with firewall rules blocking traffic
on the stats loopback port.)
regards, tom lane
