pg.ddx.io pgsql-performance@postgresql.org mailing list archive help / color / mirror / Atom feed From: Jehan-Guillaume (ioguix) de Rorthais <ioguix@free.fr>
To: Tumasgiu Rossini <rossini.t@gmail.com>
Cc: Mariel Cherkassky <mariel.cherkassky@gmail.com>
Cc: pgsql-admin@lists.postgresql.org
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: pgstattupple vs pg_total_relation_size
Date: Wed, 30 Jan 2019 14:27:01 +0100
Message-ID: <20190130142701.38992d60@firost> (raw )
In-Reply-To: <CAJD9AWwjXPwMThSJ6EcwCDSfsq4c9nj-Ypec7t0+xFeMVJ_Wsw@mail.gmail.com >
References: <CA+t6e1ka4T-oUmnq1bhBF5JY=_5mDNk3c2MLekyTM5yG5vPuxQ@mail.gmail.com >
<CAJD9AWwjXPwMThSJ6EcwCDSfsq4c9nj-Ypec7t0+xFeMVJ_Wsw@mail.gmail.com >
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
view thread (5+ messages) latest in thread
Message-ID: <20190130142701.38992d60@firost>
Permalink: ../20190130142701.38992d60@firost/
Also on: postgresql.org/message-id/20190130142701.38992d60@firost
copy link · copy postgr.es
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: ioguix@free.fr, rossini.t@gmail.com, mariel.cherkassky@gmail.com, pgsql-admin@lists.postgresql.org, pgsql-performance@lists.postgresql.org
Subject: Re: pgstattupple vs pg_total_relation_size
In-Reply-To: <20190130142701.38992d60@firost>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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