Thanks again! I'll make the change and get those numbers.
Yudhvir On 5/21/07, Jim C. Nasby <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED] > >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 [EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
-- Yudhvir Singh Sidhu 408 375 3134 cell ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
