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