Simplify an Postgres SQL query for listing table and index sizes?

Question

The following Postgres SQL query will list all tables from all schemas and their sizes and index sizes. If a table is just an index table, it will show up as 100% index. ``` SELECT schema, name, pg_size_pretty(CASE WHEN is_index THEN 0 ELSE s END) AS size, pg_size_pretty(CASE WHEN is_index THEN s ELSE st - s END) AS index, CASE WHEN st = 0 THEN 0 WHEN is_index THEN 100 ELSE 100 - ((s*100) / st) END || '%' as ratio, pg_size_pretty(st) as total FROM (SELECT *, st = s AS is_index FROM (SELECT nspname as schema, relname as name, pg_relation_size(nspname || '.' || relname) as s, pg_total_relation_size(nspname || '.' || relname) as st FROM pg_class JOIN pg_namespace ON (relnamespace = pg_namespace.oid)) AS p) AS pp ORDER BY st DESC LIMIT 30; ``` It will give the following results: ``` schema | name | size | index | ratio | total ----------------+------------------------+---------+---------+-------+--------- public | conf | 4072 kB | 4360 kB | 52% | 8432 kB archive | product_param | 4048 kB…

Accepted Answer

I get comparable results (with different formatting) with this query:

```
select
    nspname as schema,
    relname as name,
    pg_relation_size(pg_class.oid) as size,
    pg_indexes_size(pg_class.oid) as index,
    pg_total_relation_size(pg_class.oid) as total,
    100 * case when relkind = 'i' then pg_relation_size(pg_class.oid) 
                                  else pg_indexes_size(pg_class.oid) end 
        / pg_total_relation_size(pg_class.oid) as i_ratio
from 
    pg_class
    join pg_namespace on relnamespace = pg_namespace.oid
order by 5 desc
```

Simplify an Postgres SQL query for listing table and index sizes?

Solution

Problem

Related problems