在一个查询 POSTGRES 中获取多个表的大小? [英] Get the Size of Multiple Tables in One Query POSTGRES?

问题描述

继之前关于关系大小的问题:

这个查询:

query = "CREATE TEMPORARY TABLE query_out AS SELECT * FROM users WHERE is_admin = false"
ActiveRecord::Base.connection.execute(query)

将生成一个临时表并插入此查询中的所有记录，即

will generate a temporary table and insert all the records from this query i.e

SELECT * FROM users WHERE is_admin = false

然后

ActiveRecord::Base.connection.execute("SELECT pg_size_pretty(pg_relation_size('query_out'))")

我只得到一张桌子的大小.

I am only getting the the size of one table.

我需要做什么才能在一个查询中查询多个表的大小?

What doI need to do so that I can the size of multiple tables in one single query?

任何帮助将不胜感激.

谢谢

它将给出临时表的大小.

It will give the size of that temporary table.

推荐答案

以下 select 查询将返回所有表及其大小的

Following select query will returns all the table and its size's

SELECT
   relname as mytable,
   pg_size_pretty(pg_relation_size(relid)) As size
   FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC;

用这个选择创建一个视图

Create a VIEW with this select

CREATE VIEW vTableAndSize AS 
SELECT
   relname as mytable,
   pg_size_pretty(pg_relation_size(relid)) As size
   FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC;

现在你可以查询这个视图以获得这样的大小

and now you can query on this view to get the size like this

SELECT mytable,size 
       FROM vTableAndSize WHERE mytable in ('table1','table2')

<小时>

根据 OP 的评论

CREATE VIEW vTableAndSize_1 as 
SELECT
   relname as mytable,
   (pg_relation_size(relid)) As size
   FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC;

并使用

/* Get sum of specific tables */
SELECT pg_size_pretty(sum(size)) tablesizesum 
       FROM vTableAndSize_1 WHERE mytable in ('table1','table2')

/* Get sum of all tables */
SELECT pg_size_pretty(sum(size)) tablesizesum 
       FROM vTableAndSize_1

<小时>

在您的 PostgreSQL 数据库中创建 vTableAndSize_1 并在前端进行如下查询(我不熟悉 Ruby)

---

Create vTableAndSize_1 in your PostgreSQL database and query like below in your front end(am not familiar with Ruby)

ActiveRecord::Base.connection.execute("SELECT pg_size_pretty(sum(size)) FROM vTableAndSize_1 
WHERE mytable in ('table1','table2')")

这篇关于在一个查询 POSTGRES 中获取多个表的大小?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！