Re: pg_stats how-to?

Thanks again! I'll make the change and get those numbers.

Yudhvir

On 5/21/07, Jim C. Nasby <decibel(at)decibel(dot)org> wrote:
> 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)
>

--
Yudhvir Singh Sidhu
408 375 3134 cell