Re: Surprising dead_tuple_count from pgstattuple

I think this simple test highlights the question well.

------------------
create temporary table t(x int) with (autovacuum_enabled=off);
insert into t select x from generate_series(1,10000,1)x;
vacuum verbose t;
select dead_tuple_count from pgstattuple('t'); --> 0
delete from t where x <= 100;
select dead_tuple_count from pgstattuple('t'); --> 100
delete from t where x <= 300;
select dead_tuple_count from pgstattuple('t'); --> 200 (not 300)
vacuum verbose t;
vacuum verbose t;
select dead_tuple_count from pgstattuple('t'); --> 0
drop table t;
------------------

When we get to the 2 vacuums at the end, I expect it to vacuum 300 rows, but
it only appears to vacuum 200...

vacuum verbose t;
psql:test:15: INFO: vacuuming "pg_temp_2.t"
psql:test:15: INFO: "t": removed 200 row versions in 2 pages
psql:test:15: INFO: "t": found 200 removable, 9700 nonremovable row
versions in 45 out of 45 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.
VACUUM
Time: 69.792 ms

Yet, the next vacuum reports that it found 300 unused item pointers. So they
were all vacuumed by somebody.

psql:test:16: INFO: vacuuming "pg_temp_2.t"
psql:test:16: INFO: "t": found 0 removable, 7158 nonremovable row versions
in 33 out of 45 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 300 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM
Time: 38.436 ms

So, I guess my real question here is, what happened to the "missing" 100
items? If it was HOT prune, can anyone summarize what that does?

Thanks!
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Surprising-dead-tuple-count-from-pgstattuple-tp2266955p2267745.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.