Skip site navigation (1) Skip section navigation (2)

Re: vacuum full

From: george young <gry(at)ll(dot)mit(dot)edu>
To: "Henrik Steffen" <steffen(at)city-map(dot)de>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: vacuum full
Date: 2002-11-21 18:50:36
Message-ID: 20021121135036.788d3fcb.gry@ll.mit.edu (view raw or flat)
Thread:
Lists: pgsql-performance
On Thu, 21 Nov 2002 19:20:16 +0100
"Henrik Steffen" <steffen(at)city-map(dot)de> wrote:

> sorry, didn't notice your message posted to pgsql-general...
> 
> but is there any method to see the size in bytes a particular index
> for a particular table takes?

For a table foo, do:
psql mydatabase
-- first find the index name:
mydatabase=> \d foo
...
Indexes: foo_pkey unique btree (mykey)
-- now find the unix file name of the index:
mydatabase=> select relfilenode from pg_class where relname='foo_pkey';
 relfilenode 
-------------
       18122
-- Thus the file name of the index is "18122".
\q
# now go and look for the file:
unixprompt> su postgres
Password:
postgres> cd /var/lib/pgsql/data/base/????
postgres> ls -l 18122
-rw-------    1 postgres daemon    7471104 Nov 21 12:52 18122

Thus the index for table foo is 7.4 MBytes in size.  What I left
out is the ???? directory name above.  I find it by educated guess.

Does someone know the right way to map from database name to 
data directory name?

-- George

> From: "scott.marlowe" <scott(dot)marlowe(at)ihs(dot)com>
> To: "Henrik Steffen" <steffen(at)city-map(dot)de>
> Cc: <pgsql-performance(at)postgresql(dot)org>
> Sent: Thursday, November 21, 2002 6:32 PM
> Subject: Re: [PERFORM] vacuum full
> 
> 
> > On Thu, 21 Nov 2002, Henrik Steffen wrote:
> > >
> > > how often should "vacuum full" usually be run ?
> >
> > I recommend nightly.  Also, check index size.  If they are growing, you
> > may want to reindex each night or week as well.

-- 
 I cannot think why the whole bed of the ocean is
 not one solid mass of oysters, so prolific they seem. Ah,
 I am wandering! Strange how the brain controls the brain!
	-- Sherlock Holmes in "The Dying Detective"

In response to

Responses

pgsql-performance by date

Next:From: Steve CrawfordDate: 2002-11-21 18:56:29
Subject: Re: [ADMIN] H/W RAID 5 on slower disks versus no raid on faster HDDs
Previous:From: Stephan SzaboDate: 2002-11-21 18:35:37
Subject: Re: stange optimizer results

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group