Finding the histogram size for a given table PostgreSQL

Question

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 · Accepted Answer · 2018-10-22 07:24:52Z

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.

edited Oct 22, 2018 at 7:24

answered Oct 20, 2018 at 4:52

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

JAugust Over a year ago

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 <value> commands and subsequent variation in selectivity.

Laurenz Albe Over a year ago

That's exactly what I showed: the histogram for pg_type.typname occupies 1269 bytes.

JAugust Over a year ago

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.

Laurenz Albe Over a year ago

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.

Collectives™ on Stack Overflow

Finding the histogram size for a given table PostgreSQL

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related