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? Regards, [1] https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADMIN-DBSIZE -- Fabrízio de Royes Mello