Re: VACUUM produces odd freespace values

Bruce Momjian wrote:
> Can anyone explain why VACUUM after INSERT shows steadily decreasing
> freespace, while DELETE of the same rows does not decrease consistently?
> 
> Specifically, after one row is inserted I see:
> 
> 	SELECT pg_freespace('mvcc_demo');
> 	 pg_freespace
> 	--------------
> 	 (0,8128)
> 	(1 row)
> 
> but after inserting two more rows and deleting those two rows, I see:
> 
> 	SELECT pg_freespace('mvcc_demo');
> 	 pg_freespace
> 	--------------
> 	 (0,8096)
> 	(1 row)
> 
> Seems that value should be '(0,8128)'.  Is it the unused line pointers
> that are causing this?
> 
> Another odd thing --- if I change the second VACUUM to VACUUM FULL I
> see:
> 
> 	VACUUM FULL mvcc_demo;
> 	VACUUM
> 	SELECT pg_freespace('mvcc_demo');
> 	 pg_freespace
> 	--------------
> 	 (0,0)
> 	(1 row)
> 
> There is still a row in the table, so why is there no free space
> reported?  I realize after VACUUM FULL that only the last page has
> freespace --- do we assume that will be used as default for the next
> addition and just not bother with the free space map? --- makes sense if
> we do that.  Does this happen because cluster creates a new relfilenode?
> 
> I am attaching the init script, the SQL query script, and the results I
> obtained against our CVS HEAD.

Sorry.  Attached is trimmed-down result file that shows just the
problem.

-- 
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +