Обсуждение: VACUUM produces odd freespace values

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

\echo This script is designed to run in a database called 'test'
\echo and requires installation of /contrib/pageinspect and
\echo /contrib/pg_freespacemap.
\c test

DROP TABLE IF EXISTS mvcc_demo;
CREATE TABLE mvcc_demo (val INTEGER);

DROP VIEW IF EXISTS mvcc_demo_page0;
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;
-- clear out heap file
TRUNCATE mvcc_demo;

VACUUM mvcc_demo;

SELECT pg_freespace('mvcc_demo');

INSERT INTO mvcc_demo VALUES (1);

VACUUM mvcc_demo;

SELECT pg_freespace('mvcc_demo');

INSERT INTO mvcc_demo VALUES (2);

VACUUM mvcc_demo;

SELECT pg_freespace('mvcc_demo');

INSERT INTO mvcc_demo VALUES (3);

VACUUM mvcc_demo;

SELECT pg_freespace('mvcc_demo');

DELETE FROM mvcc_demo WHERE val = 3;

VACUUM mvcc_demo;

SELECT pg_freespace('mvcc_demo');

DELETE FROM mvcc_demo WHERE val = 2;

VACUUM mvcc_demo;

SELECT pg_freespace('mvcc_demo');

SELECT * FROM mvcc_demo_page0;

DELETE FROM mvcc_demo WHERE val = 1;

VACUUM mvcc_demo;

SELECT pg_freespace('mvcc_demo');

VACUUM mvcc_demo;

SELECT pg_relation_size('mvcc_demo');

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

01-xmin_ins.sql
------------------
DELETE FROM mvcc_demo;
DELETE 0
INSERT INTO mvcc_demo VALUES (1);
INSERT 0 1
SELECT xmin, xmax, * FROM mvcc_demo;
 xmin | xmax | val
------+------+-----
 1301 |    0 |   1
(1 row)


02-xmax_del.sql
------------------
DELETE FROM mvcc_demo;
DELETE 1
INSERT INTO mvcc_demo VALUES (1);
INSERT 0 1
SELECT xmin, xmax, * FROM mvcc_demo;
 xmin | xmax | val
------+------+-----
 1303 |    0 |   1
(1 row)

BEGIN WORK;
BEGIN
DELETE FROM mvcc_demo;
DELETE 1
SELECT xmin, xmax, * FROM mvcc_demo;
 xmin | xmax | val
------+------+-----
(0 rows)

    SELECT xmin, xmax, * FROM mvcc_demo;
     xmin | xmax | val
    ------+------+-----
     1303 | 1304 |   1
    (1 row)

COMMIT WORK;
COMMIT

03-xmax_upd.sql
------------------
DELETE FROM mvcc_demo;
DELETE 0
INSERT INTO mvcc_demo VALUES (1);
INSERT 0 1
SELECT xmin, xmax, * FROM mvcc_demo;
 xmin | xmax | val
------+------+-----
 1305 |    0 |   1
(1 row)

BEGIN WORK;
BEGIN
UPDATE mvcc_demo SET val = 2;
UPDATE 1
SELECT xmin, xmax, * FROM mvcc_demo;
 xmin | xmax | val
------+------+-----
 1306 |    0 |   2
(1 row)

    SELECT xmin, xmax, * FROM mvcc_demo;
     xmin | xmax | val
    ------+------+-----
     1305 | 1306 |   1
    (1 row)

COMMIT WORK;
COMMIT

04-xmax_lock.sql
------------------
DELETE FROM mvcc_demo;
DELETE 1
INSERT INTO mvcc_demo VALUES (1);
INSERT 0 1
BEGIN WORK;
BEGIN
SELECT xmin, xmax, * FROM mvcc_demo;
 xmin | xmax | val
------+------+-----
 1308 |    0 |   1
(1 row)

SELECT xmin, xmax, * FROM mvcc_demo FOR UPDATE;
 xmin | xmax | val
------+------+-----
 1308 |    0 |   1
(1 row)

SELECT xmin, xmax, * FROM mvcc_demo;
 xmin | xmax | val
------+------+-----
 1308 | 1309 |   1
(1 row)

COMMIT WORK;
COMMIT

05-cmin_ins.sql
------------------
DELETE FROM mvcc_demo;
DELETE 1
BEGIN WORK;
BEGIN
INSERT INTO mvcc_demo VALUES (1);
INSERT 0 1
INSERT INTO mvcc_demo VALUES (2);
INSERT 0 1
INSERT INTO mvcc_demo VALUES (3);
INSERT 0 1
SELECT xmin, cmin, xmax, * FROM mvcc_demo;
 xmin | cmin | xmax | val
------+------+------+-----
 1311 |    0 |    0 |   1
 1311 |    1 |    0 |   2
 1311 |    2 |    0 |   3
(3 rows)

COMMIT WORK;
COMMIT

06-cmin_del.sql
------------------
DELETE FROM mvcc_demo;
DELETE 3
BEGIN WORK;
BEGIN
INSERT INTO mvcc_demo VALUES (1);
INSERT 0 1
INSERT INTO mvcc_demo VALUES (2);
INSERT 0 1
INSERT INTO mvcc_demo VALUES (3);
INSERT 0 1
SELECT xmin, cmin, xmax, * FROM mvcc_demo;
 xmin | cmin | xmax | val
------+------+------+-----
 1313 |    0 |    0 |   1
 1313 |    1 |    0 |   2
 1313 |    2 |    0 |   3
(3 rows)

DECLARE c_mvcc_demo CURSOR FOR
SELECT xmin, xmax, cmax, * FROM mvcc_demo;
DECLARE CURSOR
DELETE FROM mvcc_demo;
DELETE 3
SELECT xmin, cmin, xmax, * FROM mvcc_demo;
 xmin | cmin | xmax | val
------+------+------+-----
(0 rows)

FETCH ALL FROM c_mvcc_demo;
 xmin | xmax | cmax | val
------+------+------+-----
 1313 | 1313 |    0 |   1
 1313 | 1313 |    1 |   2
 1313 | 1313 |    2 |   3
(3 rows)

COMMIT WORK;
COMMIT

07-cmin_upd.sql
------------------
DELETE FROM mvcc_demo;
DELETE 0
BEGIN WORK;
BEGIN
INSERT INTO mvcc_demo VALUES (1);
INSERT 0 1
INSERT INTO mvcc_demo VALUES (2);
INSERT 0 1
INSERT INTO mvcc_demo VALUES (3);
INSERT 0 1
SELECT xmin, cmin, xmax, * FROM mvcc_demo;
 xmin | cmin | xmax | val
------+------+------+-----
 1314 |    0 |    0 |   1
 1314 |    1 |    0 |   2
 1314 |    2 |    0 |   3
(3 rows)

DECLARE c_mvcc_demo CURSOR FOR
SELECT xmin, xmax, cmax, * FROM mvcc_demo;
DECLARE CURSOR
UPDATE mvcc_demo SET val = val * 10;
UPDATE 3
SELECT xmin, cmin, xmax, * FROM mvcc_demo;
 xmin | cmin | xmax | val
------+------+------+-----
 1314 |    3 |    0 |  10
 1314 |    3 |    0 |  20
 1314 |    3 |    0 |  30
(3 rows)

FETCH ALL FROM c_mvcc_demo;
 xmin | xmax | cmax | val
------+------+------+-----
 1314 | 1314 |    0 |   1
 1314 | 1314 |    1 |   2
 1314 | 1314 |    2 |   3
(3 rows)

COMMIT WORK;
COMMIT

09-cmax_upd.sql
------------------
DELETE FROM mvcc_demo;
DELETE 3
INSERT INTO mvcc_demo VALUES (1);
INSERT 0 1
SELECT xmin, xmax, * FROM mvcc_demo;
 xmin | xmax | val
------+------+-----
 1316 |    0 |   1
(1 row)

BEGIN WORK;
BEGIN
INSERT INTO mvcc_demo VALUES (2);
INSERT 0 1
INSERT INTO mvcc_demo VALUES (3);
INSERT 0 1
INSERT INTO mvcc_demo VALUES (4);
INSERT 0 1
SELECT xmin, cmin, xmax, * FROM mvcc_demo;
 xmin | cmin | xmax | val
------+------+------+-----
 1316 |    0 |    0 |   1
 1317 |    0 |    0 |   2
 1317 |    1 |    0 |   3
 1317 |    2 |    0 |   4
(4 rows)

UPDATE mvcc_demo SET val = val * 10;
UPDATE 4
SELECT xmin, cmin, xmax, * FROM mvcc_demo;
 xmin | cmin | xmax | val
------+------+------+-----
 1317 |    3 |    0 |  10
 1317 |    3 |    0 |  20
 1317 |    3 |    0 |  30
 1317 |    3 |    0 |  40
(4 rows)

    SELECT xmin, xmax, cmax, * FROM mvcc_demo;
     xmin | xmax | cmax | val
    ------+------+------+-----
     1316 | 1317 |    3 |   1
    (1 row)

COMMIT WORK;
COMMIT

10-cmin_upd_combo.sql
------------------
TRUNCATE mvcc_demo;
TRUNCATE TABLE
BEGIN WORK;
BEGIN
DELETE FROM mvcc_demo;
DELETE 0
DELETE FROM mvcc_demo;
DELETE 0
DELETE FROM mvcc_demo;
DELETE 0
INSERT INTO mvcc_demo VALUES (1);
INSERT 0 1
INSERT INTO mvcc_demo VALUES (2);
INSERT 0 1
INSERT INTO mvcc_demo VALUES (3);
INSERT 0 1
SELECT xmin, cmin, xmax, * FROM mvcc_demo;
 xmin | cmin | xmax | val
------+------+------+-----
 1319 |    3 |    0 |   1
 1319 |    4 |    0 |   2
 1319 |    5 |    0 |   3
(3 rows)

DECLARE c_mvcc_demo CURSOR FOR
SELECT xmin, xmax, cmax, * FROM mvcc_demo;
DECLARE CURSOR
UPDATE mvcc_demo SET val = val * 10;
UPDATE 3
SELECT xmin, cmin, xmax, * FROM mvcc_demo;
 xmin | cmin | xmax | val
------+------+------+-----
 1319 |    6 |    0 |  10
 1319 |    6 |    0 |  20
 1319 |    6 |    0 |  30
(3 rows)

FETCH ALL FROM c_mvcc_demo;
 xmin | xmax | cmax | val
------+------+------+-----
 1319 | 1319 |    0 |   1
 1319 | 1319 |    1 |   2
 1319 | 1319 |    2 |   3
(3 rows)

SELECT     t_xmin AS xmin,
    t_xmax::text::int8 AS xmax,
    t_field3::text::int8 AS cmin_cmax,
    (t_infomask::integer & X'0020'::integer)::bool AS is_combocid
FROM heap_page_items(get_raw_page('mvcc_demo', 0))
ORDER BY 2 DESC, 3;
 xmin | xmax | cmin_cmax | is_combocid
------+------+-----------+-------------
 1319 | 1319 |         0 | t
 1319 | 1319 |         1 | t
 1319 | 1319 |         2 | t
 1319 |    0 |         6 | f
 1319 |    0 |         6 | f
 1319 |    0 |         6 | f
(6 rows)

COMMIT WORK;
COMMIT

20-hot_ins.sql
------------------
TRUNCATE mvcc_demo;
TRUNCATE TABLE
INSERT INTO mvcc_demo SELECT 0 FROM generate_series(1, 240);
INSERT 0 240
SELECT (100 * (upper - lower) / pagesize::float8)::integer AS free_pct
FROM page_header(get_raw_page('mvcc_demo', 0));
 free_pct
----------
        6
(1 row)

INSERT INTO mvcc_demo VALUES (1);
INSERT 0 1
SELECT * FROM mvcc_demo_page0
OFFSET 240;
  ctid   |  case  | xmin | xmax | t_ctid
---------+--------+------+------+---------
 (0,241) | Normal | 1322 |    0 | (0,241)
(1 row)

DELETE FROM mvcc_demo WHERE val > 0;
DELETE 1
INSERT INTO mvcc_demo VALUES (2);
INSERT 0 1
SELECT * FROM mvcc_demo_page0
OFFSET 240;
  ctid   |  case  | xmin | xmax | t_ctid
---------+--------+------+------+---------
 (0,241) | Normal | 1322 | 1323 | (0,241)
 (0,242) | Normal | 1324 |    0 | (0,242)
(2 rows)

DELETE FROM mvcc_demo WHERE val > 0;
DELETE 1
INSERT INTO mvcc_demo VALUES (3);
INSERT 0 1
SELECT * FROM mvcc_demo_page0
OFFSET 240;
  ctid   |  case  | xmin | xmax | t_ctid
---------+--------+------+------+---------
 (0,241) | Dead   |      |      |
 (0,242) | Normal | 1324 | 1325 | (0,242)
 (0,243) | Normal | 1326 |    0 | (0,243)
(3 rows)

SELECT * FROM mvcc_demo
OFFSET 1000;
 val
-----
(0 rows)

SELECT * FROM mvcc_demo_page0
OFFSET 240;
  ctid   |  case  | xmin | xmax | t_ctid
---------+--------+------+------+---------
 (0,241) | Dead   |      |      |
 (0,242) | Dead   |      |      |
 (0,243) | Normal | 1326 |    0 | (0,243)
(3 rows)

VACUUM mvcc_demo;
VACUUM
SELECT * FROM mvcc_demo_page0
OFFSET 240;
  ctid   |  case  | xmin | xmax | t_ctid
---------+--------+------+------+---------
 (0,241) | Unused |      |      |
 (0,242) | Unused |      |      |
 (0,243) | Normal | 1326 |    0 | (0,243)
(3 rows)


21-hot_upd.sql
------------------
TRUNCATE mvcc_demo;
TRUNCATE TABLE
INSERT INTO mvcc_demo SELECT 0 FROM generate_series(1, 240);
INSERT 0 240
INSERT INTO mvcc_demo VALUES (1);
INSERT 0 1
SELECT * FROM mvcc_demo_page0
OFFSET 240;
  ctid   |  case  | xmin | xmax | t_ctid
---------+--------+------+------+---------
 (0,241) | Normal | 1329 |    0 | (0,241)
(1 row)

UPDATE mvcc_demo SET val = val + 1 WHERE val > 0;
UPDATE 1
SELECT * FROM mvcc_demo_page0
OFFSET 240;
  ctid   |  case  | xmin | xmax | t_ctid
---------+--------+------+------+---------
 (0,241) | Normal | 1329 | 1330 | (0,242)
 (0,242) | Normal | 1330 |    0 | (0,242)
(2 rows)

UPDATE mvcc_demo SET val = val + 1 WHERE val > 0;
UPDATE 1
SELECT * FROM mvcc_demo_page0
OFFSET 240;
  ctid   |      case       | xmin | xmax | t_ctid
---------+-----------------+------+------+---------
 (0,241) | Redirect to 242 |      |      |
 (0,242) | Normal          | 1330 | 1331 | (0,243)
 (0,243) | Normal          | 1331 |    0 | (0,243)
(3 rows)

UPDATE mvcc_demo SET val = val + 1 WHERE val > 0;
UPDATE 1
SELECT * FROM mvcc_demo_page0
OFFSET 240;
  ctid   |      case       | xmin | xmax | t_ctid
---------+-----------------+------+------+---------
 (0,241) | Redirect to 243 |      |      |
 (0,242) | Normal          | 1332 |    0 | (0,242)
 (0,243) | Normal          | 1331 | 1332 | (0,242)
(3 rows)

SELECT * FROM mvcc_demo
OFFSET 1000;
 val
-----
(0 rows)

SELECT * FROM mvcc_demo_page0
OFFSET 240;
  ctid   |      case       | xmin | xmax | t_ctid
---------+-----------------+------+------+---------
 (0,241) | Redirect to 242 |      |      |
 (0,242) | Normal          | 1332 |    0 | (0,242)
 (0,243) | Unused          |      |      |
(3 rows)

VACUUM mvcc_demo;
VACUUM
SELECT * FROM mvcc_demo_page0
OFFSET 240;
  ctid   |      case       | xmin | xmax | t_ctid
---------+-----------------+------+------+---------
 (0,241) | Redirect to 242 |      |      |
 (0,242) | Normal          | 1332 |    0 | (0,242)
 (0,243) | Unused          |      |      |
(3 rows)


30-vacuum.sql
------------------
TRUNCATE mvcc_demo;
TRUNCATE TABLE
INSERT INTO mvcc_demo VALUES (1);
INSERT 0 1
INSERT INTO mvcc_demo VALUES (2);
INSERT 0 1
INSERT INTO mvcc_demo VALUES (3);
INSERT 0 1
SELECT     ctid, xmin, xmax
FROM mvcc_demo_page0;
 ctid  | xmin | xmax
-------+------+------
 (0,1) | 1334 |    0
 (0,2) | 1335 |    0
 (0,3) | 1336 |    0
(3 rows)

DELETE FROM mvcc_demo;
DELETE 3
SELECT     ctid, xmin, xmax
FROM mvcc_demo_page0;
 ctid  | xmin | xmax
-------+------+------
 (0,1) | 1334 | 1337
 (0,2) | 1335 | 1337
 (0,3) | 1336 | 1337
(3 rows)

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


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)


40-index.sql
------------------

70-index-val.sql
------------------
CREATE INDEX i_mvcc_demo_val on mvcc_demo (val);
CREATE INDEX

80-index.sql
------------------

81-hot_upd.sql
------------------
TRUNCATE mvcc_demo;
TRUNCATE TABLE
INSERT INTO mvcc_demo SELECT 0 FROM generate_series(1, 240);
INSERT 0 240
INSERT INTO mvcc_demo VALUES (1);
INSERT 0 1
SELECT * FROM mvcc_demo_page0
OFFSET 240;
  ctid   |  case  | xmin | xmax | t_ctid
---------+--------+------+------+---------
 (0,241) | Normal | 1348 |    0 | (0,241)
(1 row)

UPDATE mvcc_demo SET val = val + 1 WHERE val > 0;
UPDATE 1
SELECT * FROM mvcc_demo_page0
OFFSET 240;
  ctid   |  case  | xmin | xmax | t_ctid
---------+--------+------+------+---------
 (0,241) | Normal | 1348 | 1349 | (0,242)
 (0,242) | Normal | 1349 |    0 | (0,242)
(2 rows)

UPDATE mvcc_demo SET val = val + 1 WHERE val > 0;
UPDATE 1
SELECT * FROM mvcc_demo_page0
OFFSET 240;
  ctid   |  case  | xmin | xmax | t_ctid
---------+--------+------+------+---------
 (0,241) | Dead   |      |      |
 (0,242) | Normal | 1349 | 1350 | (0,243)
 (0,243) | Normal | 1350 |    0 | (0,243)
(3 rows)

UPDATE mvcc_demo SET val = val + 1 WHERE val > 0;
UPDATE 1
SELECT * FROM mvcc_demo_page0
OFFSET 240;
  ctid   |  case  | xmin | xmax | t_ctid
---------+--------+------+------+---------
 (0,241) | Dead   |      |      |
 (0,242) | Dead   |      |      |
 (0,243) | Normal | 1350 | 1351 | (0,244)
 (0,244) | Normal | 1351 |    0 | (0,244)
(4 rows)

SELECT * FROM mvcc_demo
OFFSET 1000;
 val
-----
(0 rows)

SELECT * FROM mvcc_demo_page0
OFFSET 240;
  ctid   |  case  | xmin | xmax | t_ctid
---------+--------+------+------+---------
 (0,241) | Dead   |      |      |
 (0,242) | Dead   |      |      |
 (0,243) | Dead   |      |      |
 (0,244) | Normal | 1351 |    0 | (0,244)
(4 rows)

VACUUM mvcc_demo;
VACUUM
SELECT * FROM mvcc_demo_page0
OFFSET 240;
  ctid   |  case  | xmin | xmax | t_ctid
---------+--------+------+------+---------
 (0,241) | Unused |      |      |
 (0,242) | Unused |      |      |
 (0,243) | Unused |      |      |
 (0,244) | Normal | 1351 |    0 | (0,244)
(4 rows)


90-vacuum.sql
------------------
TRUNCATE mvcc_demo;
TRUNCATE TABLE
INSERT INTO mvcc_demo VALUES (1);
INSERT 0 1
INSERT INTO mvcc_demo VALUES (2);
INSERT 0 1
INSERT INTO mvcc_demo VALUES (3);
INSERT 0 1
SELECT     ctid, xmin, xmax
FROM mvcc_demo_page0;
 ctid  | xmin | xmax
-------+------+------
 (0,1) | 1353 |    0
 (0,2) | 1354 |    0
 (0,3) | 1355 |    0
(3 rows)

DELETE FROM mvcc_demo;
DELETE 3
SELECT     ctid, xmin, xmax
FROM mvcc_demo_page0;
 ctid  | xmin | xmax
-------+------+------
 (0,1) | 1353 | 1356
 (0,2) | 1354 | 1356
 (0,3) | 1355 | 1356
(3 rows)

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

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)

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
haveinto 256 different* categories. The highest category, 255, represents a page with at least* MaxFSMRequestSize bytes
offree 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. +