Re: [GENERAL] [ADMIN] calculating table and index size

[Günce Kaya]

Hello,

Thanks for helpful answers and sharing all of your knowledge about this
issue. Your knowledge gave me ideas and made it more clear.

Thank all of you again.

Best regards,

Gunce

On Fri, Apr 7, 2017 at 6:30 PM, Guillaume Lelarge 
wrote:

> Le 7 avr. 2017 4:58 PM, "Alban Hertroys"  a écrit :
>
> On 7 April 2017 at 09:11, Günce Kaya  wrote:
> > Hi again,
> >
> > Sorry for delay.
> >
> > Guillaume, I read your answer for first question but It's not clear to
> me.
> > The table has a column and index also use that column. so in that
> example, I
> > think table size and index size should be equal. Why these are not equal?
>
> If I understand correctly, the table only has 1 (integer) column and
> all those 1,400,000 rows have the same value?
>
>
> That's what I also understood.
>
>
>
>
> Then the table has to store each row separately and thus has to store
> the same value repeatedly. It also has to store some meta-data, such
> as visibility information.
>
>
> The meta data is the important stuff here. You have around seven system
> columns for each row, bringing the row size from a mere 4 bytes to
> something a bit more than 30 bytes.
>
>
> The index on the other hand (assuming a btree index) knows that there
> is only a single value in the table and therefore only stores a single
> value, but it has to reference each row in the table that contains
> that value.
>
>
> Not true for a btree index. The value is stored as many times as it
> appears on the table.
>
> True on a gin index IIRC
>
>
>
>
> So the table and the index are storing different things, but the total
> size of each row/index node for that single integer column is of the
> same order of magnitude. That's why they are similar in size.
>
> If you would add another integer column to your table and VACUUM FULL
> the table, the table would be about double its size, but the index
> would stay the same size.
>
>
> The table wouldn't double in size. It would grow but not that much. Though
> I agree the index would stay the same.
>
>
> Regards,
> Alban.
>
>
> > On Wed, Apr 5, 2017 at 1:02 PM, Steven Chang 
> > wrote:
> >>
> >> Hello,
> >>
> >> try pgstattuple() and pgstatindex() , I think you will figure it
> out.
> >>
> >> Steven
> >>
> >> 2017-04-05 16:56 GMT+08:00 Guillaume Lelarge :
> >>>
> >>> Hi,
> >>>
> >>> 2017-04-05 9:44 GMT+02:00 Günce Kaya :
> 
>  Hi all,
> 
>  I have some questions about calculating table and index size.
> 
>  I have a dummy table which has an integer column and its index. The
>  table has 140 rows and all of rows are same thats value is
> 2000.
>  Table size is 50MB and index size is 31MB. Why there is too much size
>  difference between table and its index? what happen on data files
> when we
>  add index?
> 
> >>>
> >>> You have metadata informations in the table datafiles that you don't
> have
> >>> on the index datafiles. For example, all the system columns for each
> line.
> >>>
> 
>  Second question is that after created table, table size was 0 byte. I
>  inserted a row as 120 then table size was 8192 byte. I inserted five
> times
>  same value to the table and table size is still 8192 bytes. Table size
>  changed after inserted lots of rows. Table size was stabile till
> first few
>  hundred rows. why table size didn't change when I inserted lots of
> rows?
> 
> >>>
> >>> PostgreSQL works with 8KB blocks. When you insert a line, it puts it
> on a
> >>> block, but this block may contain many lines. So your next new lines
> still
> >>> fit in the first block... until it doesn't, and you'll see a new block
> >>> coming, making your table datafile grows to 16KB. And so on and so on.
> >>>
> >>>
> >>> --
> >>> Guillaume.
> >>>   http://blog.guillaume.lelarge.info
> >>>   http://www.dalibo.com
> >>
> >>
> >
> >
> >
> > --
> > Gunce Kaya
> >
> > Linkedin - Twitter - Blog
>
>
>
> --
> If you can't see the forest for the trees,
> Cut the trees and you'll see there is no forest.
>
>
>


-- 
Gunce Kaya

Linkedin  - Twitter
 - Blog

Re: [GENERAL] [ADMIN] calculating table and index size

[Guillaume Lelarge]

Le 7 avr. 2017 4:58 PM, "Alban Hertroys"  a écrit :

On 7 April 2017 at 09:11, Günce Kaya  wrote:
> Hi again,
>
> Sorry for delay.
>
> Guillaume, I read your answer for first question but It's not clear to me.
> The table has a column and index also use that column. so in that
example, I
> think table size and index size should be equal. Why these are not equal?

If I understand correctly, the table only has 1 (integer) column and
all those 1,400,000 rows have the same value?


That's what I also understood.




Then the table has to store each row separately and thus has to store
the same value repeatedly. It also has to store some meta-data, such
as visibility information.


The meta data is the important stuff here. You have around seven system
columns for each row, bringing the row size from a mere 4 bytes to
something a bit more than 30 bytes.


The index on the other hand (assuming a btree index) knows that there
is only a single value in the table and therefore only stores a single
value, but it has to reference each row in the table that contains
that value.


Not true for a btree index. The value is stored as many times as it appears
on the table.

True on a gin index IIRC




So the table and the index are storing different things, but the total
size of each row/index node for that single integer column is of the
same order of magnitude. That's why they are similar in size.

If you would add another integer column to your table and VACUUM FULL
the table, the table would be about double its size, but the index
would stay the same size.


The table wouldn't double in size. It would grow but not that much. Though
I agree the index would stay the same.


Regards,
Alban.


> On Wed, Apr 5, 2017 at 1:02 PM, Steven Chang 
> wrote:
>>
>> Hello,
>>
>> try pgstattuple() and pgstatindex() , I think you will figure it out.
>>
>> Steven
>>
>> 2017-04-05 16:56 GMT+08:00 Guillaume Lelarge :
>>>
>>> Hi,
>>>
>>> 2017-04-05 9:44 GMT+02:00 Günce Kaya :

 Hi all,

 I have some questions about calculating table and index size.

 I have a dummy table which has an integer column and its index. The
 table has 140 rows and all of rows are same thats value is
2000.
 Table size is 50MB and index size is 31MB. Why there is too much size
 difference between table and its index? what happen on data files when
we
 add index?

>>>
>>> You have metadata informations in the table datafiles that you don't
have
>>> on the index datafiles. For example, all the system columns for each
line.
>>>

 Second question is that after created table, table size was 0 byte. I
 inserted a row as 120 then table size was 8192 byte. I inserted five
times
 same value to the table and table size is still 8192 bytes. Table size
 changed after inserted lots of rows. Table size was stabile till first
few
 hundred rows. why table size didn't change when I inserted lots of
rows?

>>>
>>> PostgreSQL works with 8KB blocks. When you insert a line, it puts it on
a
>>> block, but this block may contain many lines. So your next new lines
still
>>> fit in the first block... until it doesn't, and you'll see a new block
>>> coming, making your table datafile grows to 16KB. And so on and so on.
>>>
>>>
>>> --
>>> Guillaume.
>>>   http://blog.guillaume.lelarge.info
>>>   http://www.dalibo.com
>>
>>
>
>
>
> --
> Gunce Kaya
>
> Linkedin - Twitter - Blog



--
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.

Re: [GENERAL] [ADMIN] calculating table and index size

[Alban Hertroys]

On 7 April 2017 at 09:11, Günce Kaya  wrote:
> Hi again,
>
> Sorry for delay.
>
> Guillaume, I read your answer for first question but It's not clear to me.
> The table has a column and index also use that column. so in that example, I
> think table size and index size should be equal. Why these are not equal?

If I understand correctly, the table only has 1 (integer) column and
all those 1,400,000 rows have the same value?

Then the table has to store each row separately and thus has to store
the same value repeatedly. It also has to store some meta-data, such
as visibility information.

The index on the other hand (assuming a btree index) knows that there
is only a single value in the table and therefore only stores a single
value, but it has to reference each row in the table that contains
that value.

So the table and the index are storing different things, but the total
size of each row/index node for that single integer column is of the
same order of magnitude. That's why they are similar in size.

If you would add another integer column to your table and VACUUM FULL
the table, the table would be about double its size, but the index
would stay the same size.

Regards,
Alban.


> On Wed, Apr 5, 2017 at 1:02 PM, Steven Chang 
> wrote:
>>
>> Hello,
>>
>> try pgstattuple() and pgstatindex() , I think you will figure it out.
>>
>> Steven
>>
>> 2017-04-05 16:56 GMT+08:00 Guillaume Lelarge :
>>>
>>> Hi,
>>>
>>> 2017-04-05 9:44 GMT+02:00 Günce Kaya :

 Hi all,

 I have some questions about calculating table and index size.

 I have a dummy table which has an integer column and its index. The
 table has 140 rows and all of rows are same thats value is 2000.
 Table size is 50MB and index size is 31MB. Why there is too much size
 difference between table and its index? what happen on data files when we
 add index?

>>>
>>> You have metadata informations in the table datafiles that you don't have
>>> on the index datafiles. For example, all the system columns for each line.
>>>

 Second question is that after created table, table size was 0 byte. I
 inserted a row as 120 then table size was 8192 byte. I inserted five times
 same value to the table and table size is still 8192 bytes. Table size
 changed after inserted lots of rows. Table size was stabile till first few
 hundred rows. why table size didn't change when I inserted lots of rows?

>>>
>>> PostgreSQL works with 8KB blocks. When you insert a line, it puts it on a
>>> block, but this block may contain many lines. So your next new lines still
>>> fit in the first block... until it doesn't, and you'll see a new block
>>> coming, making your table datafile grows to 16KB. And so on and so on.
>>>
>>>
>>> --
>>> Guillaume.
>>>   http://blog.guillaume.lelarge.info
>>>   http://www.dalibo.com
>>
>>
>
>
>
> --
> Gunce Kaya
>
> Linkedin - Twitter - Blog



-- 
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] [ADMIN] calculating table and index size

[Günce Kaya]

Hi again,

Sorry for delay.

Guillaume, I read your answer for first question but It's not clear to me.
The table has a column and index also use that column. so in that example,
I think table size and index size should be equal. Why these are not equal?

Your answer for second question is pretty clear. Thanks for your both of
answers.

Steven, thanks for your response. I got a new information thanks to you.

Regards,

Gunce Kaya


On Wed, Apr 5, 2017 at 1:02 PM, Steven Chang 
wrote:

> Hello,
>
> try pgstattuple() and pgstatindex() , I think you will figure it out.
>
> Steven
>
> 2017-04-05 16:56 GMT+08:00 Guillaume Lelarge :
>
>> Hi,
>>
>> 2017-04-05 9:44 GMT+02:00 Günce Kaya :
>>
>>> Hi all,
>>>
>>> I have some questions about calculating table and index size.
>>>
>>> I have a dummy table which has an integer column and its index. The
>>> table has 140 rows and all of rows are same thats value is 2000.
>>> Table size is 50MB and index size is 31MB. Why there is too much size
>>> difference between table and its index? what happen on data files when we
>>> add index?
>>>
>>>
>> You have metadata informations in the table datafiles that you don't have
>> on the index datafiles. For example, all the system columns for each line.
>>
>>
>>> Second question is that after created table, table size was 0 byte. I
>>> inserted a row as 120 then table size was 8192 byte. I inserted five times
>>> same value to the table and table size is still 8192 bytes. Table size
>>> changed after inserted lots of rows. Table size was stabile till first few
>>> hundred rows. why table size didn't change when I inserted lots of rows?
>>>
>>>
>> PostgreSQL works with 8KB blocks. When you insert a line, it puts it on a
>> block, but this block may contain many lines. So your next new lines still
>> fit in the first block... until it doesn't, and you'll see a new block
>> coming, making your table datafile grows to 16KB. And so on and so on.
>>
>>
>> --
>> Guillaume.
>>   http://blog.guillaume.lelarge.info
>>   http://www.dalibo.com
>>
>
>


-- 
Gunce Kaya

Linkedin  - Twitter
 - Blog

Re: [GENERAL] [ADMIN] calculating table and index size

[Steven Chang]

Hello,

try pgstattuple() and pgstatindex() , I think you will figure it out.

Steven

2017-04-05 16:56 GMT+08:00 Guillaume Lelarge :

> Hi,
>
> 2017-04-05 9:44 GMT+02:00 Günce Kaya :
>
>> Hi all,
>>
>> I have some questions about calculating table and index size.
>>
>> I have a dummy table which has an integer column and its index. The table
>> has 140 rows and all of rows are same thats value is 2000. Table
>> size is 50MB and index size is 31MB. Why there is too much size difference
>> between table and its index? what happen on data files when we add index?
>>
>>
> You have metadata informations in the table datafiles that you don't have
> on the index datafiles. For example, all the system columns for each line.
>
>
>> Second question is that after created table, table size was 0 byte. I
>> inserted a row as 120 then table size was 8192 byte. I inserted five times
>> same value to the table and table size is still 8192 bytes. Table size
>> changed after inserted lots of rows. Table size was stabile till first few
>> hundred rows. why table size didn't change when I inserted lots of rows?
>>
>>
> PostgreSQL works with 8KB blocks. When you insert a line, it puts it on a
> block, but this block may contain many lines. So your next new lines still
> fit in the first block... until it doesn't, and you'll see a new block
> coming, making your table datafile grows to 16KB. And so on and so on.
>
>
> --
> Guillaume.
>   http://blog.guillaume.lelarge.info
>   http://www.dalibo.com
>

Re: [GENERAL] [ADMIN] calculating table and index size

[Guillaume Lelarge]

Hi,

2017-04-05 9:44 GMT+02:00 Günce Kaya :

> Hi all,
>
> I have some questions about calculating table and index size.
>
> I have a dummy table which has an integer column and its index. The table
> has 140 rows and all of rows are same thats value is 2000. Table
> size is 50MB and index size is 31MB. Why there is too much size difference
> between table and its index? what happen on data files when we add index?
>
>
You have metadata informations in the table datafiles that you don't have
on the index datafiles. For example, all the system columns for each line.


> Second question is that after created table, table size was 0 byte. I
> inserted a row as 120 then table size was 8192 byte. I inserted five times
> same value to the table and table size is still 8192 bytes. Table size
> changed after inserted lots of rows. Table size was stabile till first few
> hundred rows. why table size didn't change when I inserted lots of rows?
>
>
PostgreSQL works with 8KB blocks. When you insert a line, it puts it on a
block, but this block may contain many lines. So your next new lines still
fit in the first block... until it doesn't, and you'll see a new block
coming, making your table datafile grows to 16KB. And so on and so on.


-- 
Guillaume.
  http://blog.guillaume.lelarge.info
  http://www.dalibo.com