Thread

1. Size functions inconsistent results

   Fabrízio de Royes Mello <fabriziomello@gmail.com> — 2022-02-25T14:58:48Z

   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
   

2. Re: Size functions inconsistent results

   Japin Li <japinli@hotmail.com> — 2022-02-25T15:10:32Z

   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.
   
   
   
   

3. Re: Size functions inconsistent results

   Fabrízio de Royes Mello <fabriziomello@gmail.com> — 2022-02-25T15:16:48Z

   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