Thread: Why index occupy less amount of space than the table with same structure.
Why index occupy less amount of space than the table with same structure.
From
Tadipathri Raghu
Date:
Hi All,
Here is small testing done by my end and am curious to know the reason. Please find the example given below:-
postgres=# create table size_test(id int);
CREATE TABLE
postgres=# insert into size_test VALUES (generate_series(1,1000000));
INSERT 0 1000000
postgres=# select pg_size_pretty(pg_relation_size('size_test'));
pg_size_pretty
----------------
31 MB
(1 row)
CREATE TABLE
postgres=# insert into size_test VALUES (generate_series(1,1000000));
INSERT 0 1000000
postgres=# select pg_size_pretty(pg_relation_size('size_test'));
pg_size_pretty
----------------
31 MB
(1 row)
postgres=# create index isize_test on size_test(id);
CREATE INDEX
postgres=# select pg_size_pretty(pg_relation_size('isize_test'));
pg_size_pretty
----------------
17 MB
(1 row)
CREATE INDEX
postgres=# select pg_size_pretty(pg_relation_size('isize_test'));
pg_size_pretty
----------------
17 MB
(1 row)
postgres=# select pg_size_pretty(pg_total_relation_size('size_test'));
pg_size_pretty
----------------
48 MB
(1 row)
pg_size_pretty
----------------
48 MB
(1 row)
I like to know here is, I have created a table with one column and the index is on one column only, so why is the space occupied differently, almost all half of the space of the table and why not full. Could please you explain on this. And what exactly the postgres architecture treat on Index table and Ordinary table.
Thanks all in Advance
Regards
Raghavendra
Re: Why index occupy less amount of space than the table with same structure.
From
Alban Hertroys
Date:
On 28 Mar 2010, at 10:05, Tadipathri Raghu wrote: > Hi All, ... > I like to know here is, I have created a table with one column and the index is on one column only, so why is the spaceoccupied differently, almost all half of the space of the table and why not full. Could please you explain on this.And what exactly the postgres architecture treat on Index table and Ordinary table. For one thing: The table holds information regarding to which transactions each row is visible (the xid) whereas the indexdoes not. Alban Hertroys -- Screwing up is an excellent way to attach something to the ceiling. !DSPAM:737,4baf3c0010411382482531!
Re: Why index occupy less amount of space than the table with same structure.
From
Tadipathri Raghu
Date:
Hi Alban,
Thank you for the update.
For one thing: The table holds information regarding to which transactions each row is visible (the xid) whereas the index does not.
What would be the each value of the xid, like 8 bytes,or 32 bytes..which causing the table to hold what index is not and the space occupied is exactly half of the table in indexes. Can you explain a bit on this.
Thanks in Advance
Regards
Raghavendra
On Sun, Mar 28, 2010 at 4:52 PM, Alban Hertroys <dalroi@solfertje.student.utwente.nl> wrote:
On 28 Mar 2010, at 10:05, Tadipathri Raghu wrote:
> Hi All,
...For one thing: The table holds information regarding to which transactions each row is visible (the xid) whereas the index does not.
> I like to know here is, I have created a table with one column and the index is on one column only, so why is the space occupied differently, almost all half of the space of the table and why not full. Could please you explain on this. And what exactly the postgres architecture treat on Index table and Ordinary table.
Alban Hertroys
--
Screwing up is an excellent way to attach something to the ceiling.
!DSPAM:1046,4baf3bfb10411932744907!
Re: Why index occupy less amount of space than the table with same structure.
From
Alban Hertroys
Date:
On 29 Mar 2010, at 6:03, Tadipathri Raghu wrote: > Hi Alban, > > Thank you for the update. > > For one thing: The table holds information regarding to which transactions each row is visible (the xid) whereas the indexdoes not. > > What would be the each value of the xid, like 8 bytes,or 32 bytes..which causing the table to hold what index is not andthe space occupied is exactly half of the table in indexes. Can you explain a bit on this. I'm pretty sure the documentation explains this better than I can. Alban Hertroys -- Screwing up is an excellent way to attach something to the ceiling. !DSPAM:737,4bb0618a10411369417804!