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.
>
> regards, tom lane

OK, I solved the mystary or what ever it was. My stupidity mostly.

I started with assumption that a table row would be 40 bytes and index row
would be 20 bytes.

But I found this *little* discrepency between actual database size and number
if rows. Upon further reseatch, I found where I was wrong.

nav=# explain select * from height;
QUERY PLAN
--------------------------------------------------------------------
Seq Scan on height (cost=0.00..1201902.72 rows=80658272 width=12)
(1 row)

nav=# select reltuples from pg_class where relname='height';
reltuples
-------------
8.06583e+07
(1 row)

The number of tuples is 80.6M rather than 8.1M as I said earlier. That +07
there told me what I was doing wrong.. +07 certainly is not a million..

So the estimated table space is 3076 MB and estimated index space is 1538MB
for 100% compaction. The index is actually eating close to 2950MB space which
counts for 50% page usage ratio. It is perfectly OK.

I made a mistake in reading significant figures. No wonder I thought it was
10x bloated.

Shridhar
--
"One thing they don't tell you about doing experimental physics is that
sometimes you must work under adverse conditions ... like a state of
sheer terror."
-- W. K. Hartmann