如何找到Postgres / PostgreSQL表及其索引的磁盘大小

问题描述

我要从Oracle来Postgres，寻找一种以 bytes / MB / GB / etc 甚至更好的方式查找表和索引大小的方法所有表格的大小。在Oracle中，我有一个讨厌的长查询，它查看了user_lobs和user_segments来给出答案。

I'm coming to Postgres from Oracle and looking for a way to find the table and index size in terms of bytes/MB/GB/etc, or even better the size for all tables. In Oracle I had a nasty long query that looked at user_lobs and user_segments to give back an answer.

我认为在Postgres中可以使用 information_schema 表，但我看不到哪里。

I assume in Postgres there's something I can use in the information_schema tables, but I'm not seeing where.

推荐答案

尝试。示例：

SELECT pg_size_pretty(pg_total_relation_size('"<schema>"."<table>"'));

对于所有表，类似于以下内容：

For all tables, something along the lines of:

SELECT
    table_schema || '.' || table_name AS table_full_name,
    pg_size_pretty(pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')) AS size
FROM information_schema.tables
ORDER BY
    pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') DESC;

编辑：这是@phord提交的查询，为方便起见：

Here's the query submitted by @phord, for convenience:

SELECT
    table_name,
    pg_size_pretty(table_size) AS table_size,
    pg_size_pretty(indexes_size) AS indexes_size,
    pg_size_pretty(total_size) AS total_size
FROM (
    SELECT
        table_name,
        pg_table_size(table_name) AS table_size,
        pg_indexes_size(table_name) AS indexes_size,
        pg_total_relation_size(table_name) AS total_size
    FROM (
        SELECT ('"' || table_schema || '"."' || table_name || '"') AS table_name
        FROM information_schema.tables
    ) AS all_tables
    ORDER BY total_size DESC
) AS pretty_sizes;

我已对其稍加修改以使用 pg_table_size（）包括元数据并增加大小。

I've modified it slightly to use pg_table_size() to include metadata and make the sizes add up.

这篇关于如何找到Postgres / PostgreSQL表及其索引的磁盘大小的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！