Vacuum Full + Cluster + Vacuum full = non removable dead rows - Mailing list pgsql-performance

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