Reltuples/n_live_tup values wrong

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

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs