PostgreSQL选择结果大小 [英] PostgreSQL select result size

问题描述

我在PostgreSQL DB中有一个表，并在有一些约束的情况下从该表中进行选择，然后我想知道此选择有多少磁盘空间。我知道有一个postgres函数 pg_total_relation_size 可以给我数据库中某些表的大小，但是如何找到子表的大小呢？

I have a table in PostgreSQL DB and make a select from this table with some constraints, and than I want to know how much disk space does this select has. I know that there is a postgres function pg_total_relation_size that gives me the size of some table in DB, but how can I find the 'subtable' size?

有什么想法吗？

我使用PostgreSQL 9.1

I use PostgreSQL 9.1

推荐答案

要获取数据大小，以允许 TOAST 压缩，等等：

To get the data size, allowing for TOAST compression, etc:

regress=> SELECT sum(pg_column_size(devices)) FROM devices WHERE country = 'US';
 sum 
-----
 105
(1 row)

要获取所需的磁盘存储空间，包括块分配开销，标头等，

To get the disk storage required including block allocation overhead, headers, etc etc:

regress=> CREATE TEMPORARY TABLE query_out AS SELECT * FROM devices WHERE country = 'US';
SELECT 3
regress=> SELECT pg_total_relation_size('query_out');
 pg_total_relation_size 
------------------------
                  16384
(1 row)

为什么结果如此不同？因为后一个查询报告的是主表的8k块和TOAST表的8k块的大小。不在乎这些块大部分是空的。

Why are the results so different? Because the latter query is reporting the size of the 8k block for the main table, and the 8k block for the TOAST table. It doesn't care that these blocks are mostly empty.

这篇关于PostgreSQL选择结果大小的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！