简化一个Postgres SQL查询以列出表和索引的大小？

Question

以下的Postgres SQL查询会列出所有模式下的所有表及其大小和索引大小。如果一个表只是一个索引表，它将被显示为100％的索引。

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;

它将产生以下结果：

 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

我已经到了看不清森林的程度，因为所有的树木，它变得有些难以管理。

我在想是否有什么可以简化或者可以省略的地方？如果查询可以变得更加简单，那么列名并不需要保持不变。

- Adam Lindberg

---

请参见https://dev59.com/W3E85IYBdhLWcg3w03Dz。 - Vadzim

Answer 1

我使用以下查询得到了相似的结果（格式略有不同）：

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.

---

尽管没有漂亮的打印效果。 :-( - Adam Lindberg

@AdamLindberg 你可以很容易地将漂亮的打印添加到查询中。我的关键研究点是简化连接和子查询的内容。 - A.H.

唯一的问题是，在 pg_size_pretty(...) 之后排序是基于漂亮大小的字符串值，因此 96 Kb > 960 Kb。 - Adam Lindberg

@AdamLindberg：你可以使用 order by pg_total_relation_size(pg_class.oid) 并在 select 部分添加 pg_size_pretty。 - A.H.

我在一个较新的数据库的9.3.2版本中遇到了"ERROR: division by zero"的错误。 :) - Garen

Answer 2

我想说的是，quzary的回复应该使用oid而不是创建字符串，否则会无法解析回oid。

现在我必须写一篇正式的帖子（也许这就是阻止新手评论的目的？）这里有另一个经过清理和美化的版本：

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;

请注意，添加以下行可能会很有用：

AND c.relkind = 'r'

在p的WHERE子句中。这将限制它仅适用于关系/表，并使代码对表大小的一般摘要有用。

- Sam Mason

Answer 3

首先，为什么不使用CTE，它们可以使您的代码更易读。 然后，您没有返回is_index，所以它似乎是多余的。

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

---

在我看来，这并不更简单或更短。 - Adam Lindberg

也许你是对的，但似乎更容易理解它。我稍微改了一下。 - quzary

Answer 4

不要忘记，pg_relation_size和pg_total_relation_size是不区分大小写的！

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

应该是这样的：

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

所以它也适用于大写字母。 （我花了一段时间才弄清楚这个）

- estani