Hi,
PG 8.4.4
I have an strange problem:
carmen=# VACUUM FULL verbose tp93t;
INFO: vacuuming "public.tp93t"
INFO: "tp93t": found 0 removable, 71984 nonremovable row versions in 17996 pages
DETAIL: 70632 dead row versions cannot be removed yet.
Nonremovable row versions range from 1848 to 2032 bytes long.
There were 0 unused item pointers.
Total free space (including removable row versions) is 1523648 bytes.
0 pages are or will become empty, including 0 at the end of the table.
0 pages containing 0 free bytes are potential move destinations.
CPU 0.00s/0.03u sec elapsed 0.03 sec.
INFO: index "tp93t_pkey" now contains 71984 row versions in 868 pages
DETAIL: 0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: vacuuming "pg_toast.pg_toast_24274"
INFO: "pg_toast_24274": found 0 removable, 0 nonremovable row versions in 0 pages
DETAIL: 0 dead row versions cannot be removed yet.
Nonremovable row versions range from 0 to 0 bytes long.
There were 0 unused item pointers.
Total free space (including removable row versions) is 0 bytes.
0 pages are or will become empty, including 0 at the end of the table.
0 pages containing 0 free bytes are potential move destinations.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: index "pg_toast_24274_index" now contains 0 row versions in 1 pages
DETAIL: 0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM
armen=# cluster tp93t;
CLUSTER
carmen=# VACUUM FULL verbose tp93t;
INFO: vacuuming "public.tp93t"
INFO: "tp93t": found 0 removable, 71984 nonremovable row versions in 17996 pages
DETAIL: 70632 dead row versions cannot be removed yet.
Nonremovable row versions range from 1848 to 2032 bytes long.
There were 0 unused item pointers.
Total free space (including removable row versions) is 1523648 bytes.
0 pages are or will become empty, including 0 at the end of the table.
0 pages containing 0 free bytes are potential move destinations.
CPU 0.00s/0.03u sec elapsed 0.03 sec.
INFO: index "tp93t_pkey" now contains 71984 row versions in 868 pages
DETAIL: 0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: vacuuming "pg_toast.pg_toast_24274"
INFO: "pg_toast_24274": found 0 removable, 0 nonremovable row versions in 0 pages
DETAIL: 0 dead row versions cannot be removed yet.
Nonremovable row versions range from 0 to 0 bytes long.
There were 0 unused item pointers.
Total free space (including removable row versions) is 0 bytes.
0 pages are or will become empty, including 0 at the end of the table.
0 pages containing 0 free bytes are potential move destinations.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: index "pg_toast_24274_index" now contains 0 row versions in 1 pages
DETAIL: 0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM
carmen=# select count(*) from tp93t;
count
-------
1352
(1 row)
I did't see any transactions locking this table and I think that CLUSTER will recreate the table.
This is a temporary table, with one DELETE, Some INSERTs and a lot of UPDATES. And the UPDATES become slow and slow every time.
The only way to correct, is truncating the table.
Best regards,
Alexandre
