PSQL check table size when applying where clause

I know I can check the size of a database table by doing this:

select pg_size_pretty(pg_total_relation_size('schema.table'));

However, is there a way I can query the size of a subsection of the table? For example, all rows where a clause is met:

select pg_size_pretty(pg_total_relation_size('schema.table where type = ''ABC''));

Something like the above (which obviously doesn't work)

1 answer

  • answered 2021-06-10 11:30 a_horse_with_no_name

    There is direct support for this. The only thing I can think of is to sum the size of the rows that satisfy the condition.

    select sum(pg_column_size(t)) 
    from the_table t
    where type = 'ABC';
    

    The above does not include the size of the indexes which pg_total_relation_size() does.