From: | Steven Chang <stevenchang1213(at)gmail(dot)com> |
---|---|
To: | Guillaume Lelarge <guillaume(at)lelarge(dot)info> |
Cc: | Günce Kaya <guncekaya14(at)gmail(dot)com>, "pgsql-admin(at)postgresql(dot)org" <pgsql-admin(at)postgresql(dot)org>, PostgreSQL General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: calculating table and index size |
Date: | 2017-04-05 10:02:00 |
Message-ID: | CAEJt7k1a_FPsHmXNRkpKtD6Hs6f_xM6Dku6yiFrvL27+WORqxA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin pgsql-general |
Hello,
try pgstattuple() and pgstatindex() , I think you will figure it out.
Steven
2017-04-05 16:56 GMT+08:00 Guillaume Lelarge <guillaume(at)lelarge(dot)info>:
> Hi,
>
> 2017-04-05 9:44 GMT+02:00 Günce Kaya <guncekaya14(at)gmail(dot)com>:
>
>> 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 1400000 rows and all of rows are same thats value is 20000000. 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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Ertan Küçükoğlu | 2017-04-05 12:49:26 | Re: PostgreSQL 9.6.1 - Windows 64bit - HDD crash due to power outage |
Previous Message | Guillaume Lelarge | 2017-04-05 08:56:49 | Re: calculating table and index size |
From | Date | Subject | |
---|---|---|---|
Next Message | Albe Laurenz | 2017-04-05 10:34:22 | Re: expensive function in select list vs limit clause |
Previous Message | Александр Киселев | 2017-04-05 09:37:26 | How does hot_standby_feedback work |