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. +

On 18 September 2010 02:00, Bruce Momjian <bruce@momjian.us> wrote:

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. +

00-init.sql
------------------
This script is designed to run in a database called test
and requires installation of /contrib/pageinspect and
/contrib/pg_freespacemap.
You are now connected to database "test" as user "postgres".
DROP TABLE IF EXISTS mvcc_demo;
DROP TABLE
CREATE TABLE mvcc_demo (val INTEGER);
CREATE TABLE
DROP VIEW IF EXISTS mvcc_demo_page0;
DROP VIEW
CREATE VIEW mvcc_demo_page0 AS
       SELECT  '(0,' || lp || ')' AS ctid,
               CASE lp_flags
                       WHEN 0 THEN 'Unused'
                       WHEN 1 THEN 'Normal'
                       WHEN 2 THEN 'Redirect to ' || lp_off
                       WHEN 3 THEN 'Dead'
               END,
               t_xmin::text::int8 AS xmin,
               t_xmax::text::int8 AS xmax,
               t_ctid
       FROM heap_page_items(get_raw_page('mvcc_demo', 0))
       ORDER BY lp;
CREATE VIEW

31-vacuum-freesp.sql
------------------
TRUNCATE mvcc_demo;
TRUNCATE TABLE
VACUUM mvcc_demo;
VACUUM
SELECT pg_freespace('mvcc_demo');
 pg_freespace
--------------
(0 rows)

INSERT INTO mvcc_demo VALUES (1);
INSERT 0 1
VACUUM mvcc_demo;
VACUUM
SELECT pg_freespace('mvcc_demo');
 pg_freespace
--------------
 (0,8128)
(1 row)

INSERT INTO mvcc_demo VALUES (2);
INSERT 0 1
VACUUM mvcc_demo;
VACUUM
SELECT pg_freespace('mvcc_demo');
 pg_freespace
--------------
 (0,8096)
(1 row)

INSERT INTO mvcc_demo VALUES (3);
INSERT 0 1
VACUUM mvcc_demo;
VACUUM
SELECT pg_freespace('mvcc_demo');
 pg_freespace
--------------
 (0,8064)
(1 row)

DELETE FROM mvcc_demo WHERE val = 3;
DELETE 1
VACUUM mvcc_demo;
VACUUM
SELECT pg_freespace('mvcc_demo');
 pg_freespace
--------------
 (0,8096)
(1 row)

DELETE FROM mvcc_demo WHERE val = 2;
DELETE 1
VACUUM mvcc_demo;
VACUUM
SELECT pg_freespace('mvcc_demo');
 pg_freespace
--------------
 (0,8096)
(1 row)

SELECT * FROM mvcc_demo_page0;
 ctid  |  case  | xmin | xmax | t_ctid
-------+--------+------+------+--------
 (0,1) | Normal | 1339 |    0 | (0,1)
 (0,2) | Unused |      |      |
 (0,3) | Unused |      |      |
(3 rows)

DELETE FROM mvcc_demo WHERE val = 1;
DELETE 1
VACUUM mvcc_demo;
VACUUM
SELECT pg_freespace('mvcc_demo');
 pg_freespace
--------------
(0 rows)

VACUUM mvcc_demo;
VACUUM
SELECT pg_relation_size('mvcc_demo');
 pg_relation_size
------------------
               0
(1 row)

That's odd. When I delete val 2, the freespace goes back up in 9.0rc1
(attached).

--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

Thom Brown wrote:

VACUUM mvcc_demo;
VACUUM
SELECT pg_relation_size('mvcc_demo');
?pg_relation_size
------------------
? ? ? ? ? ? ? ?0
(1 row)

That's odd. When I delete val 2, the freespace goes back up in 9.0rc1
(attached).

Your numbers are odd too. With one row after INSERT you show:

(0,8128)

but after delete you with one row left you show:

(0,8096)

You also dip to (0,8032), which I don't see. I now see we only track
range of free space values. For example, freespace.c has:

* We use just one byte to store the amount of free space on a page, so we
* divide the amount of free space a page can have into 256 different
* categories. The highest category, 255, represents a page with at least
* MaxFSMRequestSize bytes of free space, and the second highest category
* represents the range from 254 * FSM_CAT_STEP, inclusive, to
* MaxFSMRequestSize, exclusive.
*
* MaxFSMRequestSize depends on the architecture and BLCKSZ, but assuming
* default 8k BLCKSZ, and that MaxFSMRequestSize is 24 bytes, the categories
* look like this
*
*
* Range Category
* 0 - 31 0
* 32 - 63 1
* ... ... ...
* 8096 - 8127 253
* 8128 - 8163 254
* 8164 - 8192 255

So, my guess is that the unused item pointers are causing the free space
to fall into a smaller category than we had after the first INSERT. I
bet if I pulled more columns from heap_page_items() I could see it.

Anyway, I think I have my explaination now. Thanks.

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

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