Re: Subject: bool / vacuum full bug followup part 2

On Mon, 6 May 2002, Steve Lane wrote:

> I'd like to interject a short novice question, because I'd like to check
> this:
>
> What's the best way to see how much space my indexes take up?

You'll need a program called oid2name, found in the contrib directory of
the postgresql source distribution. Many Linux distros include it
automagically, but if not, it's a simple install.

On my machine, I build postgresql from source, and I build it in the
/usr/local/src/postgresql-x.y.z directory, where x.y.z is the version
number. so, for 7.2.1, I would do this:

su -
(enter root password)
cd /usr/local/src/postgresql-7.2.1/contrib/oid2name
make
make install
exit (back to being joe regular user)

then try entering oid2name. Assuming you have /usr/local/pgsql/bin in
your path, it should run and tell you the name of all your databases.

On my local test box, I get this:
All databases:
---------------------------------
16556 = scott.marlowe
1126697 = test
1 = template1
16555 = template0

Using the -d switch, you can get a list of all the oids used by a given
database, like so:

oid2name -d scott.marlowe
All tables from database "scott.marlowe":
---------------------------------
126572 = accounts
1126708 = accounts_pkey
126566 = branches
1126706 = branches_pkey
126575 = history
126569 = tellers
1126707 = tellers_pkey
16557 = test
1126709 = test_id_dx

Now, lastly, you need to be the postgres super user to do this, replacing
db with the name of the database you wanna see, and dx with the name of
the index you want to know about: (note this should be on one line, but it
ran pretty long, so I have a \ continuation character in there)

du -s $PGDATA/base/`oid2name |grep db|cut -d ' ' -f 1`/`oid2name -d \
db|grep dx|cut -d ' ' -f 1`

or scriptify it by putting that command into a file called
/usr/local/pgsql/bin/tsize that looks like this:

#!/bin/bash
du -s $PGDATA/base/`oid2name |grep $1|cut -d ' ' -f 1`/`oid2name -d \
$1|grep $2|cut -d ' ' -f 1`

(don't forget to chmod 755 it so it's executable)
and call it like so:

tsize dbname tablename

Hope that helps!

explanation:

What the above script does is kind like this:

cd $PGDATA/base
oid2name
(find oid of your database in list)
cd oid_of_your_database
oid2name -d your_database
(find oid of your index)
du -s oid_of_your_index