Thread: 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.