Postgres Stats after Crash Recovery

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