Re: Cube Index Size

On 30.05.2011 21:51, Nick Raj wrote:
> 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?

Which version of PostgreSQL are you using? I wonder if this could be due
to the bug in cube's picksplit algorithm that was fixed a while ago:

http://archives.postgresql.org/message-id/AANLkTimC8W6guHpWJeWdjQA6WGoVH-7qG9Ar4pem2N2V@mail.gmail.com

If not, please post a self-contained test case to create and populate
the table, so that others can easily try to reproduce it.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com