1. Viewing table data distribution across segment Servers To view the data distribution of a table’s rows (the number of rows on each segment), you can run a query such as: [gpadmin@sachi ~]$ SELECT gp_segment_id, count(*) FROM abc GROUP BY gp_segment_id; To see the data distribution of a table’s rows on segment servers and the directory location of where the data is located, you can run a query such as: [gpadmin@sachi ~]$SELECT table_name.gp_segment_id,hostname, fselocation as datadir,count(*) FROM table_name, pg_filespace_entry pgfse,gp_segment_configuration gsc WHERE gsc.dbid=pgfse.fsedbid and table_name.gp_segment_id= pgfse.fsedbid GROUP BY table_name.gp_segment_id,hostname,datadir ORDER BY gp_segment_id; Balanced Distribution: A table is considered to have a balanced distribution if all of the segments have roughly the same number of rows. Answer: Table Level: psql> select pg_size_pretty(pg_relation_size('schema.tablename')); Replace schema.tablename with your search table. Table and Index: psql> select pg_size_pretty(pg_total_relation_size('schema.tablename')); Replace schema.tablename with your search table. 3. Check the Schema size Answer: Schema Level: psql> select schemaname ,round(sum(pg_total_relation_size(schemaname||'.'||tablename))/1024/1024) "Size_MB" from pg_tables where schemaname='SCHEMANAME' group by 1; Replace SCHEMANAME with your schema name. 4. Check the database size Answer: To see size of specific database: psql> select pg_size_pretty(pg_database_size('DATBASE_NAME')); Example: gpdb=# select pg_size_pretty(pg_database_size('gpdb')); pg_size_pretty ---------------- 24 MB (1 row) To see all database sizes: psql> select datname,pg_size_pretty(pg_database_size(datname)) from pg_database; psql>select sodddatname, (sodddatsize/1073741824) AS sizeinGB from gp_toolkit.gp_size_of_database; 6. Check partitioned table size including indexes and partitions Answer: Table size with partitions: The following SQL gives you employee_dailly table size, which includes partitions. select schemaname,tablename,round(sum(pg_total_relation_size(schemaname || '.' || partitiontablename))/1024/1024) "MB" from pg_partitions where tablename='employee_daily' group by 1,2; schemaname | tablename | MB -----------+----------------+----- public | employee_daily | 254 sachi=> select 'grant select,insert,update, delete on '||schemaname||'.'||tablename||' to gpadmin' from pg_tables where tableowner='sachi'; ?column? ------------------------------------------------------------------------------ grant select,insert,update, delete on sachi.countries to gpadmin grant select,insert,update, delete on sachi.departments to gpadmin grant select,insert,update, delete on sachi.employees to gpadmin grant select,insert,update, delete on sachi.job_history to gpadmin grant select,insert,update, delete on sachi.jobs to gpadmin grant select,insert,update, delete on sachi.locations to gpadmin grant select,insert,update, delete on sachi.regions to gpadmin grant select,insert,update, delete on sachi.ext_countries_error to gpadmin grant select,insert,update, delete on sachi.ext_countries to gpadmin grant select,insert,update, delete on sachi.ext_departments_error to gpadmin grant select,insert,update, delete on sachi.ext_departments to gpadmin grant select,insert,update, delete on sachi.ext_employees_error to gpadmin grant select,insert,update, delete on sachi.ext_employees to gpadmin grant select,insert,update, delete on sachi.ext_job_history_error to gpadmin grant select,insert,update, delete on sachi.ext_job_history to gpadmin grant select,insert,update, delete on sachi.ext_jobs_error to gpadmin grant select,insert,update, delete on sachi.ext_jobs to gpadmin grant select,insert,update, delete on sachi.ext_locations_error to gpadmin grant select,insert,update, delete on sachi.ext_locations to gpadmin grant select,insert,update, delete on sachi.ext_regions_error to gpadmin grant select,insert,update, delete on sachi.ext_regions to gpadmin (21 rows) 9. select rolname from pg_roles where rolcanlogin is TRUE; -- List users 10. select rolname from pg_roles where rolcanlogin is FALSE; --- List Roles 11. select * from pg_views ; 12. select * from information_schema.table_privileges; 13. select * from pg_user; 14. select * from pg_views where viewname like '%priv%'; 15. select * from pg_views where viewname like '%role%'; 16. select * from pg_views where viewname like '%gran%'; 17. select * from pg_views where viewname like '%part%'; 18. select * from pg_views where viewname like '%schema%'; 19. select * from pg_views where viewname like '%stat%'; 20. select * from information_schema.role_table_grants; 21. select * from information_schema.role_routine_grants; 22. select * from information_schema.applicable_roles; 23. select * from information_schema.enabled_roles; 24. select * from gp_toolkit.gp_roles_assigned; The following query will give you the dependent views for that table: # select a.*,b.definition from information_schema.view_table_usage a, pg_views b where a.table_name='test' and a.view_name=b.viewname; When you alter an existing table (for example dropping a column), you may want to determine if there are any views that depend on the columns you are changing. Example: \dt test List of relations Schema | Name | Type | Owner | Storage --------+------+-------+------------+--------- public | test | table | gpadmin | heap create view test_view as (select * from test); ##Run the following query with "where a.table_name='<table_name>' select a.*,b.definition from information_schema.view_table_usage a, pg_views b where a.table_name='test' and a.view_name=b.viewname; view_catalog | view_schema | view_name | table_catalog | table_schema | table_name | definition -------------+------------+-----------+-----------+-------------- +------------+-------------------- ddata | public | test_view | ddata | public | test | SELECT test.a FROM test; |