From: | Mariel Cherkassky <mariel(dot)cherkassky(at)gmail(dot)com> |
---|---|
To: | pgsql-admin(at)lists(dot)postgresql(dot)org, pgsql-performance(at)lists(dot)postgresql(dot)org |
Subject: | pgstattupple vs pg_total_relation_size |
Date: | 2019-01-30 10:41:55 |
Message-ID: | CA+t6e1ka4T-oUmnq1bhBF5JY=_5mDNk3c2MLekyTM5yG5vPuxQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin pgsql-performance |
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)
From | Date | Subject | |
---|---|---|---|
Next Message | Tumasgiu Rossini | 2019-01-30 13:19:52 | Re: pgstattupple vs pg_total_relation_size |
Previous Message | Shreeyansh Dba | 2019-01-30 07:47:51 | Re: : : How to mask column: : |
From | Date | Subject | |
---|---|---|---|
Next Message | Bob Jolliffe | 2019-01-30 11:57:10 | Re: How can sort performance be so different |
Previous Message | Mariel Cherkassky | 2019-01-30 10:35:56 | Re: ERROR: found xmin from before relfrozenxid |