From: | Tumasgiu Rossini <rossini(dot)t(at)gmail(dot)com> |
---|---|
To: | Mariel Cherkassky <mariel(dot)cherkassky(at)gmail(dot)com> |
Cc: | 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 13:19:52 |
Message-ID: | CAJD9AWwjXPwMThSJ6EcwCDSfsq4c9nj-Ypec7t0+xFeMVJ_Wsw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin pgsql-performance |
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(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)
>
>
>
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Jehan-Guillaume (ioguix) de Rorthais | 2019-01-30 13:27:01 | Re: pgstattupple vs pg_total_relation_size |
Previous Message | Mariel Cherkassky | 2019-01-30 10:41:55 | pgstattupple vs pg_total_relation_size |
From | Date | Subject | |
---|---|---|---|
Next Message | Jehan-Guillaume (ioguix) de Rorthais | 2019-01-30 13:27:01 | Re: pgstattupple vs pg_total_relation_size |
Previous Message | Bob Jolliffe | 2019-01-30 11:57:10 | Re: How can sort performance be so different |