The following views calculate the total size of an object across all primary segments (mirrors are not included in the size calculations). 1. gp_size_of_all_table_indexes 2. gp_size_of_database 3. gp_size_of_index 4. gp_size_of_partition_and_indexes_disk 5. gp_size_of_schema_disk 6. gp_size_of_table_and_indexes_disk 7. gp_size_of_table_and_indexes_licensing 8. gp_size_of_table_disk 9. gp_size_of_table_uncompressed 10. gp_disk_free 1. 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; This view shows the total size of all indexes for a table. This view is accessible to all users, however non-superusers will only be able to see relations that they have permission to access. soatioid=>The object ID of the table soatisize=>The total size of all table indexes in bytes soatischemaname=>The schema name soatitablename=>The table name 2. 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; This view shows the total size of a database. This view is accessible to all users, however non-superusers will only be able to see databases that they have permission to access. sodddatname=>The name of the database sodddatsize=>The size of the database in bytes 3. gp_size_of_index gpadmin=# \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; This view shows the total size of an index. This view is accessible to all users, however non-superusers will only be able to see relations that they have permission to access. soioid=>The object ID of the index soitableoid=>The object ID of the table to which the index belongs soisize=>The size of the index in bytes soiindexschemaname=>The name of the index schema soiindexname=>The name of the index soitableschemaname=>The name of the table schema soitablename=>The name of the table 4. gp_size_of_partition_and_indexes_disk gpadmin=# \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; This view shows the size on disk of partitioned child tables and their indexes. This view is accessible to all users, however non-superusers will only be able to see relations that they have permission to access. sopaidparentoid=>The object ID of the parent table sopaidpartitionoid=>The object ID of the partition table sopaidpartitiontablesize=>The partition table size in bytes sopaidpartitionindexessize=>The total size of all indexes on this partition Sopaidparentschemaname=>The name of the parent schema Sopaidparenttablename=>The name of the parent table Sopaidpartitionschemaname=>The name of the partition schema sopaidpartitiontablename=>The name of the partition table 5. gp_size_of_schema_disk gpadmin=# \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; This view shows schema sizes for the public schema and the user-created schemas in the current database. This view is accessible to all users, however non-superusers will be able to see only the schemas that they have permission to access. sosdschematablesize=>The total size of tables in the schema in bytes sosdschemaidxsize=>The total size of indexes in the schema in bytes 6. gp_size_of_table_and_indexes_disk gpadmin=# \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; This view shows the size on disk of tables and their indexes. This view is accessible to all users, however non-superusers will only be able to see relations that they have permission to access. sotaidoid=>The object ID of the parent table sotaidtablesize=>The disk size of the table sotaididxsize=>The total size of all indexes on the table sotaidschemaname=>The name of the schema sotaidtablename=>The name of the table 7. gp_size_of_table_and_indexes_licensing gpadmin=# \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; This view shows the total size of tables and their indexes for licensing purposes. The use of this view requires superuser permissions. sotailoid=>The object ID of the table sotailtablesizedisk=>The total disk size of the table sotailtablesizeuncompressed=>If the table is a compressed append-optimized table, shows the uncompressed table size in bytes. sotailindexessize=>The total size of all indexes in the table sotailschemaname=>The schema name sotailtablename=>The table name 8. 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 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; This view shows the size of a table on disk. This view is accessible to all users, however non-superusers will only be able to see tables that they have permission to access. sotdoid=>The object ID of the table sotdsize=>The total size of the table in bytes (main relation, plus oversized (toast) attributes, plus additional storage objects for AO tables). sotdtoastsize=>The size of the TOAST table (oversized attribute storage), if there is one. sotdadditionalsize=>Reflects the segment and block directory table sizes for append-optimized (AO) tables. sotdschemaname=>The schema name sotdtablename=>The table name 9. 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; This view shows 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. sotuoid=>The object ID of the table sotusize=>The uncomressed size of the table in bytes if it is a compressed AO table. Otherwise, the table size on disk. sotuschemaname=>The schema name sotutablename=>The table name External table "gp_toolkit.gp_disk_free" Column | Type | Modifiers ------------+---------+----------- dfsegment | integer | dfhostname | text | dfdevice | text | dfspace | bigint | Type: readable Encoding: UTF8 Format type: csv Format options: delimiter ',' null '' escape '"' quote '"' Command: python -c "from gppylib.commands import unix; df=unix.DiskFree.get_disk_free_info_local('token','$GP_SEG_DATADIR'); print '%s, %s, %s, %s' % ('$GP_SEGMENT_ID', unix.getLocalHostname(), df[0], df[3])" Execute on: all segments This external table runs the df (disk free) command on the active segment hosts and reports back the results. Inactive mirrors are not included in the calculation. The use of this external table requires superuser permissions. dfsegment=>The content id of the segment (only active segments are shown) dfhostname=>The hostname of the segment host dfdevice=>The device name dfspace=>Free disk space in the segment file system in kilobytes |
|
|