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

Index Size

From: Nick Raj <nickrajjain(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Index Size
Date: 2011-05-30 12:53:23
Message-ID: BANLkTimJ+iOQz8=OQv3dbyQRnZFJdU+abw@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-general
Hi,

Cube code provided by postgres contrib folder. It uses the NDBOX structure.
On creating index, it's size increase at a high rate.

On inserting some tuple and creating indexes its behaviour is shown below.

1. When there is only one tuple
    select pg_size_pretty(pg_relation_
size('cubtest'));   //Table size without index
 pg_size_pretty
----------------
 8192 bytes
(1 row)

select pg_size_pretty(pg_total_relation_size('cubtest')); //Table size with
index
 pg_size_pretty
----------------
 16 kB
(1 row)

i.e. Index size in nearly 8kB

2. When tuples are 20,000

Table Size without index - 1.6 MB
Table Size with index - 11 MB
i.e. Index size is nearly 9.4 MB

3. When tuples are 5 lakh

Table Size without index - 40 MB
Table Size with index - 2117 MB
i.e. Index size is nearly 2077 MB ~ 2GB
It is taking nearly 20-25 min for creating index for 5 lakh tuples.

Can some one tell me why index is becoming so large?
How to compress or reduce its size?

Thanks
Nick

Responses

pgsql-general by date

Next:From: Nicholson, Brad (Toronto, ON, CA)Date: 2011-05-30 13:08:03
Subject: Re: Inspecting a DB - psql or system tables ?
Previous:From: Sairam KrishnamurthyDate: 2011-05-30 12:35:50
Subject:

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