简化用于列表表和索引大小的Postgres SQL查询？

问题描述：

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

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

我来到一个地步，我可以不见森林的所有树木，它开始变得有点笨拙。

我想知道是否有什么可以简化或冗余吗？如果查询可以变得更简单，那么列不一定保持不变。

另请参见http://stackoverflow.com/questions/2596624/how-do-you-find-the-disk-size-of-a-postgres-postgresql-表和它的索引。 – Vadzim 2014-04-03 09:40:12

答

我得到类似的结果（用不同的格式），与此查询：

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

虽然没有漂亮的打印。 :-( – 2012-04-15 14:05:08

@AdamLindberg）你可以很容易地为查询添加漂亮的打印，我的关键研究点是简化连接和子查询 – 2012-04-15 20:39:02

唯一的问题是在'pg_size_pretty（...）'命令之后排序基于漂亮大小的字符串值，因此96 Kb> 960 Kb。 – 2012-04-17 14:47:12

答

首先为什么不使用CTE他们会让你的代码更具可读性。然后你不回is_index所以它似乎是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;

这不是简单或更短，海事组织。 – 2012-04-05 06:58:03

也许你是对的，但它似乎更容易理解它。我已经改变了一点。 – quzary 2012-04-05 11:07:26

答

我真正想要做的是指出， 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子句。这将仅限于关系/表格，并使代码对于表格大小的总体摘要很有用。

答

并且不要忘记pg_relation_size和pg_total_relation_size不区分大小写！

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

实际上应该是：

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

所以它与上情况下工作过。（花了我一会儿来找出这一个）

简化用于列表表和索引大小的Postgres SQL查询？

相关推荐