Size functions inconsistent results

On Fri, 25 Feb 2022 at 22:58, Fabrízio de Royes Mello <fabriziomello@gmail.com> wrote:

Hi all,

While doing some work using our functions [1] for calculate relations size
I noticed an inconsistency between pg_total_relation_size and calculate
everything separately, have a look in this example:

fabrizio=# create table test_size (id bigserial primary key, toast_column
text);
CREATE TABLE

fabrizio=# insert into test_size (toast_column)

select repeat('X'::text, pg_size_bytes('1MB')::integer)
from generate_series(1,1000);
INSERT 0 1000

fabrizio=# with relations as (
select schemaname, relname, relid
from pg_stat_user_tables
where relname = 'test_size'
),
sizes as (
select
schemaname,
r.relname,

pg_total_relation_size(relid) AS total_bytes,

pg_relation_size(relid, 'main') +
pg_relation_size(relid, 'init') +
pg_relation_size(relid, 'fsm') +
pg_relation_size(relid, 'vm') AS heap_bytes,
pg_indexes_size(relid) AS index_bytes,
pg_table_size(reltoastrelid) AS toast_bytes
from relations r
join pg_class on pg_class.oid = r.relid
)
select
total_bytes, heap_bytes, index_bytes, toast_bytes,
(total_bytes = (heap_bytes+index_bytes+toast_bytes)) as "Equal?",
(total_bytes - (heap_bytes+index_bytes+toast_bytes)) as "Diff"
from sizes;
total_bytes | heap_bytes | index_bytes | toast_bytes | Equal? | Diff
-------------+------------+-------------+-------------+--------+--------
14000128 | 90112 | 40960 | 13688832 | f | 180224
(1 row)

I want to calculate separately HEAP, INDEXES and TOAST (including indexes)
sizes but it seems it's a bit inconsistent with pg_total_relation_size.

Is it correct or am I missing something?

I think, you forget the index size of toast table.

with relations as (
select schemaname, relname, relid
from pg_stat_user_tables
where relname = 'test_size'
),
sizes as (
select
schemaname,
r.relname,

pg_total_relation_size(relid) AS total_bytes,

pg_relation_size(relid, 'main') +
pg_relation_size(relid, 'init') +
pg_relation_size(relid, 'fsm') +
pg_relation_size(relid, 'vm') AS heap_bytes,
pg_indexes_size(relid) AS index_bytes,
pg_table_size(reltoastrelid) + pg_indexes_size(reltoastrelid) AS toast_bytes
from relations r
join pg_class on pg_class.oid = r.relid
)
select
total_bytes, heap_bytes, index_bytes, toast_bytes,
(total_bytes = (heap_bytes+index_bytes+toast_bytes)) as "Equal?",
(total_bytes - (heap_bytes+index_bytes+toast_bytes)) as "Diff"
from sizes;

--
Regrads,
Japin Li.
ChengDu WenWu Information Technology Co.,Ltd.

On Fri, Feb 25, 2022 at 12:10 PM Japin Li <japinli@hotmail.com> wrote:

I think, you forget the index size of toast table.

with relations as (
select schemaname, relname, relid
from pg_stat_user_tables
where relname = 'test_size'
),
sizes as (
select
schemaname,
r.relname,

pg_total_relation_size(relid) AS total_bytes,

pg_relation_size(relid, 'main') +
pg_relation_size(relid, 'init') +
pg_relation_size(relid, 'fsm') +
pg_relation_size(relid, 'vm') AS heap_bytes,
pg_indexes_size(relid) AS index_bytes,
pg_table_size(reltoastrelid) + pg_indexes_size(reltoastrelid) AS

toast_bytes

from relations r
join pg_class on pg_class.oid = r.relid
)
select
total_bytes, heap_bytes, index_bytes, toast_bytes,
(total_bytes = (heap_bytes+index_bytes+toast_bytes)) as "Equal?",
(total_bytes - (heap_bytes+index_bytes+toast_bytes)) as "Diff"
from sizes;

Ahh perfect... thanks... make sense because pg_table_size don't compute the
indexes size, now it worked:

fabrizio=# with relations as (
select schemaname, relname, relid
from pg_stat_user_tables
where relname = 'test_size'
),
sizes as (
select
schemaname,
r.relname,

pg_total_relation_size(relid) AS total_bytes,

pg_relation_size(relid, 'main') +
pg_relation_size(relid, 'init') +
pg_relation_size(relid, 'fsm') +
pg_relation_size(relid, 'vm') AS heap_bytes,
pg_indexes_size(relid) AS index_bytes,
pg_table_size(reltoastrelid) + pg_indexes_size(reltoastrelid) AS
toast_bytes
from relations r
join pg_class on pg_class.oid = r.relid
)
select
total_bytes, heap_bytes, index_bytes, toast_bytes,
(total_bytes = (heap_bytes+index_bytes+toast_bytes)) as "Equal?",
(total_bytes - (heap_bytes+index_bytes+toast_bytes)) as "Diff"
from sizes;
total_bytes | heap_bytes | index_bytes | toast_bytes | Equal? | Diff
-------------+------------+-------------+-------------+--------+------
14622720 | 65536 | 40960 | 14516224 | t | 0
(1 row)

Regards,

--
Fabrízio de Royes Mello