Re: pg_stats how-to?

From: "Jim C(dot) Nasby" <decibel(at)decibel(dot)org>
To: Y Sidhu <ysidhu(at)gmail(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: pg_stats how-to?
Date: 2007-05-21 17:24:06
Message-ID: 20070521172405.GF62346@nasby.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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)

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Richard Huxton 2007-05-21 17:34:11 Re: Rewriting DISTINCT and losing performance
Previous Message Chuck D. 2007-05-21 17:13:23 Re: Rewriting DISTINCT and losing performance