VACUUM produces odd freespace values

Bruce Momjian <bruce@momjian.us>

From: Bruce Momjian <bruce@momjian.us>
To: PostgreSQL-development <pgsql-hackers@postgreSQL.org>
Date: 2010-09-18T00:45:00Z
Lists: pgsql-hackers

Attachments

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.

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

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