Reltuples/n_live_tup values wrong - Mailing list pgsql-bugs

Hi,

I'm facing a problem which seems like a bug to me. I'm running:

centos 5.9
postgresql92.x86_64 (9.2.4-1PGDG.rhel5)

I have a table whith many inserts/deletes

> select * from pg_stat_user_tables where schemaname = 'lobby' and
> relname = 'lobby_player' order by n_live_tup desc;
> relid | schemaname | relname | seq_scan | seq_tup_read | idx_scan |
> idx_tup_fetch | n_tup_ins | n_tup_upd | n_tup_del | n_tup_hot_upd |
> n_live_tup | n_dead_tup | last_vacuum | last_autovacuum | last_analyze
> | last_autoanalyze | vacuum_count | autovacuum_count | analyze_count |
> autoanalyze_count
>
-------+------------+--------------+----------+--------------+----------+---------------+-----------+-----------+-----------+---------------+------------+------------+-------------------------------+------
>
-------------------------+-------------------------------+-------------------------------+--------------+------------------+---------------+-------------------
> 16594 | lobby | lobby_player | 231335 | 7156825 | 3726165 | 3334044 |
> 277457 | 124 | 277380 | 118 | 608965 | 187 | 2013-08-07
> 11:38:08.877226+02 | 2013- 08-07 10:48:16.282828+02 | 2013-08-07
> 11:17:50.551531+02 | 2013-08-06 19:48:41.649425+02 | 8 | 25 | 4 | 24
> (1 row)


> select reltuples from pg_class where relname = 'lobby_player';
> reltuples
> -----------
> 608985
> (1 row)


But my Problem is the following:

> select count(*) from lobby_player;
> count
> -------
> 10
> (1 row)

If I do an analyze, the stats change:

> select relname, n_live_tup, n_dead_tup, last_vacuum, last_autovacuum
> from pg_stat_user_tables where schemaname = 'lobby' and relname =
> 'lobby_player' order by n_live_tup desc;
> relname | n_live_tup | n_dead_tup | last_vacuum | last_autovacuum
> --------------+------------+------------+-------------------------------+-------------------------------
> lobby_player | 15 | 609186 | 2013-08-07 11:38:08.877226+02 |
> 2013-08-07 10:48:16.282828+02
> (1 row)

Reltuples stays at the 600k value, after another vacuum the tuples are
live again.

I already did a reindex table, dropped the index+pkey, vacuum full but
it doesn't change anything.

Another thing I see is:

> \dt+ lobby_player
> List of relations
> Schema | Name | Type | Owner | Size | Description
> --------+--------------+-------+-------+-------+-------------
> lobby | lobby_player | table | adm | 46 MB |
> (1 row)

> select oid from pg_class where relname = 'lobby_player';
> oid
> -------
> 16594
> (1 row)

> postgres@database:~ # ls -alh 9.2/data/base/*/16594*
> -rw------- 1 postgres postgres 0 Aug 7 11:34 9.2/data/base/16384/16594

So the 46MB are not there

> vacuum ANALYZE VERBOSE lobby_player;
> INFO:  vacuuming "lobby.lobby_player"
> INFO:  index "lobby_player_pkey" now contains 609319 row versions in
> 2348 pages
> DETAIL:  0 index row versions were removed.
> 0 index pages have been deleted, 0 are currently reusable.
> CPU 0.00s/0.00u sec elapsed 8.03 sec.
> INFO:  index "idx_lobby_player_gtype_player_type" now contains 609319
> row versions in 1674 pages
> DETAIL:  0 index row versions were removed.
> 0 index pages have been deleted, 0 are currently reusable.
> CPU 0.00s/0.00u sec elapsed 1.00 sec.
> INFO:  "lobby_player": found 0 removable, 609319 nonremovable row
> versions in 5937 out of 5937 pages
> DETAIL:  609299 dead row versions cannot be removed yet.
> There were 0 unused item pointers.
> 0 pages are entirely empty.
> CPU 0.00s/0.00u sec elapsed 16.18 sec.
> INFO:  vacuuming "pg_toast.pg_toast_16594"
> INFO:  index "pg_toast_16594_index" now contains 0 row versions in 1 pages
> DETAIL:  0 index row versions were removed.
> 0 index pages have been deleted, 0 are currently reusable.
> CPU 0.00s/0.00u sec elapsed 0.00 sec.
> INFO:  "pg_toast_16594": found 0 removable, 0 nonremovable row
> versions in 0 out of 0 pages
> DETAIL:  0 dead row versions cannot be removed yet.
> There were 0 unused item pointers.
> 0 pages are entirely empty.
> CPU 0.00s/0.00u sec elapsed 0.00 sec.
> INFO:  analyzing "lobby.lobby_player"
> INFO:  "lobby_player": scanned 5937 of 5937 pages, containing 21 live
> rows and 609302 dead rows; 21 rows in sample, 21 estimated total rows
> VACUUM

Thanks for any help!
-Sebastian