up vote 3 down vote favorite
share [g+] share [fb]

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 | 3968 kB | 50%   | 8016 kB                                                   
 public         | conf_pkey              | 0 bytes | 4320 kB | 100%  | 4320 kB
 archive        | product_value          | 1568 kB | 1136 kB | 43%   | 2704 kB
 public         | param_mapping          | 1472 kB | 832 kB  | 37%   | 2304 kB
 archive        | supplie_price          | 944 kB  | 896 kB  | 49%   | 1840 kB
 public         | product_param_param_id | 0 bytes | 1552 kB | 100%  | 1552 kB
 archive        | product_param_id       | 0 bytes | 1536 kB | 100%  | 1536 kB

I've come to a point where I can't see the forest for all the trees, and it's starting to get a bit unwieldy.

I'm wondering if there's anything in it that can be simplified or made redundant? The columns mustn't necessarily stay the same if the query can be made much simpler.

link|improve this question
100% accept rate
feedback

2 Answers

up vote 1 down vote accepted

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
link|improve this answer
Albeit without pretty printing. :-( – Adam Lindberg Apr 15 at 14:05
@AdamLindberg You can add pretty printing to the query quite easily. My key research point was the simplification of the join stuff and the subquery stuff. – A.H. Apr 15 at 20:39
The only problem is that ordering after pg_size_pretty(...) orders based on the string value of the pretty size, thus 96 Kb > 960 Kb. – Adam Lindberg Apr 17 at 14:47
@AdamLindberg: you can use order by pg_total_relation_size(pg_class.oid) and add pg_size_pretty to the select part. – A.H. Apr 17 at 17:30
feedback
up vote 0 down vote

First of all why not to use CTEs they'll make you code a bit more readable. Then you do not return is_index so it seems to be redundunt

;with p as (
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)
),
pp as (
SELECT *,
        case when st = s then 0 else s end as size,
        case when st = s then s else st-s end as index

   FROM p
)
select schema,
       name,
       pg_size_pretty(size) as size,        
       pg_size_pretty(index) as index,
       (case st 
             when 0 then 0 
             else index*100 / st 
        end) || '%' ratio,
       st total
 from pp
 order by st desc limit 30;
link|improve this answer
PostgreSQL needs the statement termination character (;) at the end of the statement, not in front of the statement. – a_horse_with_no_name Apr 4 at 17:04
It's not really simpler or shorter, IMO. – Adam Lindberg Apr 5 at 6:58
MAybe you're right but it seems to be easier to understand it. I've changed it little more. – quzary Apr 5 at 11:07
feedback

Your Answer

 
or
required, but never shown
discard

Not the answer you're looking for? Browse other questions tagged or ask your own question.