Re: pgstattupple vs pg_total_relation_size
"Jehan-Guillaume (ioguix) de Rorthais" writes: > On Wed, 30 Jan 2019 14:19:52 +0100 > Tumasgiu Rossini 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
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 < iog...@free.fr wrote: > On Wed, 30 Jan 2019 14:19:52 +0100 > Tumasgiu Rossini 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.cherkas...@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
On Wed, 30 Jan 2019 14:19:52 +0100 Tumasgiu Rossini 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.cherkas...@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
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.cherkas...@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) > > > > >