Re: Estimating space required for indexes

On Monday 28 April 2003 20:11, Tom Lane wrote:
> Shridhar Daithankar <shridhar_daithankar@persistent.co.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@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