Why the mismatch? {row size reported by VACUUM} vs. {table size on disk / number of rows} vs. {sum of column type sizes} - Mailing list pgsql-general

PostgreSQL 8.3.3, on Win32.

I have a table with 8 fixed-size (integer-type) columns: 6 x int8, 1 x
integer, 1 x int2.  All columns have storage=plain.  Have explicitly set
FILLFACTOR=100 and WITHOUT OIDS just so there's no misunderstanding.

Expected row size, based solely on data types and minus any per-row (MVCC
etc.) overhead: 54 bytes.

However, row size reported by VACUUM FULL VERBOSE is 88 bytes.

Also, table size (reported by pg_relation_size) divided by number of rows in
the table: ~93 bytes per row.

After altering the int2 column to int8, the table size (reported by
pg_relation_size) does not change by one single byte.  Idem row size
reported by VACCUM.

Why is the observed-vs-theoretical row size overhead ~40 bytes/row?  Why
does VACUUM report a value that's 5 bytes less than
table_size/number_of_rows?  Why does the table size not change when altering
a column's type from int2 to int8?  Is there any place where I can read more
about this (other than the source code itself? :) )

Thanks!
V.



SQL> CREATE TABLE my_schema.my_table (
  seq bigserial NOT NULL,
  column1 integer NOT NULL,
  column2 bigint NOT NULL,
  column3 bigint NOT NULL,
  column4 smallint NOT NULL,
  column5 bigint NOT NULL,
  column6 bigint NOT NULL,
  column7 bigint NOT NULL,
  CONSTRAINT my_table_pkey PRIMARY KEY (column1, column6, column2, column3),
  CONSTRAINT my_table_seq_key UNIQUE (seq)
) WITH (FILLFACTOR=100, OIDS=FALSE);
CREATE INDEX my_table_el_tmndx ON my_schema.my_table USING btree(column2,
column3);
CREATE INDEX my_table_stream_el_tmndx ON my_schema.my_table USING btree
(column1, column2, column3);
CREATE INDEX my_table_tndx ON my_schema.my_table USING btree (column3);


...
populate table with 1245969 rows
...



SQL> SELECT pg_relation_size('my_schema.my_table');
115990528

SQL> SELECT COUNT(*) FROM my_schema.my_table;
1245969

SQL> SELECT 1.0*pg_relation_size('my_schema.my_table')/(SELECT COUNT(*) FROM
my_schema.my_table);
93.0926275051787003

SQL> VACUUM FULL VERBOSE my_schema.my_table;
INFO:  vacuuming "my_schema.my_table"INFO:  "my_table": found 0 removable,
1245969 nonremovable row versions in 14159 pages
DETAIL:  0 dead row versions cannot be removed yet.
Nonremovable row versions range from 88 to 88 bytes long.
There were 0 unused item pointers.
Total free space (including removable row versions) is 1021564 bytes.
0 pages are or will become empty, including 0 at the end of the table.
1 pages containing 2188 free bytes are potential move destinations.
CPU 0.00s/0.13u sec elapsed 0.13 sec.INFO:  index "my_table_pkey" now
contains 1245969 row versions in 7567 pages
DETAIL:  0 index pages have been deleted, 0 are currently reusable.
CPU 0.07s/0.00u sec elapsed 0.07 sec.INFO:  index "my_table_seq_key" now
contains 1245969 row versions in 3419 pages
DETAIL:  0 index pages have been deleted, 0 are currently reusable.
CPU 0.03s/0.00u sec elapsed 0.03 sec.
INFO:  index "my_table_el_tmndx" now contains 1245969 row versions in 4800
pages
DETAIL:  0 index pages have been deleted, 0 are currently reusable.
CPU 0.05s/0.00u sec elapsed 0.05 sec.INFO:  index "my_table_stream_el_tmndx"
now contains 1245969 row versions in 6179 pages
DETAIL:  0 index pages have been deleted, 0 are currently reusable.
CPU 0.04s/0.01u sec elapsed 0.05 sec.INFO:  index "my_table_tndx" now
contains 1245969 row versions in 3419 pages
DETAIL:  0 index pages have been deleted, 0 are currently reusable.
CPU 0.03s/0.01u sec elapsed 0.04 sec.INFO:  "my_table": moved 0 row
versions, truncated 14159 to 14159 pages
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.00 sec.
Query returned successfully with no result in 390 ms.

SQL> SELECT pg_relation_size('my_schema.my_table');
115990528

SQL> ALTER TABLE my_schema.my_table ALTER COLUMN column4 TYPE int8; -- from
int2
Query returned successfully with no result in 53937 ms.

SQL> SELECT pg_relation_size('my_schema.my_table');
115990528

SQL> VACUUM FULL VERBOSE my_schema.my_table;
INFO:  vacuuming "my_schema.my_table"INFO:  "my_table": found 0 removable,
1245969 nonremovable row versions in 14159 pages
DETAIL:  0 dead row versions cannot be removed yet.
Nonremovable row versions range from 88 to 88 bytes long.
There were 0 unused item pointers.
Total free space (including removable row versions) is 1021564 bytes.
0 pages are or will become empty, including 0 at the end of the table.
1 pages containing 2188 free bytes are potential move destinations.
CPU 0.00s/0.18u sec elapsed 0.19 sec.
INFO:  index "my_table_pkey" now contains 1245969 row versions in 7567 pages
DETAIL:  0 index pages have been deleted, 0 are currently reusable.
CPU 0.04s/0.02u sec elapsed 0.06 sec.INFO:  index "my_table_seq_key" now
contains 1245969 row versions in 3419 pages
DETAIL:  0 index pages have been deleted, 0 are currently reusable.
CPU 0.02s/0.02u sec elapsed 0.04 sec.INFO:  index "my_table_el_tmndx" now
contains 1245969 row versions in 4800 pages
DETAIL:  0 index pages have been deleted, 0 are currently reusable.
CPU 0.03s/0.01u sec elapsed 0.04 sec.INFO:  index "my_table_stream_el_tmndx"
now contains 1245969 row versions in 6179 pages
DETAIL:  0 index pages have been deleted, 0 are currently reusable.
CPU 0.02s/0.04u sec elapsed 0.06 sec.INFO:  index "my_table_tndx" now
contains 1245969 row versions in 3419 pages
DETAIL:  0 index pages have been deleted, 0 are currently reusable.
CPU 0.03s/0.00u sec elapsed 0.03 sec.INFO:  "my_table": moved 0 row
versions, truncated 14159 to 14159 pages
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.00 sec.
Query returned successfully with no result in 451 ms.

SQL> SELECT pg_relation_size('my_schema.my_table');
115990528