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.

Note: These view is accessible to all users, however non-superusers will only be able to see information that they have permission to access.










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,


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,


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;

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;