Обсуждение: calculating table and index size
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?
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?
Is there anyone can explain two questions?
Best regards
Hi,
--
2017-04-05 9:44 GMT+02:00 Günce Kaya <guncekaya14@gmail.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.
--
Hello,
try pgstattuple() and pgstatindex() , I think you will figure it out.
Steven
2017-04-05 16:56 GMT+08:00 Guillaume Lelarge <guillaume@lelarge.info>:
Hi,2017-04-05 9:44 GMT+02:00 Günce Kaya <guncekaya14@gmail.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.
--