Re: Estimating space required for indexes

On Monday 28 April 2003 20:11, Tom Lane wrote:
> Shridhar Daithankar <shridhar_daithankar(at)persistent(dot)co(dot)in> writes:
> > With these overheads it turns out that,
> > disk space for table= 8.1M*40=324MB
> > disk space for index=8.1M*20*1.5=243MB
> > On disk actually, 5.9GB is gone, as I mentioned earlier.
>
> Into what? Look in the database directory and show us the file sizes.
> contrib/pgstattuple might be useful as well.

OK, whatever I picked up from admin. guide regarding disk usage. I hope this
is useful..

nav=# \d height
Table "public.height"
Column | Type | Modifiers
--------+------+-----------
x | real |
y | real |
z | real |
Indexes: height_index btree (x, y)
nav=# \di
List of relations
Schema | Name | Type | Owner | Table
--------+--------------+-------+-------+--------
public | height_index | index | ajit | height
(1 row)

nav=# select relfilenode,relpages from pg_class where relname='height';
relfilenode | relpages
-------------+----------
16977 | 395320
(1 row)

nav=# select relfilenode,relpages from pg_class where relname='height_index';
relfilenode | relpages
-------------+----------
16988 | 376840
(1 row)
nav=# SELECT c2.relname, c2.relpages
nav-# FROM pg_class c, pg_class c2, pg_index i
nav-# WHERE c.relname = 'height' AND
nav-# c.oid = i.indrelid AND
nav-# c2.oid = i.indexrelid
nav-# ORDER BY c2.relname;
relname | relpages
--------------+----------
height_index | 376840
(1 row)

[ajit(at)jejuri dbs]$ du -h
3.6M ./base/1
3.6M ./base/16975
5.9G ./base/16976
6.0G ./base
132K ./global
8.0K ./pg_clog
128M ./pg_xlog
6.1G .

Shridhar

--
"The algorithm to do that is extremely nasty. You might want to mug
someone with it."
-- M. Devine, Computer Science 340