5

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.

Adam Lindberg
  • 16,447
  • 6
  • 65
  • 85
  • See also http://stackoverflow.com/questions/2596624/how-do-you-find-the-disk-size-of-a-postgres-postgresql-table-and-its-indexes. – Vadzim Apr 03 '14 at 09:40

4 Answers4

3

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
A.H.
  • 63,967
  • 15
  • 92
  • 126
  • Albeit without pretty printing. :-( – Adam Lindberg Apr 15 '12 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 '12 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 '12 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 '12 at 17:30
  • 4
    I get "ERROR: division by zero" on 9.3.2 in a newish DB. :) – Garen Dec 10 '13 at 17:50
1

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;
quzary
  • 285
  • 1
  • 4
1

All I really want to do is point out that quzary's response should be using oid's and not creating strings that will fail to get parsed back to oid's.

Now I've got to write a proper post (maybe this is the point of stopping newbies from commenting?) here's another cleaned and prettied up version:

WITH p AS (
  SELECT n.nspname AS schema,
         c.relname AS name,
         pg_relation_size(c.oid) AS s,
         pg_total_relation_size(c.oid) AS st
  FROM pg_class c, pg_namespace n
  WHERE c.relnamespace = n.oid
)
SELECT schema, name,
  pg_size_pretty(s) AS size,        
  pg_size_pretty(st - s) AS index,
  (100.0 * s / NULLIF(st, 0))::numeric(10,1) AS "% data of total",
  st AS total
FROM p
ORDER BY st DESC
LIMIT 30;

Note that it may be useful to add in the following line:

AND c.relkind = 'r'

in the WHERE clause of p. That will limit it to relations/tables only and makes the code useful for a general summary of table sizes.

Sam Mason
  • 15,216
  • 1
  • 41
  • 60
0

And don't forget that pg_relation_size and pg_total_relation_size are case insensitive!

pg_relation_size(nspname || '.' || relname)

Should actually be:

pg_relation_size('"' || nspname || '.' || relname || '"')

so it works with upper cases too. (Took me a while to figure this one out)

estani
  • 24,254
  • 2
  • 93
  • 76