Thread: How can I find out the space used on disk for a table/index
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? Thanks, Viktor
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