pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedpgstattupple vs pg_total_relation_size
5+ messages / 4 participants
[nested] [flat]
* pgstattupple vs pg_total_relation_size
@ 2019-01-30 10:41 Mariel Cherkassky <mariel.cherkassky@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Mariel Cherkassky @ 2019-01-30 10:41 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org; pgsql-performance@lists.postgresql.org
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)
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: pgstattupple vs pg_total_relation_size
@ 2019-01-30 13:19 Tumasgiu Rossini <rossini.t@gmail.com>
parent: Mariel Cherkassky <mariel.cherkassky@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Tumasgiu Rossini @ 2019-01-30 13:19 UTC (permalink / raw)
To: Mariel Cherkassky <mariel.cherkassky@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org; pgsql-performance@lists.postgresql.org
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 ?
[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)
>
>
>
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: pgstattupple vs pg_total_relation_size
@ 2019-01-30 13:27 Jehan-Guillaume (ioguix) de Rorthais <ioguix@free.fr>
parent: Tumasgiu Rossini <rossini.t@gmail.com>
0 siblings, 2 replies; 5+ messages in thread
From: Jehan-Guillaume (ioguix) de Rorthais @ 2019-01-30 13:27 UTC (permalink / raw)
To: Tumasgiu Rossini <rossini.t@gmail.com>; +Cc: Mariel Cherkassky <mariel.cherkassky@gmail.com>; pgsql-admin@lists.postgresql.org; pgsql-performance@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: pgstattupple vs pg_total_relation_size
@ 2019-01-30 14:32 Mariel Cherkassky <mariel.cherkassky@gmail.com>
parent: Jehan-Guillaume (ioguix) de Rorthais <ioguix@free.fr>
1 sibling, 0 replies; 5+ messages in thread
From: Mariel Cherkassky @ 2019-01-30 14:32 UTC (permalink / raw)
To: Jehan-Guillaume (ioguix) de Rorthais <ioguix@free.fr>; +Cc: Tumasgiu Rossini <rossini.t@gmail.com>; pgsql-admin@lists.postgresql.org; pgsql-performance@lists.postgresql.org
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
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: pgstattupple vs pg_total_relation_size
@ 2019-01-30 14:46 Tom Lane <tgl@sss.pgh.pa.us>
parent: Jehan-Guillaume (ioguix) de Rorthais <ioguix@free.fr>
1 sibling, 0 replies; 5+ messages in thread
From: Tom Lane @ 2019-01-30 14:46 UTC (permalink / raw)
To: Jehan-Guillaume (ioguix) de Rorthais <ioguix@free.fr>; +Cc: Tumasgiu Rossini <rossini.t@gmail.com>; Mariel Cherkassky <mariel.cherkassky@gmail.com>; pgsql-admin@lists.postgresql.org; pgsql-performance@lists.postgresql.org
"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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2019-01-30 14:46 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-01-30 10:41 pgstattupple vs pg_total_relation_size Mariel Cherkassky <mariel.cherkassky@gmail.com>
2019-01-30 13:19 ` Tumasgiu Rossini <rossini.t@gmail.com>
2019-01-30 13:27 ` Jehan-Guillaume (ioguix) de Rorthais <ioguix@free.fr>
2019-01-30 14:32 ` Mariel Cherkassky <mariel.cherkassky@gmail.com>
2019-01-30 14:46 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox