BUG #13830: pg_table_size and pg_indexes_size

On Wed, Dec 23, 2015 at 7:55 AM, <foggyglass@163.com> wrote:

The following bug has been logged on the website:

Bug reference: 13830
Logged by: T_T
Email address: foggyglass@163.com
PostgreSQL version: 9.5rc1
Operating system: centos7
Description:

Hi friend,
I think there is a error about pg_table_size and
pg_indexes_size in
PostgreSQL .
This is my operation log

[postgres@c7 bin]$./psql
psql (9.5beta2)
Type "help" for help.

postgres=# create table b(id int);
CREATE TABLE
postgres=# create index b_idx on b(id);
CREATE INDEX
postgres=# select relname , pg_table_size(oid),pg_indexes_size(oid)
postgres-# from pg_class where relname in ('b','b_idx');
relname | pg_table_size | pg_indexes_size
---------+---------------+-----------------
b | 0 | 8192
b_idx | 8192 | 0
(2 rows)

postgres=#

The b_idex is a index , but pg_indexes_size does not show its size . The b
is a table , but pg_table_size does not show its size .I think the
pg_table_size show the b size , but not pg_indexes_size .

​Working as documented.​

Table "b" has no size of its own - however its one index has a size of 8192.

An index is sometimes considered a "table", in this case when using
pg_table_size​, and so

​you get 8192 as the size of the index table (i.e., relation). Since an
index doesn't have an index of its own pg_indexes_size returns 0.

pg_table_size on "b" returns zero since its size is zero and the
calculation excludes indexes. pg_indexes_size on "b" returns the size of
"b_idx" which is 8192.

http://www.postgresql.org/docs/9.5/interactive/functions-admin.html

​David J.​
​

foggyglass@163.com writes:

postgres=# create table b(id int);
CREATE TABLE
postgres=# create index b_idx on b(id);
CREATE INDEX
postgres=# select relname , pg_table_size(oid),pg_indexes_size(oid)
postgres-# from pg_class where relname in ('b','b_idx');
relname | pg_table_size | pg_indexes_size
---------+---------------+-----------------
b | 0 | 8192
b_idx | 8192 | 0
(2 rows)

Seems fine to me. b is empty at this point, so it has size zero.
btree indexes, on the other hand, never have size zero because their
metapage is created immediately. b_idx has no indexes attached to it,
so pg_indexes_size finds nothing to report on and returns zero for
that.

If you insert any actual data, the results change:

regression=# insert into b values(33);
INSERT 0 1
regression=# select relname , pg_table_size(oid),pg_indexes_size(oid) from pg_class where relname in ('b','b_idx');
relname | pg_table_size | pg_indexes_size
---------+---------------+-----------------
b | 8192 | 16384
b_idx | 16384 | 0
(2 rows)

which maybe looks a bit saner.

regards, tom lane

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Mr. David ,
thank you for your explanation .

T.T
在 2015/12/24 2:18, David G. Johnston 写道:

Mr. Tom Lane
Thank you for your explanation .:)

在 2015/12/24 2:22, Tom Lane 写道:

foggyglass@163.com writes:

postgres=# create table b(id int);
CREATE TABLE
postgres=# create index b_idx on b(id);
CREATE INDEX
postgres=# select relname , pg_table_size(oid),pg_indexes_size(oid)
postgres-# from pg_class where relname in ('b','b_idx');
relname | pg_table_size | pg_indexes_size
---------+---------------+-----------------
b | 0 | 8192
b_idx | 8192 | 0
(2 rows)

Seems fine to me. b is empty at this point, so it has size zero.
btree indexes, on the other hand, never have size zero because their
metapage is created immediately. b_idx has no indexes attached to it,
so pg_indexes_size finds nothing to report on and returns zero for
that.

If you insert any actual data, the results change:

regression=# insert into b values(33);
INSERT 0 1
regression=# select relname , pg_table_size(oid),pg_indexes_size(oid) from pg_class where relname in ('b','b_idx');
relname | pg_table_size | pg_indexes_size
---------+---------------+-----------------
b | 8192 | 16384
b_idx | 16384 | 0
(2 rows)

which maybe looks a bit saner.

regards, tom lane

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs