On Fri, Feb 25, 2022 at 12:10 PM Japin Li <japi...@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