We have migrated to new Google sites!
This is the most asked information by executives and managers. You must have access to objects in order to get size details. if you are a developer and you do not have access to all the tables in a particular schema and if you run select on gp_toolkit.gp_size_of_schema_disk you will get an error for those objects in schema that you do not have access. The work around is you can ask your DBA to create a function from gpadmin user and grant execute access to you. You can find function code in your greenplumdba.com web portal.
There are combined 9 views related to size of the database, schema, table and indexes.
Note: These view is accessible to all users, however non-superusers will only be able to see information that they have permission to access.
gp_size_of_all_table_indexes
gp_size_of_database
gp_size_of_index
gp_size_of_partition_and_indexes_disk
gp_size_of_schema_disk
gp_size_of_table_and_indexes_disk
gp_size_of_table_and_indexes_licensing
gp_size_of_table_disk
gp_size_of_table_uncompressed
gp_toolkit.gp_size_of_all_table_indexes - Displays total size of all indexes of a table
gp_toolkit.gp_size_of_database - Displays total size of a database
gp_toolkit.gp_size_of_index - Displays total size of an index
gp_toolkit.gp_size_of_partition_and_indexes_disk - Displays the size on disk of partitioned child tables and their indexes.
gp_toolkit.gp_size_of_schema_disk - Displays the size of an schema
gp_toolkit.gp_size_of_table_and_indexes_disk - Displays size of a table and all of its indexes.
gp_toolkit.gp_size_of_table_and_indexes_licensing: Displays the total size of tables and their indexes for licensing purposes. The use of this view requires superuser permissions.
gp_toolkit.gp_size_of_table_disk: Displays size of a table on disk.
gp_toolkit.gp_size_of_table_uncompressed - Displays the uncompressed table size for append-optimized (AO) tables. Otherwise, the table size on disk is shown. The use of this view requires superuser permissions.
sachi=# \dv gp_toolkit.gp_size*
List of relations
Schema | Name | Type | Owner | Storage
------------+----------------------------------------+------+---------+---------
gp_toolkit | gp_size_of_all_table_indexes | view | gpadmin | none
gp_toolkit | gp_size_of_database | view | gpadmin | none
gp_toolkit | gp_size_of_index | view | gpadmin | none
gp_toolkit | gp_size_of_partition_and_indexes_disk | view | gpadmin | none
gp_toolkit | gp_size_of_schema_disk | view | gpadmin | none
gp_toolkit | gp_size_of_table_and_indexes_disk | view | gpadmin | none
gp_toolkit | gp_size_of_table_and_indexes_licensing | view | gpadmin | none
gp_toolkit | gp_size_of_table_disk | view | gpadmin | none
gp_toolkit | gp_size_of_table_uncompressed | view | gpadmin | none
(9 rows)
sachi=# \d gp_toolkit.gp_size_of_all_table_indexes
View "gp_toolkit.gp_size_of_all_table_indexes"
Column | Type | Modifiers
-----------------+---------+-----------
soatioid | oid |
soatisize | numeric |
soatischemaname | name |
soatitablename | name |
View definition:
SELECT soati.soatioid, soati.soatisize, fn.fnnspname AS soatischemaname, fn.fnrelname AS soatitablename
FROM ( SELECT ti.tireloid AS soatioid, sum(pg_relation_size(ti.tiidxoid)) AS soatisize
FROM gp_toolkit.gp_table_indexes ti
GROUP BY ti.tireloid) soati
JOIN gp_toolkit.__gp_fullname fn ON soati.soatioid = fn.fnoid;
sachi=# \d gp_toolkit.gp_size_of_database
View "gp_toolkit.gp_size_of_database"
Column | Type | Modifiers
-------------+--------+-----------
sodddatname | name |
sodddatsize | bigint |
View definition:
SELECT pg_database.datname AS sodddatname, pg_database_size(pg_database.oid) AS sodddatsize
FROM pg_database
WHERE pg_database.datname <> 'template0'::name AND pg_database.datname <> 'template1'::name AND pg_database.datname <> 'postgres'::name;
sachi=# \d gp_toolkit.gp_size_of_index
View "gp_toolkit.gp_size_of_index"
Column | Type | Modifiers
--------------------+--------+-----------
soioid | oid |
soitableoid | oid |
soisize | bigint |
soiindexschemaname | name |
soiindexname | name |
soitableschemaname | name |
soitablename | name |
View definition:
SELECT soi.soioid, soi.soitableoid, soi.soisize, fnidx.fnnspname AS soiindexschemaname, fnidx.fnrelname AS soiindexname, fntbl.fnnspname AS soitableschemaname, fntbl.fnrelname AS soitablename
FROM ( SELECT pgi.indexrelid AS soioid, pgi.indrelid AS soitableoid, pg_relation_size(pgi.indexrelid) AS soisize
FROM pg_index pgi
JOIN gp_toolkit.__gp_user_data_tables_readable ut ON pgi.indrelid = ut.autoid) soi
JOIN gp_toolkit.__gp_fullname fnidx ON soi.soioid = fnidx.fnoid
JOIN gp_toolkit.__gp_fullname fntbl ON soi.soitableoid = fntbl.fnoid;
sachi=# \d gp_toolki.gp_size_of_partition_and_indexes_disk
Did not find any relation named "gp_toolki.gp_size_of_partition_and_indexes_disk".
sachi=# \d gp_toolkit.gp_size_of_partition_and_indexes_disk
View "gp_toolkit.gp_size_of_partition_and_indexes_disk"
Column | Type | Modifiers
----------------------------+---------+-----------
sopaidparentoid | oid |
sopaidpartitionoid | oid |
sopaidpartitiontablesize | bigint |
sopaidpartitionindexessize | numeric |
sopaidparentschemaname | name |
sopaidparenttablename | name |
sopaidpartitionschemaname | name |
sopaidpartitiontablename | name |
View definition:
SELECT sopaid.sopaidparentoid, sopaid.sopaidpartitionoid, sopaid.sopaidpartitiontablesize, sopaid.sopaidpartitionindexessize, fnparent.fnnspname AS sopaidparentschemaname, fnparent.fnrelname AS sopaidparenttablename, fnpart.fnnspname AS sopaidpartitionschemaname, fnpart.fnrelname AS sopaidpartitiontablename
FROM ( SELECT pgp.parrelid AS sopaidparentoid, pgpr.parchildrelid AS sopaidpartitionoid, sotd.sotdsize + sotd.sotdtoastsize + sotd.sotdadditionalsize AS sopaidpartitiontablesize, COALESCE(soati.soatisize, 0::numeric) AS sopaidpartitionindexessize
FROM pg_partition pgp
JOIN pg_partition_rule pgpr ON pgp.oid = pgpr.paroid
JOIN gp_toolkit.gp_size_of_table_disk sotd ON sotd.sotdoid = pgpr.parchildrelid
LEFT JOIN gp_toolkit.gp_size_of_all_table_indexes soati ON soati.soatioid = pgpr.parchildrelid) sopaid
JOIN gp_toolkit.__gp_fullname fnparent ON sopaid.sopaidparentoid = fnparent.fnoid
JOIN gp_toolkit.__gp_fullname fnpart ON sopaid.sopaidpartitionoid = fnpart.fnoid;
sachi=# \d gp_toolkit.gp_size_of_schema_disk
View "gp_toolkit.gp_size_of_schema_disk"
Column | Type | Modifiers
---------------------+---------+-----------
sosdnsp | name |
sosdschematablesize | numeric |
sosdschemaidxsize | numeric |
View definition:
SELECT un.aunnspname AS sosdnsp, COALESCE(sum(sotaid.sotaidtablesize), 0::numeric) AS sosdschematablesize, COALESCE(sum(sotaid.sotaididxsize), 0::numeric) AS sosdschemaidxsize
FROM gp_toolkit.gp_size_of_table_and_indexes_disk sotaid
JOIN gp_toolkit.__gp_fullname fn ON sotaid.sotaidoid = fn.fnoid
RIGHT JOIN gp_toolkit.__gp_user_namespaces un ON un.aunnspname = fn.fnnspname
GROUP BY un.aunnspname;
sachi=# \d gp_toolkit.gp_size_of_table_and_indexes_disk
View "gp_toolkit.gp_size_of_table_and_indexes_disk"
Column | Type | Modifiers
------------------+---------+-----------
sotaidoid | oid |
sotaidtablesize | bigint |
sotaididxsize | numeric |
sotaidschemaname | name |
sotaidtablename | name |
View definition:
SELECT sotaid.sotaidoid, sotaid.sotaidtablesize, sotaid.sotaididxsize, fn.fnnspname AS sotaidschemaname, fn.fnrelname AS sotaidtablename
FROM ( SELECT sotd.sotdoid AS sotaidoid, sotd.sotdsize + sotd.sotdtoastsize + sotd.sotdadditionalsize AS sotaidtablesize,
CASE
WHEN soati.soatisize IS NULL THEN 0::numeric
ELSE soati.soatisize
END AS sotaididxsize
FROM gp_toolkit.gp_size_of_table_disk sotd
LEFT JOIN gp_toolkit.gp_size_of_all_table_indexes soati ON sotd.sotdoid = soati.soatioid) sotaid
JOIN gp_toolkit.__gp_fullname fn ON sotaid.sotaidoid = fn.fnoid;
sachi=# \d gp_toolkit.gp_size_of_table_and_indexes_licensing
View "gp_toolkit.gp_size_of_table_and_indexes_licensing"
Column | Type | Modifiers
-----------------------------+------------------+-----------
sotailoid | oid |
sotailtablesizedisk | bigint |
sotailtablesizeuncompressed | double precision |
sotailindexessize | numeric |
sotailschemaname | name |
sotailtablename | name |
View definition:
SELECT sotail.sotailoid, sotail.sotailtablesizedisk, sotail.sotailtablesizeuncompressed, sotail.sotailindexessize, fn.fnnspname AS sotailschemaname, fn.fnrelname AS sotailtablename
FROM ( SELECT sotu.sotuoid AS sotailoid, sotaid.sotaidtablesize AS sotailtablesizedisk, sotu.sotusize AS sotailtablesizeuncompressed, sotaid.sotaididxsize AS sotailindexessize
FROM gp_toolkit.gp_size_of_table_uncompressed sotu
JOIN gp_toolkit.gp_size_of_table_and_indexes_disk sotaid ON sotu.sotuoid = sotaid.sotaidoid) sotail
JOIN gp_toolkit.__gp_fullname fn ON sotail.sotailoid = fn.fnoid;
sachi=# gp_toolkit.gp_size_of_table_disk
sachi-# \d gp_toolkit.gp_size_of_table_disk
View "gp_toolkit.gp_size_of_table_disk"
Column | Type | Modifiers
--------------------+--------+-----------
sotdoid | oid |
sotdsize | bigint |
sotdtoastsize | bigint |
sotdadditionalsize | bigint |
sotdschemaname | name |
sotdtablename | name |
View definition:
SELECT sotd.sotdoid, sotd.sotdsize, sotd.sotdtoastsize, sotd.sotdadditionalsize, fn.fnnspname AS sotdschemaname, fn.fnrelname AS sotdtablename
FROM ( SELECT udtr.autoid AS sotdoid, pg_relation_size(udtr.autoid) AS sotdsize,
CASE
WHEN udtr.auttoastoid > 0::oid THEN pg_total_relation_size(udtr.auttoastoid)
ELSE 0::bigint
END AS sotdtoastsize,
CASE
WHEN ao.segrelid IS NOT NULL AND ao.segrelid > 0::oid THEN pg_total_relation_size(ao.segrelid)
ELSE 0::bigint
END +
CASE
WHEN ao.blkdirrelid IS NOT NULL AND ao.blkdirrelid > 0::oid THEN pg_total_relation_size(ao.blkdirrelid)
ELSE 0::bigint
END +
CASE
WHEN ao.visimaprelid IS NOT NULL AND ao.visimaprelid > 0::oid THEN pg_total_relation_size(ao.visimaprelid)
ELSE 0::bigint
END AS sotdadditionalsize
FROM ( SELECT __gp_user_data_tables_readable.autnspname, __gp_user_data_tables_readable.autrelname, __gp_user_data_tables_readable.autrelkind, __gp_user_data_tables_readable.autreltuples, __gp_user_data_tables_readable.autrelpages, __gp_user_data_tables_readable.autrelacl, __gp_user_data_tables_readable.autoid, __gp_user_data_tables_readable.auttoastoid, __gp_user_data_tables_readable.autrelstorage
FROM gp_toolkit.__gp_user_data_tables_readable
WHERE __gp_user_data_tables_readable.autrelstorage <> 'x'::"char") udtr
LEFT JOIN pg_appendonly ao ON udtr.autoid = ao.relid) sotd
JOIN gp_toolkit.__gp_fullname fn ON sotd.sotdoid = fn.fnoid;
sachi-# \d gp_toolkit.gp_size_of_table_uncompressed
View "gp_toolkit.gp_size_of_table_uncompressed"
Column | Type | Modifiers
----------------+------------------+-----------
sotuoid | oid |
sotusize | double precision |
sotuschemaname | name |
sotutablename | name |
View definition:
SELECT sotu.sotuoid, sotu.sotusize, fn.fnnspname AS sotuschemaname, fn.fnrelname AS sotutablename
FROM ( SELECT sotd.sotdoid AS sotuoid,
CASE
WHEN iao.iaotype THEN
CASE
WHEN pg_relation_size(sotd.sotdoid) = 0 THEN 0::double precision
ELSE pg_relation_size(sotd.sotdoid)::double precision *
CASE
WHEN get_ao_compression_ratio(sotd.sotdoid) = (-1)::double precision THEN NULL::double precision
ELSE get_ao_compression_ratio(sotd.sotdoid)
END
END
ELSE sotd.sotdsize::double precision
END + sotd.sotdtoastsize::double precision + sotd.sotdadditionalsize::double precision AS sotusize
FROM gp_toolkit.gp_size_of_table_disk sotd
JOIN gp_toolkit.__gp_is_append_only iao ON sotd.sotdoid = iao.iaooid) sotu
JOIN gp_toolkit.__gp_fullname fn ON sotu.sotuoid = fn.fnoid;
sachi-#