Re: pg_stats how-to?

On Fri, May 18, 2007 at 04:26:05PM -0700, Y Sidhu wrote:
> >To answer your original question, a way to take a look at how bloated
> >your tables are would be to ANALYZE, divide reltuples by relpages from
> >pg_class (gives how many rows per page you have) and compare that to 8k
> >/ average row size. The average row size for table rows would be the sum
> >of avg_width from pg_stats for the table + 24 bytes overhead. For
> >indexes, it would be the sum of avg_width for all fields in the index
> >plus some overhead (8 bytes, I think).
> >
> >An even simpler alternative would be to install contrib/pgstattuple and
> >use the pgstattuple function, though IIRC that does read the entire
> >relation from disk.
> >--
> >Jim Nasby decibel(at)decibel(dot)org
> >EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
> >
>
> Here are my results:
>
> a. SELECT sum(reltuples)/sum(relpages) as rows_per_page FROM pg_class;
>
> I get 66
>
> b. SELECT (8000/(sum(avg_width)+24)) as table_stat FROM pg_stats;
>
> I get 1

And those results will be completely meaningless because they're
covering the entire database (catalog tables included). You need to
compare the two numbers on a table-by-table basis, and you'd also have
to ignore any small tables (say smaller than 1000 pages). Also, a page
is 8192 bytes in size (though granted there's a page header that's
something like 16 bytes).
--
Jim Nasby decibel(at)decibel(dot)org
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)