From: | Mariel Cherkassky <mariel(dot)cherkassky(at)gmail(dot)com> |
---|---|
To: | "Jehan-Guillaume (ioguix) de Rorthais" <ioguix(at)free(dot)fr> |
Cc: | Tumasgiu Rossini <rossini(dot)t(at)gmail(dot)com>, pgsql-admin(at)lists(dot)postgresql(dot)org, pgsql-performance(at)lists(dot)postgresql(dot)org |
Subject: | Re: pgstattupple vs pg_total_relation_size |
Date: | 2019-01-30 14:32:01 |
Message-ID: | CA+t6e1kac6ZAfEr74LsuiBpyMAPd-a=m=HujhyCAh+kwpF79qg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin pgsql-performance |
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(at)free(dot)fr wrote:
> On Wed, 30 Jan 2019 14:19:52 +0100
> Tumasgiu Rossini <rossini(dot)t(at)gmail(dot)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(dot)cherkassky(at)gmail(dot)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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Pepe TD Vo | 2019-01-30 14:44:31 | Re: ora2pg import |
Previous Message | Jehan-Guillaume (ioguix) de Rorthais | 2019-01-30 13:27:01 | Re: pgstattupple vs pg_total_relation_size |
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2019-01-30 14:46:49 | Re: pgstattupple vs pg_total_relation_size |
Previous Message | Jehan-Guillaume (ioguix) de Rorthais | 2019-01-30 13:27:01 | Re: pgstattupple vs pg_total_relation_size |