How can I find out the space used on disk for a table/index

On Sunday 12 July 2009 21:07:56 Viktor Rosenfeld wrote:

Hi,

I want to judge the performance gain of additional indexes against the
space they consume on disk. Currently I do a VACUUM FULL ANALYZE and
then take the space used by the base directory and do some
calculations. This is time consuming, error-prone and unreliable.

Is there a quick way to compute the space used on disk by an index (in
MB or blocks) using the statistics tables?

Read the manual:
http://www.postgresql.org/docs/current/static/functions-admin.html#FUNCTIONS-
ADMIN-DBSIZE

e.g. SELECT pg_size_pretty(pg_index_size('indexname'));

Using the system tables you can easily do that for all indices in your
database.

Andres

On Sun, Jul 12, 2009 at 8:45 PM, Andres Freund<andres@anarazel.de> wrote:

On Sunday 12 July 2009 21:07:56 Viktor Rosenfeld wrote:

Hi,

I want to judge the performance gain of additional indexes against the
space they consume on disk.  Currently I do a VACUUM FULL ANALYZE and
then take the space used by the base directory and do some
calculations.  This is time consuming, error-prone and unreliable.

Also counter-productive since vacuum full actually causes indexes to
grow, not shrink.

As Andres said, use the pg_*_size functions. You may also want to run
a regular vacuum verbose and use the number of pages from that. That
would have the side benefit of giving you an idea of how densely
packed or bloated each index is which may be a factor in measuring
performance.

--
greg
http://mit.edu/~gsstark/resume.pdf

On Mon, 2009-07-13 at 00:53 +0100, Greg Stark wrote:

Also counter-productive since vacuum full actually causes indexes to
grow, not shrink.

As a result, your indexes may be quite bloated. Consider REINDEXing them
to get them back to sensible sizes, then avoiding VACUUM FULL.

--
Craig Ringer