1

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

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

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: 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';

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

Any ideas there too?

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
JAugust
  • 557
  • 1
  • 5
  • 14

1 Answers1

1

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).

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)

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.

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.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • Thanks a lot, Laurenz Albe. Appreciate your answer. I think I was not so clear about what I wanted in my question. I am trying to see how much space it takes for storing the statistics information by `ANALYSE` in PostgreSQL. For example, I have the `census` table. I did `ANALYSE` on the mentioned table. Some entries are added to `pg_stats` and I wanted to know how much space these new entries are taking. My aim is to study the total size of histograms produced by different `ALTER TABLE .. SET ` commands and subsequent variation in selectivity. – JAugust Oct 20 '18 at 05:29
  • That's exactly what I showed: the histogram for `pg_type.typname` occupies 1269 bytes. – Laurenz Albe Oct 20 '18 at 12:29
  • Yeah sure, Thanks. I am not understanding the only aspect then. I am unable to see tablename='census' anywhere which cause some confusion in my mind still. Can you explain to me why is it picking only information from analyzing census table? Thanks a lot for explaining in advance. – JAugust Oct 20 '18 at 18:03
  • Oh, I see. I have used a different table and column name for demonstration purposes, you'll have to replace them with your table and column name. I have added a first paragraph to clarify that. – Laurenz Albe Oct 22 '18 at 07:25