Re: Surprising dead_tuple_count from pgstattuple

On Fri, Aug 6, 2010 at 9:11 PM, Itagaki Takahiro
<itagaki(dot)takahiro(at)gmail(dot)com> wrote:
> 2010/8/7 Gordon Shannon <gordo169(at)gmail(dot)com>:
>> 1. I delete 10,000 rows.
>> pgstattuple.dead_tuple_count -> 10000
>>
>> 2. I delete 15,000 more rows.
>> pgstattuple.dead_tuple_count -> 15000 ??
>>
>> pgstattuple now appears to count the earlier 10K deleted tuples as no longer
>> dead, but free space.
>
> I think it's expected behavior that comes from HOT page reclaim.
> The second DELETE not only deleted rows but also removed physical
> tuples that were deleted in 1. Missing dead rows were pruned by HOT.

What would have triggered a HOT prune at any point in this operation?
And why would it have reclaimed all of the deleted rows?

My thought would be "is autovacuum running in the background in
between these commands?".

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company