查找给定表PostgreSQL的直方图大小 [英] Finding the histogram size for a given table PostgreSQL

问题描述

我有一个名为 census 的PostgreSQL表.我已经在表上执行了 ANALYSE 命令，并且统计信息记录在 pg_stats 中.

I have a PostgreSQL table named census. I have performed the ANALYSE command on the table, and the statistics are recorded in pg_stats.

此 pg_stats 中的其他条目也可能来自其他数据库表.

There are other entries in this pg_stats from other database tables as can be expected.

但是，我想知道仅用于存储普查表的 histogram_bounds 的空间.有什么好方法吗?

However, I wanted to know the space consumed for storing the histogram_bounds for the census table alone. Is there a good and fast way for it?

PS:我尝试将 pg_stats 表转储到磁盘上以使用

PS: I have tried dumping the pg_stats table onto the disk to measure the memory using

select * into copy_table from census where tablename='census';

但是，它由于伪类型 anyarray 而失败.

However, it failed because of the pseudo-type anyarray.

那里有什么主意吗?

推荐答案

在下面，出于演示目的，我将表 pg_type 及其列 typname 用作表.将它们替换为表和列名称，以获取适合您情况的答案(您没有说出您对哪一列感兴趣).

In the following I use the table pg_type and its column typname for demonstration purposes. Replace these with your table and column name to get the answer for your case (you didn't say which column you are interested in).

您可以使用 pg_column_size 函数获取 any 列的大小:

You can use the pg_column_size function to get the size of any column:

SELECT pg_column_size(histogram_bounds)
FROM pg_stats
WHERE schemaname = 'pg_catalog'
  AND tablename = 'pg_type'
  AND attname = 'typname';

 pg_column_size 
----------------
           1269
(1 row)

要将 anyarray 转换为常规数组，可以先将其转换为 text ，然后转换为所需的数组类型:

To convert an anyarray to a regular array, you can first cast it to text and then to the desired array type:

SELECT histogram_bounds::text::name[] FROM pg_stats ...

如果测量转换后的数组的大小，则会发现它比上面的结果大得多.

If you measure the size of that converted array, you'll notice that it is much bigger than the result above.

原因是 pg_column_size 测量磁盘上的实际大小，并且 histogram_bounds 足够大，无法存储在TOAST表中，该表将在此处进行压缩.转换后的数组未压缩，因为它没有存储在表中.

The reason is that pg_column_size measures the actual size on disk, and histogram_bounds is big enough to be stored out of line in the TOAST table, where it will be compressed. The converted array is not compressed, because it is not stored in a table.

这篇关于查找给定表PostgreSQL的直方图大小的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！