I confirm this as a bug. First ANALYZE after crash recovery leaves stats showing as zeroes. Repeatable on CVS HEAD with ANALYZE and VACUUM ANALYZE.
Forwarding to bugs. On Wed, 2008-09-24 at 15:29 -0400, Chirag Dave wrote: > > Testing AutoVac on 8.3 , i came across the problem of loosing stats > data, which was discussed in my last post > > http://archives.postgresql.org/pgsql-general/2008-08/msg00198.php > > that problem was recognized that doing hard stop, server will throw > away the stats while going through crash recovery. > > Problem i see is after crash recovery , we have to manually analyze > database in order for autovac to work but it not working as expected. > > Here is test case: > > foo=# SELECT version(); > > version > ------------------------------------------------------------------------------------------------ > PostgreSQL 8.3.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) > 4.1.2 (Ubuntu 4.1.2-0ubuntu4) > (1 row) > > foo=# SELECT * from pg_stat_user_tables where relname='accounts'; > -[ RECORD 1 ]----+------------------------------ > relid | 57350 > schemaname | public > relname | accounts > seq_scan | 1 > seq_tup_read | 1000000 > idx_scan | 0 > idx_tup_fetch | 0 > 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 > last_vacuum | 2008-09-24 15:04:35.384012-04 > last_autovacuum | > last_analyze | 2008-09-24 15:04:35.384012-04 > last_autoanalyze | > > > Next i will stop DB immediate and expect to loose stats as normal > behavior. > > pg_ctl -m immediate -D /opt/rg/foo/pgsql83 stop > waiting for server to shut down...LOG: received immediate shutdown > request > done > server stopped > > After stating the DB, as expected: > foo=# SELECT * from pg_stat_user_tables where relname='accounts'; > -[ RECORD 1 ]----+--------- > relid | 57350 > schemaname | public > relname | accounts > seq_scan | 0 > seq_tup_read | 0 > idx_scan | 0 > idx_tup_fetch | 0 > 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 > last_vacuum | > last_autovacuum | > last_analyze | > last_autoanalyze | > > Next step is to manually analyse to collects the stats again: > foo=# ANALYZE ; > ANALYZE > foo=# SELECT * from pg_stat_user_tables where relname='accounts'; > -[ RECORD 1 ]----+--------- > relid | 57350 > schemaname | public > relname | accounts > seq_scan | 0 > seq_tup_read | 0 > idx_scan | 0 > idx_tup_fetch | 0 > 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 > last_vacuum | > last_autovacuum | > last_analyze | > last_autoanalyze | > > note: After ANALYSE, ststs were not updated. > > Running ANALYSE, second time seems to does the trick. > > foo=# ANALYZE ; > ANALYZE > foo=# SELECT * from pg_stat_user_tables where relname='accounts'; > -[ RECORD 1 ]----+------------------------------ > relid | 57350 > schemaname | public > relname | accounts > seq_scan | 0 > seq_tup_read | 0 > idx_scan | 0 > idx_tup_fetch | 0 > n_tup_ins | 0 > n_tup_upd | 0 > n_tup_del | 0 > n_tup_hot_upd | 0 > n_live_tup | 1000062 > n_dead_tup | 0 > last_vacuum | > last_autovacuum | > last_analyze | 2008-09-24 15:13:13.423424-04 > last_autoanalyze | > > > So question i have is, is this normal operation,why we need to analyze > twice to updates the stats ? if table/tables are candidate for > vacuuming after crash recovery will never get auto-vac unless you do > 'ANALYZE' twice. > > Thanks in advance, > > Chirag Dave > DBA > Afilias -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
