Understanding gp_toolkit schema gp_size views and Checking database object sizes and disk space

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.
The gadget spec URL could not be found
Note: These view is accessible to all users, however non-superusers will only be able to see information that they have permission to access.
The gadget spec URL could not be found
  •  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.
The gadget spec URL could not be found
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)
The gadget spec URL could not be found
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;

The gadget spec URL could not be found
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;

The gadget spec URL could not be found
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;
The gadget spec URL could not be found

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;

The gadget spec URL could not be found
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;

The gadget spec URL could not be found
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,
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;

The gadget spec URL could not be found
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;

The gadget spec URL could not be found
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,
WHEN udtr.auttoastoid > 0::oid THEN pg_total_relation_size(udtr.auttoastoid)
ELSE 0::bigint
END AS sotdtoastsize,
WHEN ao.segrelid IS NOT NULL AND ao.segrelid > 0::oid THEN pg_total_relation_size(ao.segrelid)
ELSE 0::bigint
WHEN ao.blkdirrelid IS NOT NULL AND ao.blkdirrelid > 0::oid THEN pg_total_relation_size(ao.blkdirrelid)
ELSE 0::bigint
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;

The gadget spec URL could not be found
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,
WHEN iao.iaotype THEN
WHEN pg_relation_size(sotd.sotdoid) = 0 THEN 0::double precision
ELSE pg_relation_size(sotd.sotdoid)::double precision *
WHEN get_ao_compression_ratio(sotd.sotdoid) = (-1)::double precision THEN NULL::double precision
ELSE get_ao_compression_ratio(sotd.sotdoid)
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;

The gadget spec URL could not be found









The gadget spec URL could not be found

The gadget spec URL could not be found

The gadget spec URL could not be found

The gadget spec URL could not be found

The gadget spec URL could not be found

The gadget spec URL could not be found

The gadget spec URL could not be found