Re: pgstattupple vs pg_total_relation_size

Поиск
Список
Период
Сортировка
От Tumasgiu Rossini
Тема Re: pgstattupple vs pg_total_relation_size
Дата
Msg-id CAJD9AWwjXPwMThSJ6EcwCDSfsq4c9nj-Ypec7t0+xFeMVJ_Wsw@mail.gmail.com
обсуждение исходный текст
Ответ на pgstattupple vs pg_total_relation_size  (Mariel Cherkassky <mariel.cherkassky@gmail.com>)
Ответы Re: pgstattupple vs pg_total_relation_size  ("Jehan-Guillaume (ioguix) de Rorthais" <ioguix@free.fr>)
Re: pgstattupple vs pg_total_relation_size  ("Jehan-Guillaume (ioguix) de Rorthais" <ioguix@free.fr>)
Список pgsql-admin
According to the doc [1],
pg_total_relation_size add toasted data *and* indexes to the mix.
Any index, unique constraint, or primary key on your table ?


Le mer. 30 janv. 2019 à 11:42, Mariel Cherkassky <mariel.cherkassky@gmail.com> a écrit :
Hey,
I'm using postgresql 9.6.11. I wanted to ask something about the functions I mentioned in the title : 
I created the next table : 
postgres=# \d students;
      Table "public. students  "
  Column  |  Type   | Modifiers
----------+---------+-----------
 id| integer |
 name| text    |
 age| integer |
 data     | jsonb   |

I inserted one row. When I query the table`s size with pg_total_relation_size I see that the data occupies 2 pages : 

postgres=# select pg_total_relation_size(' students  ');
 pg_total_relation_size
------------------------
                  16384
(1 row)

postgres=# select pg_relation_size(' students  ');
 pg_relation_size
------------------
             8192
(1 row)

When I used pgstattuple : 
postgres=# select * from pgstattuple('pg_toast.pg_toast_1187222');
 table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent
-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------
         0 |           0 |         0 |             0 |                0 |              0 |                  0 |          0 |            0
(1 row)

postgres=# select * from pgstattuple('students');
 table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent
-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------
      8192 |           1 |      1221 |          14.9 |                0 |              0 |                  0 |       6936 |        84.67
(1 row)

Which means, the toasted table is empty and you can see that the row I inserted should occupy only one page(8K in my system).

Then, why the pg_total_relation_size shows another page ?(16KB in total)




В списке pgsql-admin по дате отправления:

Предыдущее
От: Mariel Cherkassky
Дата:
Сообщение: pgstattupple vs pg_total_relation_size
Следующее
От: "Jehan-Guillaume (ioguix) de Rorthais"
Дата:
Сообщение: Re: pgstattupple vs pg_total_relation_size