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.

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

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-#