Index Size
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
On 05/30/2011 08:53 PM, 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.
[snip]
Can some one tell me why index is becoming so large?
How to compress or reduce its size?
It'd help if you included some more details:
- Your PostgreSQL version
- A .sql file that demonstrated the problem,
including your table definitions and index
creation commands.
--
Craig Ringer
Craig Ringer <craig@postnewspapers.com.au> writes:
On 05/30/2011 08:53 PM, Nick Raj wrote:
Cube code provided by postgres contrib folder. It uses the NDBOX structure.
On creating index, it's size increase at a high rate.
[snip]
Can some one tell me why index is becoming so large?
How to compress or reduce its size?
It'd help if you included some more details:
- Your PostgreSQL version
In particular, I wonder whether his version contains this fix:
Author: Robert Haas <rhaas@postgresql.org>
Branch: master [4fa0a23c7] 2010-11-14 21:27:34 -0500
Branch: REL9_0_STABLE Release: REL9_0_2 [e6b380251] 2010-11-14 21:27:34 -0500
Branch: REL8_4_STABLE Release: REL8_4_6 [2519b8268] 2010-11-14 21:27:34 -0500
Branch: REL8_3_STABLE Release: REL8_3_13 [d589e4070] 2010-11-14 21:27:34 -0500
Branch: REL8_2_STABLE Release: REL8_2_19 [e642ca767] 2010-11-14 21:27:34 -0500
Branch: REL8_1_STABLE Release: REL8_1_23 [0e27a7319] 2010-11-14 21:27:34 -0500
Fix bug in cube picksplit algorithm.
Alexander Korotkov
regards, tom lane
On Tue, May 31, 2011 at 8:50 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Craig Ringer <craig@postnewspapers.com.au> writes:
On 05/30/2011 08:53 PM, Nick Raj wrote:
Cube code provided by postgres contrib folder. It uses the NDBOX
structure.
On creating index, it's size increase at a high rate.
[snip]
Can some one tell me why index is becoming so large?
How to compress or reduce its size?It'd help if you included some more details:
- Your PostgreSQL version
In particular, I wonder whether his version contains this fix:
Author: Robert Haas <rhaas@postgresql.org>
Branch: master [4fa0a23c7] 2010-11-14 21:27:34 -0500
Branch: REL9_0_STABLE Release: REL9_0_2 [e6b380251] 2010-11-14 21:27:34
-0500
Branch: REL8_4_STABLE Release: REL8_4_6 [2519b8268] 2010-11-14 21:27:34
-0500
Branch: REL8_3_STABLE Release: REL8_3_13 [d589e4070] 2010-11-14 21:27:34
-0500
Branch: REL8_2_STABLE Release: REL8_2_19 [e642ca767] 2010-11-14 21:27:34
-0500
Branch: REL8_1_STABLE Release: REL8_1_23 [0e27a7319] 2010-11-14 21:27:34
-0500Fix bug in cube picksplit algorithm.
Alexander Korotkov
Is this bug fixed?
postgresql 9.0 having this bug or not?
Thanks
On 30/05/11 20:53, 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.
Here's what I get on 8.4 with a cleaned up test case. It uses the
original poster's data ( output_vehicle.sql ) and just automates adding
rows in powers of 2 and checking the table size.
(Quoted because it is the only way to stop Thunderbird wrapping text in
plain text editing mode - argh!)
nrows | tablesize | totalsize | indexsize | bpr_row | bpr_total | bpr_index
-------+-----------+-----------+-----------+---------+-----------+-----------
0 | 0 | 8192 | 8192 | | |
2 | 8192 | 16384 | 8192 | 4096 | 8192 | 4096
4 | 8192 | 16384 | 8192 | 2048 | 4096 | 2048
8 | 8192 | 16384 | 8192 | 1024 | 2048 | 1024
16 | 8192 | 16384 | 8192 | 512 | 1024 | 512
32 | 8192 | 16384 | 8192 | 256 | 512 | 256
64 | 8192 | 16384 | 8192 | 128 | 256 | 128
128 | 16384 | 65536 | 49152 | 128 | 512 | 384
256 | 24576 | 122880 | 98304 | 96 | 480 | 384
512 | 49152 | 229376 | 180224 | 96 | 448 | 352
1024 | 90112 | 327680 | 237568 | 88 | 320 | 232
2048 | 180224 | 1376256 | 1196032 | 88 | 672 | 584
4096 | 352256 | 2228224 | 1875968 | 86 | 544 | 458
8192 | 696320 | 3751936 | 3055616 | 85 | 458 | 373
16384 | 1384448 | 13254656 | 11870208 | 84 | 809 | 724
19875 | 1679360 | 15466496 | 13787136 | 84 | 778 | 693
(16 rows)
As expected, the bytes cost per table row sans index (bpr_row) tends
down slowly toward a stable value. The index size per row (bpr_index) is
all over the place, but seems consistently pretty huge.
At 19875 rows the index is 90% of the total size, or over 8 times the
size of the table its self.
While I realize that a general-purpose index for multi-dimensional
structures like cubes may not be hugely efficient, is this kind of huge
storage consumption expected? The memory hit will be so bad that it's
likely to be faster to use full table scans.
Run the attached test as:
psql -f test.sql -q regress
where "regress" is the name of the database to do the work in. The
original poster's "output_vehicle.sql" must be in the same directory.
--
Craig Ringer