Thread: pgstattupple vs pg_total_relation_size

pgstattupple vs pg_total_relation_size

From
Mariel Cherkassky
Date:
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)




Re: pgstattupple vs pg_total_relation_size

From
Tumasgiu Rossini
Date:
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)




Re: pgstattupple vs pg_total_relation_size

From
Tumasgiu Rossini
Date:
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)




Re: pgstattupple vs pg_total_relation_size

From
"Jehan-Guillaume (ioguix) de Rorthais"
Date:
On Wed, 30 Jan 2019 14:19:52 +0100
Tumasgiu Rossini <rossini.t@gmail.com> wrote:

> According to the doc [1],
> pg_total_relation_size add toasted data *and* indexes to the mix.

*and* FSM *and* VM.

> Any index, unique constraint, or primary key on your table ?
>
> [1]
> https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADMIN-DBSIZE
>
> 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)
> >
> >
> >
> >
> >



--
Jehan-Guillaume de Rorthais
Dalibo


Re: pgstattupple vs pg_total_relation_size

From
"Jehan-Guillaume (ioguix) de Rorthais"
Date:
On Wed, 30 Jan 2019 14:19:52 +0100
Tumasgiu Rossini <rossini.t@gmail.com> wrote:

> According to the doc [1],
> pg_total_relation_size add toasted data *and* indexes to the mix.

*and* FSM *and* VM.

> Any index, unique constraint, or primary key on your table ?
>
> [1]
> https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADMIN-DBSIZE
>
> 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)
> >
> >
> >
> >
> >



--
Jehan-Guillaume de Rorthais
Dalibo


Re: pgstattupple vs pg_total_relation_size

From
Mariel Cherkassky
Date:
There aren't any constraint or indexes, just a regular table.  I didn't see the fsm and vm files in the base dir. Were they created immediately for every table or after some updates/deletes ?

On Wed, Jan 30, 2019, 3:27 PM Jehan-Guillaume (ioguix) de Rorthais <ioguix@free.fr wrote:
On Wed, 30 Jan 2019 14:19:52 +0100
Tumasgiu Rossini <rossini.t@gmail.com> wrote:

> According to the doc [1],
> pg_total_relation_size add toasted data *and* indexes to the mix.

*and* FSM *and* VM.

> Any index, unique constraint, or primary key on your table ?
>
> [1]
> https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADMIN-DBSIZE
>
> 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)
> >
> >
> >
> >
> > 



--
Jehan-Guillaume de Rorthais
Dalibo

Re: pgstattupple vs pg_total_relation_size

From
Mariel Cherkassky
Date:
There aren't any constraint or indexes, just a regular table.  I didn't see the fsm and vm files in the base dir. Were they created immediately for every table or after some updates/deletes ?

On Wed, Jan 30, 2019, 3:27 PM Jehan-Guillaume (ioguix) de Rorthais <ioguix@free.fr wrote:
On Wed, 30 Jan 2019 14:19:52 +0100
Tumasgiu Rossini <rossini.t@gmail.com> wrote:

> According to the doc [1],
> pg_total_relation_size add toasted data *and* indexes to the mix.

*and* FSM *and* VM.

> Any index, unique constraint, or primary key on your table ?
>
> [1]
> https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADMIN-DBSIZE
>
> 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)
> >
> >
> >
> >
> > 



--
Jehan-Guillaume de Rorthais
Dalibo

Re: pgstattupple vs pg_total_relation_size

From
Tom Lane
Date:
"Jehan-Guillaume (ioguix) de Rorthais" <ioguix@free.fr> writes:
> On Wed, 30 Jan 2019 14:19:52 +0100
> Tumasgiu Rossini <rossini.t@gmail.com> wrote:
>> According to the doc [1],
>> pg_total_relation_size add toasted data *and* indexes to the mix.

> *and* FSM *and* VM.

Yeah.  In this particular case, the other page presumably belongs to the
toast table's index, which will have a metapage even if the table is
empty.

            regards, tom lane


Re: pgstattupple vs pg_total_relation_size

From
Tom Lane
Date:
"Jehan-Guillaume (ioguix) de Rorthais" <ioguix@free.fr> writes:
> On Wed, 30 Jan 2019 14:19:52 +0100
> Tumasgiu Rossini <rossini.t@gmail.com> wrote:
>> According to the doc [1],
>> pg_total_relation_size add toasted data *and* indexes to the mix.

> *and* FSM *and* VM.

Yeah.  In this particular case, the other page presumably belongs to the
toast table's index, which will have a metapage even if the table is
empty.

            regards, tom lane