On 08/28/2017 09:49 AM, Don Seiler wrote: > I just find it very curious how pg_stat_all_tables.n_live_tups can be so > very very far off in this case unless stats tracking was somehow > disabled during a large bulk load or something. I'll keep an eye on things. If you have a dev machine where you can run an experiment, try the following: (warning: sorry but this is quite long...) 8<-------------- createdb test psql test 8<-------------- create table test(id int); insert into test select g.i from generate_series(1,1000000) as g(i); -- wait a while for autovacuum to run select pg_sleep(90); \x select reltuples from pg_class where relname = 'test'; -[ RECORD 1 ]---- reltuples | 1e+06 select * from pg_stat_all_tables where relname = 'test'; -[ RECORD 1 ]-------+------------------------------ relid | 16385 schemaname | public relname | test seq_scan | 0 seq_tup_read | 0 idx_scan | idx_tup_fetch | n_tup_ins | 1000000 n_tup_upd | 0 n_tup_del | 0 n_tup_hot_upd | 0 n_live_tup | 1000000 n_dead_tup | 0 n_mod_since_analyze | 0 last_vacuum | last_autovacuum | last_analyze | last_autoanalyze | 2017-08-28 10:10:55.046554-07 vacuum_count | 0 autovacuum_count | 0 analyze_count | 0 autoanalyze_count | 1 8<-------------- -- quit postgres -- do an *immediate* shutdown -- can be done a few ways, but for example see -- https://www.postgresql.org/docs/9.6/static/app-pg-ctl.html -- restart postgres 8<-------------- psql test 8<-------------- \x select reltuples from pg_class where relname = 'test'; -[ RECORD 1 ]---- reltuples | 1e+06 select * from pg_stat_all_tables where relname = 'test'; -[ RECORD 1 ]-------+------- relid | 16385 schemaname | public relname | test seq_scan | 0 seq_tup_read | 0 idx_scan | idx_tup_fetch | n_tup_ins | 0 n_tup_upd | 0 n_tup_del | 0 n_tup_hot_upd | 0 n_live_tup | 0 n_dead_tup | 0 n_mod_since_analyze | 0 last_vacuum | last_autovacuum | last_analyze | last_autoanalyze | vacuum_count | 0 autovacuum_count | 0 analyze_count | 0 autoanalyze_count | 0 update test set id = id + 10000000 where id < 50000; -- wait a while for autovacuum to run select pg_sleep(90); select * from pg_stat_all_tables where relname = 'test'; -[ RECORD 1 ]-------+-------- relid | 16385 schemaname | public relname | test seq_scan | 1 seq_tup_read | 1000000 idx_scan | idx_tup_fetch | n_tup_ins | 0 n_tup_upd | 49999 n_tup_del | 0 n_tup_hot_upd | 0 n_live_tup | 0 n_dead_tup | 49999 n_mod_since_analyze | 49999 last_vacuum | last_autovacuum | last_analyze | last_autoanalyze | vacuum_count | 0 autovacuum_count | 0 analyze_count | 0 autoanalyze_count | 0 update test set id = id + 10000000 where id < 100000; -- wait a while for autovacuum to run select pg_sleep(90); -- will still not trigger autoanalyze because defaults are -- 50 + .1 x 1000000 = 100050 -- but we have only changed 100000 select * from pg_stat_all_tables where relname = 'test'; -[ RECORD 1 ]-------+-------- relid | 16385 schemaname | public relname | test seq_scan | 2 seq_tup_read | 2000000 idx_scan | idx_tup_fetch | n_tup_ins | 0 n_tup_upd | 99999 n_tup_del | 0 n_tup_hot_upd | 48 n_live_tup | 0 n_dead_tup | 99999 n_mod_since_analyze | 99999 last_vacuum | last_autovacuum | last_analyze | last_autoanalyze | vacuum_count | 0 autovacuum_count | 0 analyze_count | 0 autoanalyze_count | 0 -- one more time should trigger autoanalyze -- however not enough to trigger autovac which will be -- 50 + .2 x 1000000 = 200050 update test set id = id + 10000000 where id < 150000; -- wait a while for autovacuum to run select pg_sleep(90); -- no we have changed 150000 select * from pg_stat_all_tables where relname = 'test'; -[ RECORD 1 ]-------+------------------------------ relid | 16385 schemaname | public relname | test seq_scan | 3 seq_tup_read | 3000000 idx_scan | idx_tup_fetch | n_tup_ins | 0 n_tup_upd | 149999 n_tup_del | 0 n_tup_hot_upd | 113 n_live_tup | 1000000 n_dead_tup | 149951 n_mod_since_analyze | 0 last_vacuum | last_autovacuum | last_analyze | last_autoanalyze | 2017-08-28 10:26:28.691209-07 vacuum_count | 0 autovacuum_count | 0 analyze_count | 0 autoanalyze_count | 1 -- one more time should trigger autovac -- 50 + .2 x 1000000 = 200050 update test set id = id + 10000000 where id < 250000; -- wait a while for autovacuum to run select pg_sleep(90); -- no we have changed 150000 select * from pg_stat_all_tables where relname = 'test'; -[ RECORD 1 ]-------+------------------------------ relid | 16385 schemaname | public relname | test seq_scan | 4 seq_tup_read | 4000000 idx_scan | idx_tup_fetch | n_tup_ins | 0 n_tup_upd | 249999 n_tup_del | 0 n_tup_hot_upd | 178 n_live_tup | 1000000 n_dead_tup | 0 n_mod_since_analyze | 100000 last_vacuum | last_autovacuum | 2017-08-28 10:29:37.164542-07 last_analyze | last_autoanalyze | 2017-08-28 10:26:28.691209-07 vacuum_count | 0 autovacuum_count | 1 analyze_count | 0 autoanalyze_count | 1 -- Crunchy Data - http://crunchydata.com PostgreSQL Support for Secure Enterprises Consulting, Training, & Open Source Development
Attachment:
signature.asc
Description: OpenPGP digital signature
