Greenplum DBA - Useful SQL Queries

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.

2. Check the size of a table
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

7. Generate Scripts to grant DML priv to other user or role
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)

8. select count(*) from pg_tables where schemaname='sachi'; 
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;

25. Find dependent views created on any table
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;
Comments