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
