Sachi's SQL collections for Greenplum database DBA's

posted Apr 28, 2017, 5:26 PM by Sachchida Ojha

Note: pg_stat_activity, pg_locks, pg_class, pg_namespace tables/views are most often used tables and views to monitor greenplum database.

SQL to list all parameter settings in Greenplum

-- parameter settings
select category,name,setting,unit,context,source,min_val,max_val,short_desc,extra_desc from pg_settings order by category;

DCA Information SQL

--SELECT 'You are now connected to database '|| current_database()||' as user '||current_user as Connection_details;
-- 1. Check total no of segments in the DCA
Select count(*) as total_segments_in_the_database from gp_segment_configuration;

-- 2. check_GP_server_segment status
select dbid, content, role, preferred_role, mode, status, port,hostname, address from gp_segment_configuration where role = 'p' order by dbid;

--3. check storage utilization per segment server
\c gpperfmon
select ctime,hostname,filesystem,pg_size_pretty(total_bytes) as total_bytes,pg_size_pretty(bytes_used) as used_bytes, pg_size_pretty(bytes_available) as avail_bytes, (round((bytes_used * 100)/total_bytes::numeric,2))||'%' as pct_used from diskspace_now order by (round((bytes_used * 100)/total_bytes::numeric,2)) desc;
-- 4. Disk Space Availability
select total_tb,used_tb, available_tb, round((used_tb*100)/total_tb::numeric,2) as pct_used, round((available_tb*100)/total_tb::numeric,2) as pct_available from
(
select
       sum(round((bytes_used / power(1024,4))::numeric,2)) as used_tb,
       sum(round((bytes_available / power(1024,4))::numeric,2)) as available_tb,
       sum(round((total_bytes / power(1024,4))::numeric,2)) as total_tb
from
(
select date_trunc('hour', ctime) ctime_hour,
       to_char(ctime, 'Month') as "month",
       to_char(ctime, 'ddd') as "day",
       to_char(ctime, 'Day') as weekday,
       to_char(ctime, 'hh24') as "hour",
       hostname,
       filesystem,
       bytes_used,
       bytes_available,
       total_bytes,
       row_number() over (partition by to_char(ctime, 'ddd'), hostname order by total_bytes desc) space_used_rank
from   diskspace_now
where filesystem in ('/data1','/data2','/disk1','/disk2')
and   hostname like 'sdw%' or hostname like 'sachi%'
) a
where space_used_rank = 1
group by day
) as b;

SQL to list Databases, Schemas, Users and Roles in Greenplum

-- list databases
select datname,datacl from pg_database;

-- List Schemas
select nspname from pg_catalog.pg_namespace;

-- List User Roles
select usename as username from pg_user;

-- List Group Roles
select distinct rolname as group_role from pg_roles join pg_auth_members on (pg_roles.oid=pg_auth_members.roleid);

SQL to list Grants and Privileges in Greenplum
select obj_name,
obj_type,
grantee,
grantor,
case when grantee is null then null else case when acc like '%r%' then true else false end end select_priv,
case when grantee is null then null else case when acc like '%a%' then true else false end end insert_priv,
case when grantee is null then null else case when acc like '%w%' then true else false end end update_priv,
case when grantee is null then null else case when acc like '%d%' then true else false end end delete_priv,
case when grantee is null then null else case when acc like '%x%' then true else false end end reference_priv,
case when grantee is null then null else case when acc like '%t%' then true else false end end trigger_priv,
case when grantee is null then null else case when acc like '%X%' then true else false end end execute_priv
from
(
select obj_name,
obj_type,
split_part(priv,'=',1) grantee,
split_part(split_part(priv,'=',2), '/',2) grantor,
split_part(split_part(priv,'=',2), '/',1) acc
from
(
select n.nspname || '.' || c.relname obj_name,
case c.relkind
when 'r' then case when relstorage = 'x' then 'external table' else 'table' end
when 'v' then 'view'
when 'S' then 'sequence'
end obj_type,
priv
from pg_catalog.pg_class c
left join (select oid, regexp_split_to_table(array_to_string(relacl, ','), ',') priv from pg_class) privs on privs.oid = c.oid
left join pg_catalog.pg_namespace n on n.oid = c.relnamespace
where c.relkind in ('r', 'v', 'S')
and n.nspname NOT IN ('gp_toolkit', 'information_schema', 'pg_catalog','pg_aoseg','pg_toast')
) a
) b
;

SQL to list Schema Objects in Greenplum

-- List Schema Objects with type and storage class
SELECT b.nspname , relname, c.relkind,c.relstorage FROM pg_class c,pg_namespace b
WHERE c.relnamespace=b.oid and b.nspname NOT IN ('gp_toolkit', 'information_schema', 'pg_catalog','pg_aoseg','pg_toast')
ORDER BY b.nspname,c.relkind,c.relstorage;

-- List tables order by no of rows in the tables
SELECT   N.oid, nspname AS schemaname,relname,to_char(reltuples,'99G999G999999')
FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema') AND relkind='r'
--and relname='sachitabletest'
ORDER BY reltuples DESC;

SQL to monitor partition information in Greenplum

-- List of partition tables in all schema in your db.

select distinct tablename from pg_partitions;

select distinct schemaname,tablename from pg_partitions;

select distinct tablename,count(partitiontablename) as total_no_of_partitions from pg_partitions group by tablename;

- List the tables that are partitioned and provide the total number of partitions and subpartitions in the table.

SELECT distinct m.schemaname||'.'||m.tablename "Relation Name",
CASE
WHEN p.tolpart IS NULL THEN 0
ELSE p.tolpart
END AS "Total Parition",
CASE
WHEN s.tolsubpart IS NULL THEN 0
ELSE s.tolsubpart
END AS "Total Subpartitions"
FROM pg_partitions m
LEFT JOIN (SELECT schemaname,tablename,count(*) tolpart FROM pg_partitions WHERE parentpartitiontablename IS NULL group by schemaname,tablename ) p
ON p.schemaname=m.schemaname AND p.tablename=m.tablename
LEFT JOIN (SELECT schemaname,tablename,count(*) tolsubpart FROM pg_partitions WHERE parentpartitiontablename IS NOT NULL group by schemaname,tablename) s
ON s.schemaname=m.schemaname
AND s.tablename=m.tablename
ORDER BY 1;
SELECT n.nspname AS schemaname, c.relname AS tablename, a.attname AS columnname, p.parlevel AS partitionlevel, p.i + 1 AS position_in_partition_key
FROM pg_namespace n, pg_class c, pg_attribute a, ( SELECT p.parrelid, p.parlevel, p.paratts[i.i] AS attnum, i.i
FROM pg_partition p, generate_series(0, ( SELECT max(array_upper(pg_partition.paratts, 1)) AS max
FROM pg_partition)) i(i)
WHERE p.paratts[i.i] IS NOT NULL) p
WHERE p.parrelid = c.oid AND c.relnamespace = n.oid AND p.attnum = a.attnum AND a.attrelid = c.oid;

SELECT p1.schemaname, p1.tablename, p1.partitionname, p1.partitiontype, p1.partitionlevel,
CASE
WHEN p1.partitiontype <> 'range'::text THEN NULL::bigint
WHEN p1.partitionnodefault > 0 THEN p1.partitionrank
WHEN p1.partitionrank = 1 THEN NULL::bigint
ELSE p1.partitionrank - 1
END AS partitionrank, p1.partitionposition, p1.partitionlistvalues, p1.partitionrangestart,
CASE
WHEN p1.partitiontype = 'range'::text THEN p1.partitionstartinclusive
ELSE NULL::boolean
END AS partitionstartinclusive, p1.partitionrangeend,
CASE
WHEN p1.partitiontype = 'range'::text THEN p1.partitionendinclusive
ELSE NULL::boolean
END AS partitionendinclusive, p1.partitioneveryclause, p1.parisdefault AS partitionisdefault, p1.partitionboundary
FROM ( SELECT n.nspname AS schemaname, cl.relname AS tablename, pr1.parname AS partitionname, p.parlevel AS partitionlevel, pr1.parruleord AS partitionposition, rank() OVER(
PARTITION BY p.oid, cl.relname, p.parlevel
ORDER BY pr1.parruleord) AS partitionrank, pg_get_expr(pr1.parlistvalues, p.parrelid) AS partitionlistvalues, pg_get_expr(pr1.parrangestart, p.parrelid) AS partitionrangestart, pr1.parrangestartincl AS partitionstartinclusive, pg_get_expr(pr1.parrangeend, p.parrelid) AS partitionrangeend, pr1.parrangeendincl AS partitionendinclusive, pg_get_expr(pr1.parrangeevery, p.parrelid) AS partitioneveryclause, min(pr1.parruleord) OVER(
PARTITION BY p.oid, cl.relname, p.parlevel
ORDER BY pr1.parruleord) AS partitionnodefault, pr1.parisdefault,
CASE
WHEN p.parkind = 'h'::"char" THEN 'hash'::text
WHEN p.parkind = 'r'::"char" THEN 'range'::text
WHEN p.parkind = 'l'::"char" THEN 'list'::text
ELSE NULL::text
END AS partitiontype, pg_get_partition_rule_def(pr1.oid, true) AS partitionboundary
FROM pg_namespace n, pg_class cl, pg_partition p, pg_partition_rule pr1
WHERE p.parrelid = cl.oid AND pr1.paroid = p.oid AND cl.relnamespace = n.oid AND p.paristemplate = true) p1;

SELECT p1.schemaname, p1.tablename, p1.partitionschemaname, p1.partitiontablename, p1.partitionname, p1.parentpartitiontablename, p1.parentpartitionname, p1.partitiontype, p1.partitionlevel, CASE
WHEN p1.partitiontype <> 'range'::text THEN NULL::bigint
WHEN p1.partitionnodefault > 0 THEN p1.partitionrank
WHEN p1.partitionrank = 0 THEN NULL::bigint
ELSE p1.partitionrank
END AS partitionrank, p1.partitionposition, p1.partitionlistvalues, p1.partitionrangestart,
CASE
WHEN p1.partitiontype = 'range'::text THEN p1.partitionstartinclusive
ELSE NULL::boolean
END AS partitionstartinclusive, p1.partitionrangeend,
CASE
WHEN p1.partitiontype = 'range'::text THEN p1.partitionendinclusive
ELSE NULL::boolean
END AS partitionendinclusive, p1.partitioneveryclause, p1.parisdefault AS partitionisdefault, p1.partitionboundary, p1.parentspace AS parenttablespace, p1.partspace AS partitiontablespace
FROM ( SELECT n.nspname AS schemaname, cl.relname AS tablename, n2.nspname AS partitionschemaname, cl2.relname AS partitiontablename, pr1.parname AS partitionname, cl3.relname AS parentpartitiontablename, pr2.parname AS parentpartitionname,
CASE
WHEN pp.parkind = 'h'::"char" THEN 'hash'::text
WHEN pp.parkind = 'r'::"char" THEN 'range'::text
WHEN pp.parkind = 'l'::"char" THEN 'list'::text
ELSE NULL::text
END AS partitiontype, pp.parlevel AS partitionlevel, pr1.parruleord AS partitionposition,
CASE
WHEN pp.parkind <> 'r'::"char" OR pr1.parisdefault THEN NULL::bigint
ELSE rank() OVER(
PARTITION BY pp.oid, cl.relname, pp.parlevel, cl3.relname
ORDER BY pr1.parisdefault, pr1.parruleord)
END AS partitionrank, pg_get_expr(pr1.parlistvalues, pr1.parchildrelid) AS partitionlistvalues, pg_get_expr(pr1.parrangestart, pr1.parchildrelid) AS partitionrangestart, pr1.parrangestartincl AS partitionstartinclusive, pg_get_expr(pr1.parrangeend, pr1.parchildrelid) AS partitionrangeend, pr1.parrangeendincl AS partitionendinclusive, pg_get_expr(pr1.parrangeevery, pr1.parchildrelid) AS partitioneveryclause, min(pr1.parruleord) OVER(
PARTITION BY pp.oid, cl.relname, pp.parlevel, cl3.relname
ORDER BY pr1.parruleord) AS partitionnodefault, pr1.parisdefault, pg_get_partition_rule_def(pr1.oid, true) AS partitionboundary, COALESCE(sp.spcname, d.dfltspcname) AS parentspace, COALESCE(sp3.spcname, d.dfltspcname) AS partspace
FROM pg_namespace n, pg_namespace n2, pg_class cl
LEFT JOIN pg_tablespace sp ON cl.reltablespace = sp.oid, pg_class cl2
LEFT JOIN pg_tablespace sp3 ON cl2.reltablespace = sp3.oid, pg_partition pp, pg_partition_rule pr1
LEFT JOIN pg_partition_rule pr2 ON pr1.parparentrule = pr2.oid
LEFT JOIN pg_class cl3 ON pr2.parchildrelid = cl3.oid, ( SELECT s.spcname
FROM pg_database, pg_tablespace s
WHERE pg_database.datname = current_database() AND pg_database.dattablespace = s.oid) d(dfltspcname)
WHERE pp.paristemplate = false AND pp.parrelid = cl.oid AND pr1.paroid = pp.oid AND cl2.oid = pr1.parchildrelid AND cl.relnamespace = n.oid AND cl2.relnamespace = n2.oid) p1;
SELECT pso.classname, pso.objname, pso.objid, pso.schemaname, pso.usestatus, pso.usename, pso.actionname, pso.subtype, pso.statime,
CASE
WHEN pr.parlevel IS NOT NULL THEN pr.parlevel
ELSE pr2.parlevel
END AS partitionlevel, pcns.relname AS parenttablename, pcns.nspname AS parentschemaname, pr.parrelid AS parent_relid
FROM pg_stat_operations pso
LEFT JOIN pg_partition_rule ppr ON pso.objid = ppr.parchildrelid
LEFT JOIN pg_partition pr ON pr.oid = ppr.paroid
LEFT JOIN ( SELECT min(prx.parlevel) AS parlevel, prx.parrelid
FROM pg_partition prx
GROUP BY prx.parrelid) pr2 ON pr2.parrelid = pso.objid
LEFT JOIN ( SELECT pc.oid, pc.relname, pc.relnamespace, pc.reltype, pc.relowner, pc.relam, pc.relfilenode, pc.reltablespace, pc.relpages, pc.reltuples, pc.reltoastrelid, pc.reltoastidxid, pc.relaosegrelid, pc.relaosegidxid, pc.relhasindex, pc.relisshared, pc.relkind, pc.relstorage, pc.relnatts, pc.relchecks, pc.reltriggers, pc.relukeys, pc.relfkeys, pc.relrefs, pc.relhasoids, pc.relhaspkey, pc.relhasrules, pc.relhassubclass, pc.relfrozenxid, pc.relacl, pc.reloptions, ns.nspname, ns.nspowner, ns.nspacl
FROM pg_class pc
FULL JOIN pg_namespace ns ON ns.oid = pc.relnamespace) pcns ON pcns.oid = pr.parrelid;

-- List the tables that are partitioned and provide the total number of partitions and subpartitions in the table.

SELECT distinct m.schemaname||'.'||m.tablename "Relation Name",
CASE
WHEN p.tolpart IS NULL THEN 0
ELSE p.tolpart
END AS "Total Parition",
CASE
WHEN s.tolsubpart IS NULL THEN 0
ELSE s.tolsubpart
END AS "Total Subpartitions"
FROM pg_partitions m
LEFT JOIN (SELECT schemaname,tablename,count(*) tolpart FROM pg_partitions WHERE parentpartitiontablename IS NULL group by schemaname,tablename ) p
ON p.schemaname=m.schemaname AND p.tablename=m.tablename
LEFT JOIN (SELECT schemaname,tablename,count(*) tolsubpart FROM pg_partitions WHERE parentpartitiontablename IS NOT NULL group by schemaname,tablename) s
ON s.schemaname=m.schemaname
AND s.tablename=m.tablename
ORDER BY 1;
Tables without sub-partitions

If the table you want to query does not have subpartitions, perform the following queries:

NOTES:Ensure that you replace the <schemaname> and <tablename> with the schema and table names you want to query.
The queries below assume that you are interested in partition information for a specific table.

-- List all partition information for the table specified and provide its structural information.

SELECT p.schemaname||'.'||p.tablename "Relation Name",
p.partitionschemaname||'.'||p.partitiontablename "Partition Table",
p.partitionname "Name",
p.partitiontype "Type",
c.columnname "Partition Column",
p.partitionlevel "Level",
p.partitionrank "Rank",
p.partitionposition "Position",
CASE
WHEN p.partitionisdefault='t' then 'YES'
ELSE 'NO'
END AS "Default"
FROM pg_partitions p , pg_partition_columns c
WHERE c.schemaname=p.schemaname
AND c.tablename=p.tablename
AND c.partitionlevel=p.partitionlevel
AND (p.schemaname,p.tablename)=('<schemaname>','<tablename>');

-- If the partition is of the type "Range", use the query below to access its partition criteria.

SELECT p.schemaname||'.'||p.tablename "Relation Name",
p.partitionschemaname||'.'||p.partitiontablename "Partition Table",
p.partitionname "Name",
p.partitiontype "Type",
c.columnname "Partition Column",
p.partitionrangestart "Start Range",
CASE
WHEN p.partitionstartinclusive='t' then 'YES'
ELSE 'NO'
END AS "Start Include",
p.partitionrangeend "End Range",
CASE
WHEN p.partitionendinclusive='t' then 'YES'
ELSE 'NO'
END AS "End Include",
CASE
WHEN p.partitionisdefault='t' then 'YES'
ELSE 'NO'
END AS "Default"
FROM pg_partitions p, pg_partition_columns c
WHERE c.schemaname=p.schemaname
AND c.tablename=p.tablename
AND c.partitionlevel=p.partitionlevel
AND (p.schemaname,p.tablename)=('<schemaname>','<tablename>');

-- If the partition is of the type "List", use the query below to access its partition criteria.

SELECT p.schemaname||'.'||p.tablename "Relation Name",
p.partitionschemaname||'.'||p.partitiontablename "Partition Table",
p.partitionname "Name",
p.partitiontype "Type",
c.columnname "Partition Column",
p.partitionlistvalues "List Values",
CASE
WHEN p.partitionisdefault='t' then 'YES'
ELSE 'NO'
END AS "Default"
FROM pg_partitions p , pg_partition_columns c
WHERE c.schemaname=p.schemaname
AND c.tablename=p.tablename
AND c.partitionlevel=p.partitionlevel
AND (p.schemaname,p.tablename)=('<schemaname>','<tablename>');
Tables with sub-partitions

If the table you want to query does have subpartitions, use the queries below.

NOTES:Ensure that you replace the <schemaname> and <tablename> with the schema and table names you want to query.
For queries that request for <schemaname> and <partition tablename>, provide the schema name and partition name of the parent partition that holds the sub-partition.

The queries below assume that you are interested in partition information and subpartitions for a specific table.

-- List the partitions in the table, their partition criteria and the total counts of sub-partitions.

SELECT distinct m.schemaname||'.'||m.tablename "Relation Name",
m.partitionschemaname||'.'||m.partitiontablename "Partition Table",
m.partitiontype "Type",
c.columnname "Column",
m.partitionrangestart "Start Range",
CASE
WHEN m.partitionstartinclusive='t' then 'YES'
ELSE 'NO'
END AS "Start Include",
m.partitionrangeend "End Range",
CASE
WHEN m.partitionendinclusive='t' then 'YES'
ELSE 'NO'
END AS "End Include",
m.partitionlistvalues "List Values",
CASE
WHEN m.partitionisdefault='t' then 'YES'
ELSE 'NO'
END AS "Default",
CASE
WHEN s.tolsubpart IS NULL THEN 0
ELSE s.tolsubpart
END AS "Total Subpart's"
FROM pg_partitions m
LEFT JOIN (SELECT schemaname,tablename,parentpartitiontablename,count(*) tolsubpart FROM pg_partitions WHERE parentpartitiontablename IS NOT NULL group by schemaname,tablename,parentpartitiontablename) s
ON s.schemaname=m.schemaname
AND s.tablename=m.tablename
AND s.parentpartitiontablename=m.partitiontablename
LEFT JOIN pg_partition_columns c
ON c.schemaname=m.schemaname
AND c.tablename=m.tablename
AND c.partitionlevel=m.partitionlevel
WHERE m.parentpartitiontablename is NULL
AND (m.schemaname,m.tablename)=('<schemaname>','<tablename>')
ORDER BY 5;
-- Subpartition parent partitions structure information

SELECT p.schemaname||'.'||p.tablename "Relation Name",
p.partitionschemaname||'.'||p.partitiontablename "Partition Table",
p.partitionname "Name",
p.partitiontype "Type",
c.columnname "Partition Column",
p.partitionlevel "Level",
p.partitionrank "Rank",
p.partitionposition "Position",
CASE
WHEN p.partitionisdefault='t' then 'YES'
ELSE 'NO'
END AS "Default"
FROM pg_partitions p , pg_partition_columns c
WHERE p.parentpartitiontablename is NULL
AND c.schemaname=p.schemaname
AND c.tablename=p.tablename
AND c.partitionlevel=p.partitionlevel
AND (p.schemaname,p.tablename)=('<schemaname>','<tablename>');
-- Subpartition structure information

SELECT distinct p.schemaname||'.'||p.tablename "Relation Name",
p.partitionschemaname||'.'||p.partitiontablename "Partition Table",
s.partitionschemaname||'.'||s.partitiontablename "Subpartition Table",
s.partitionname "Name",
s.partitiontype "Type",
s.columnname "Column",
s.partitionlevel "Level",
s.partitionrank "Rank",
s.partitionposition "Position",
CASE
WHEN s.partitionisdefault='t' then 'YES'
ELSE 'NO'
END AS "Default"
FROM pg_partitions p
LEFT JOIN (SELECT s.schemaname,
s.tablename,
partitionschemaname,
partitiontablename,
parentpartitiontablename,
partitionname,
partitiontype,
s.partitionlevel,
partitionrank,
partitionposition,
partitionisdefault,
columnname
FROM pg_partitions s
LEFT JOIN pg_partition_columns c
ON c.schemaname=s.schemaname
AND c.tablename=s.tablename
AND c.partitionlevel=s.partitionlevel
WHERE s.parentpartitiontablename is NOT NULL) s
ON s.schemaname=p.schemaname
AND s.tablename=p.tablename
AND s.parentpartitiontablename=p.partitiontablename
WHERE p.parentpartitiontablename is NULL
AND (p.schemaname,p.tablename)=('<schemaname>','<tablename>')
AND (p.schemaname,p.partitiontablename)=('<schemaname>','<partition tablename>')
ORDER BY 8;

-- If subpartition is of the type "Range", use the query below to access its partition criteria.

SELECT distinct p.schemaname||'.'||p.tablename "Relation Name",
p.partitionschemaname||'.'||p.partitiontablename "Partition Table",
s.partitionschemaname||'.'||s.partitiontablename "Subpartition Table",
s.partitionname "Name",
s.partitiontype "Type",
s.columnname "Column",
s.partitionrangestart "Start Range",
CASE
WHEN s.partitionstartinclusive='t' then 'YES'
ELSE 'NO'
END AS "Start Include",
s.partitionrangeend "End Range",
CASE
WHEN s.partitionendinclusive='t' then 'YES'
ELSE 'NO'
END AS "End Include",
CASE
WHEN s.partitionisdefault='t' then 'YES'
ELSE 'NO'
END AS "Default"
FROM pg_partitions p
LEFT JOIN (SELECT s.schemaname,
s.tablename,
partitionschemaname,
partitiontablename,
parentpartitiontablename,
partitionname,
partitiontype,
s.partitionlevel,
partitionrangestart,
partitionstartinclusive,
partitionrangeend,
partitionendinclusive,
partitionisdefault,
columnname
FROM pg_partitions s
LEFT JOIN pg_partition_columns c
ON c.schemaname=s.schemaname
AND c.tablename=s.tablename
AND c.partitionlevel=s.partitionlevel
WHERE s.parentpartitiontablename is NOT NULL) s
ON s.schemaname=p.schemaname
AND s.tablename=p.tablename
AND s.parentpartitiontablename=p.partitiontablename
WHERE p.parentpartitiontablename is NULL
AND (p.schemaname,p.tablename)=('<schemaname>','<tablename>')
AND (p.schemaname,p.partitiontablename)=('<schemaname>','<partition tablename>')
ORDER BY 3;
-- If subpartition is of the type "List", use the query below to access its partition criteria

SELECT distinct p.schemaname||'.'||p.tablename "Relation Name",
p.partitionschemaname||'.'||p.partitiontablename "Partition Table",
s.partitionschemaname||'.'||s.partitiontablename "Subpartition Table",
s.partitionname "Name",
s.partitiontype "Type",
s.columnname "Column",
s.partitionlistvalues "List Values",
CASE
WHEN s.partitionisdefault='t' then 'YES'
ELSE 'NO'
END AS "Default"
FROM pg_partitions p
LEFT JOIN (SELECT s.schemaname,
s.tablename,
partitionschemaname,
partitiontablename,
parentpartitiontablename,
partitionname,
partitiontype,
s.partitionlevel,
partitionlistvalues,
partitionisdefault,
columnname
FROM pg_partitions s
LEFT JOIN pg_partition_columns c
ON c.schemaname=s.schemaname
AND c.tablename=s.tablename
AND c.partitionlevel=s.partitionlevel
WHERE s.parentpartitiontablename is NOT NULL) s
ON s.schemaname=p.schemaname
AND s.tablename=p.tablename
AND s.parentpartitiontablename=p.partitiontablename
WHERE p.parentpartitiontablename is NULL
AND (p.schemaname,p.tablename)=('<schemaname>','<tablename>')
AND (p.schemaname,p.partitiontablename)=('<schemaname>','<partition tablename>')
ORDER BY 3;

SQL to display information about Resource queues in Greenplum

-- Resource Queues in your database
select pg_resqueue.oid, rsqname from pg_catalog.pg_resqueue;

-- Resource Queue Parameter Settings
select * from pg_catalog.pg_resqueue_attributes;

-- Viewing the Roles Assigned to a Resource Queue
SELECT rolname, rsqname FROM pg_roles, gp_toolkit.gp_resqueue_status
WHERE pg_roles.rolresqueue=gp_toolkit.gp_resqueue_status.queueid
order by rsqname;

-- Viewing the Waiting Queries for a Resource Queue
SELECT * FROM gp_toolkit.gp_locks_on_resqueue WHERE lorwaiting='true';

-- Clearing a Waiting Statement From a Resource Queue
SELECT rolname, rsqname, pid, granted, current_query, datname FROM pg_roles, gp_toolkit.gp_resqueue_status, pg_locks, pg_stat_activity
WHERE pg_roles.rolresqueue=pg_locks.objid
AND pg_locks.objid=gp_toolkit.gp_resqueue_status.queueid
AND pg_stat_activity.procpid=pg_locks.pid;
\qecho -- execute pg_cancel_backend(pid) to clear the statement from RQ.
\qecho
\qecho -- Resetting the Priority of an Active Statement Superusers can adjust the priority of a statement currently being executed using the
\qecho -- built-in function gp_adjust_priority(session_id, statement_count, priority). Using this function, superusers can raise or lower
\qecho -- the priority of any\qecho -- query. For example:=# SELECT gp_adjust_priority(752, 24905, 'HIGH')
\qecho -- To obtain the session ID and statement count parameters required by this function, Superusers can use the gp_toolkit administrative schema view,
\qecho -- gp_resq_priority_statement. This function affects only the specified statement . Subsequent statements in the same resource queue
\qecho -- are executed using the queue’s normally assigned priority.

-- Resource Queue Current Activities
SELECT q.oid AS queueid, q.rsqname AS queuename, pg_stat_get_queue_num_exec(q.oid) AS n_queries_exec,
pg_stat_get_queue_num_wait(q.oid) AS n_queries_wait, pg_stat_get_queue_elapsed_exec(q.oid) AS elapsed_exec,
pg_stat_get_queue_elapsed_wait(q.oid) AS elapsed_wait FROM pg_resqueue q;

-- Resource Queue waiters and holders
SELECT
q.oid AS queueid,
q.rsqname, t1.value::integer AS rsqcountlimit,
t2.value::integer AS rsqcountvalue,
t3.value::real AS rsqcostlimit,
t4.value::real AS rsqcostvalue,
t5.value::real AS rsqmemorylimit,
t6.value::real AS rsqmemoryvalue,
t7.value::integer AS rsqwaiters,
t8.value::integer AS rsqholders
FROM pg_resqueue q,
pg_resqueue_status_kv() t1(queueid oid, key text, value text),
pg_resqueue_status_kv() t2(queueid oid, key text, value text),
pg_resqueue_status_kv() t3(queueid oid, key text, value text),
pg_resqueue_status_kv() t4(queueid oid, key text, value text),
pg_resqueue_status_kv() t5(queueid oid, key text, value text),
pg_resqueue_status_kv() t6(queueid oid, key text, value text),
pg_resqueue_status_kv() t7(queueid oid, key text, value text),
pg_resqueue_status_kv() t8(queueid oid, key text, value text)
WHERE q.oid = t1.queueid
AND t1.queueid = t2.queueid
AND t2.queueid = t3.queueid
AND t3.queueid = t4.queueid
AND t4.queueid = t5.queueid
AND t5.queueid = t6.queueid
AND t6.queueid = t7.queueid
AND t7.queueid = t8.queueid
AND t1.key = 'rsqcountlimit'::text
AND t2.key = 'rsqcountvalue'::text
AND t3.key = 'rsqcostlimit'::text
AND t4.key = 'rsqcostvalue'::text
AND t5.key = 'rsqmemorylimit'::text
AND t6.key = 'rsqmemoryvalue'::text
AND t7.key = 'rsqwaiters'::text
AND t8.key = 'rsqholders'::text;

SQL to check database activities in Greenplum

--check_current_activity
-- select * from pg_stat_activity;
SELECT datid, datname, procpid, sess_id, usesysid, usename, waiting, query_start, backend_start, xact_start, application_name, current_query FROM pg_stat_activity ;

--check_user_activity_by_database
select datname,usename,count(*) from pg_catalog.pg_stat_activity group by datname,usename order by datname,usename;

-- Check sessins started and idle for more than 60 minutes
--select * from pg_stat_activity where backend_start<now()-interval '1 second' and current_query like '%IDLE%';
select * from pg_stat_activity where substring(current_query,2,4)='IDLE' and backend_start<now()-interval '1 hour';

SQL to display Database Locks and Resource Queues in Greenplum
--check_waiting_lock
SELECT usename,COUNT(DISTINCT pid) FROM pg_locks l JOIN pg_stat_activity a ON (a.procpid=l.pid) WHERE l.granted IS FALSE GROUP BY 1 ORDER BY count DESC;

--check_size_of_resource_queue
SELECT * FROM pg_resqueue_status;

SQL to display Database Size,Schema Size, Table and Index Size in Greenplum
--Database size

select datname, pg_size_pretty(pg_database_size(datname)) as size from pg_database;
\qecho
\qecho -- Schema size
select sosdnsp as SchemaName,
round(sosdschematablesize/power(1024,3)::numeric,2) as table_size_GB ,
round(sosdschemaidxsize/power(1024,3)::numeric,2) as index_size_GB,
round((sosdschematablesize + sosdschemaidxsize)/power(1024,3)::numeric,2) as schema_size_GB
from gp_toolkit.gp_size_of_schema_disk ;
\qecho

set statement_mem='1GB';
-- Table and Index Size

SELECT tabs.nspname AS schema_name
, COALESCE(parts.tablename, tabs.relname) AS table_name
, ROUND(SUM(sotaidtablesize)/1024/1024/1024,3) AS table_GB
, ROUND(SUM(sotaididxsize)/1024/1024/1024,3) AS index_GB
, ROUND(SUM(sotaidtablesize)/1024/1024/1024,3)+ROUND(SUM(sotaididxsize)/1024/1024/1024,3) total_gb
FROM   gp_toolkit.gp_size_of_table_and_indexes_disk sotd
,     (SELECT c.oid, c.relname, n.nspname
       FROM   pg_class c
       ,      pg_namespace n
       WHERE n.oid = c.relnamespace
       AND    c.relname NOT LIKE '%_err'
      )tabs
LEFT JOIN pg_partitions parts
ON     tabs.nspname = parts.schemaname
AND    tabs.relname = parts.partitiontablename
WHERE sotd.sotaidoid = tabs.oid and tabs.nspname = 'public'
GROUP BY tabs.nspname, COALESCE(parts.tablename, tabs.relname)
ORDER BY 1 desc,(ROUND(SUM(sotaidtablesize)/1024/1024/1024,3)+ROUND(SUM(sotaididxsize)/1024/1024/1024,3)) desc,2;

-- Schema Size
select
sosdnsp as schemaname,
sosdschematablesize/(1024*1024*1024) as SchemaTableSizeGB,
sosdschemaidxsize/(1024*1024*1024) as SchemaIndxSizeGB,
round((sosdschematablesize/power(1024,4)+sosdschemaidxsize/power(1024,4)):: numeric,2) as SchemaSizeTB
from schema_size() where sosdschematablesize+sosdschemaidxsize >0;

where schema_size() function is like

As gpadmin:
-- Schema Size
create or replace function schema_size()
returns setof gp_toolkit.gp_size_of_schema_disk
as
$$
select * from gp_toolkit.gp_size_of_schema_disk;
$$
language sql volatile security definer;

-- Table Size
create or replace function table_size()
returns setof gp_toolkit.gp_size_of_table_disk
as
$$
select * from gp_toolkit.gp_size_of_table_disk;
$$
language sql volatile security definer;

-- Table and Index Size
create or replace function tableandindex_size()
returns setof gp_toolkit.gp_size_of_table_and_indexes_disk
as
$$
select * from gp_toolkit.gp_size_of_table_and_indexes_disk;
$$
language sql volatile security definer;

grant execute on function schema_size() to sachi;
grant execute on function table_size() to sachi;
grant execute on function tableandindex_size() to sachi;

SQL to monitor health check of Greenplum database

-- check missing stats report
select * from gp_toolkit.gp_stats_missing;

-- check bloat diagnosis report
select * from gp_toolkit.gp_bloat_diag;

-- check tables with random distribution key
select table_owner,table_name, COALESCE(distribution_keys,'DISTRIBUTED RANDOMLY') distribution_keys
from
(
SELECT pgn.nspname as table_owner,
pgc.relname as table_name,
pga.attname as distribution_keys
FROM (SELECT gdp.localoid,
CASE
WHEN ( Array_upper(gdp.attrnums, 1) > 0 ) THEN
Unnest(gdp.attrnums)
ELSE NULL
END AS attnum
FROM gp_distribution_policy gdp
ORDER BY gdp.localoid) AS distrokey
INNER JOIN pg_class AS pgc
ON distrokey.localoid = pgc.oid
INNER JOIN pg_namespace pgn
ON pgc.relnamespace = pgn.oid
LEFT OUTER JOIN pg_attribute pga
ON distrokey.attnum = pga.attnum
AND distrokey.localoid = pga.attrelid
ORDER BY pgn.nspname,
pgc.relname) as a where COALESCE(distribution_keys,'DISTRIBUTED RANDOMLY')='DISTRIBUTED RANDOMLY';

-- Check data distribution skew by calculating the coefficient of variation (CV) for the data stored on each segment.
select * from gp_toolkit.gp_skew_coefficients;

-- check data distribution skew by calculating the percentage of the system that is idle during a table scan, which is an indicator of processing data skew.
select * from gp_toolkit.gp_skew_idle_fractions;

-- check table not analyzed in last 3 days
SELECT
n.nspname,
c.relname,
a.statime,
'analyze '||n.nspname||'.'||c.relname||';'
FROM
pg_class c INNER JOIN pg_namespace n
ON (c.relnamespace = n.oid)
LEFT OUTER JOIN pg_partitions pgp
ON (
pgp.schemaname = n.nspname
AND pgp.tablename = c.relname
)
LEFT OUTER JOIN (
SELECT DISTINCT
objid,
statime
FROM
pg_stat_last_operation
WHERE
staactionname in ('ANALYZE')
) a
ON (a.objid = c.oid)
WHERE
n.nspname NOT IN ('pg_catalog','information_schema','gp_toolkit')
and c.relname not like 'stg%' and c.relname not like 'swap%'
AND relkind in ('r')
AND c.relstorage = 'h'
and date_trunc('day',statime) < date_trunc('day',localtimestamp- interval '3 day')
ORDER BY reltuples DESC;


-- check worst performing SQL
\c gpperfmon

select sh.ctime,query_text,username, db,rsqname,
avg(tfinish-tstart) as run_time,
--date_part('hour',tfinish - tstart)*60+ date_part('minute',tfinish - tstart)
min(to_char(sh.ctime, 'Month')) as "month",
min(to_char(sh.ctime, 'dd')) as "day",
min(to_char(sh.ctime, 'Day')) weekday,
min(to_char(sh.ctime, 'hh24')) as "hour",
count(*) as cnt,
round(avg(100 - cpu_idle)::numeric,2) as avg_cpu_used,
round(min(100 - cpu_idle)::numeric,2) as min_cpu_used,
round(max(100 - cpu_idle)::numeric,2) as max_cpu_used,
round(avg(load2)::numeric,2) as avg_load2,
round(min(load2)::numeric,2) as min_load2,
round(max(load2)::numeric,2) as max_load2,
round(avg(disk_rb_rate)::numeric,2) avg_disk_rb_rate,
round(min(disk_rb_rate)::numeric,2) min_disk_rb_rate,
round(max(disk_rb_rate)::numeric,2) max_disk_rb_rate,
round(avg(disk_wb_rate)::numeric,2) avg_disk_wb_rate,
round(min(disk_wb_rate)::numeric,2) min_disk_wb_rate,
round(max(disk_wb_rate)::numeric,2) max_disk_wb_rate,
round(avg(net_rb_rate)::numeric,2) avg_net_rb_rate,
round(min(net_rb_rate)::numeric,2) min_net_rb_rate,
round(max(net_rb_rate)::numeric,2) max_net_rb_rate,
round(avg(net_wb_rate)::numeric,2) avg_net_wb_rate,
round(min(net_wb_rate)::numeric,2) min_net_wb_rate,
round(max(net_wb_rate)::numeric,2) max_net_wb_rate,
round(avg(mem_actual_used)/power(1024,3)::numeric,2) avg_mem_actual_used_gb,
round(min(mem_actual_used)/power(1024,3)::numeric,2) min_mem_actual_used_gb,
round(max(mem_actual_used)/power(1024,3)::numeric,2) max_mem_actual_used_gb,
round(avg(swap_used)::numeric,2) avg_swap_used,
round(min(swap_used)::numeric,2) min_swap_used,
round(max(swap_used)::numeric,2) max_swap_used
from system_history sh,queries_history qh
where sh.ctime between date_trunc('day',localtimestamp- interval '2 days') and date_trunc('day',localtimestamp)
and sh.ctime=qh.ctime
--and to_char(sh.ctime, 'dd')=28
--and username not in ('gpmon')
and db not in ('gpperfmon')
and date_part('hour',tfinish - tstart)*60+ date_part('minute',tfinish - tstart)> 30
group by sh.ctime,query_text,username, db,rsqname;

Finding distribution keys of all tables in the greenplum database

SELECT pgn.nspname as schemaname,
pgc.relname as tablename,
pga.attname as distributionkey
FROM (SELECT gdp.localoid,
CASE
WHEN ( Array_upper(gdp.attrnums, 1) > 0 ) THEN
Unnest(gdp.attrnums)
ELSE NULL
END AS attnum
FROM gp_distribution_policy gdp
ORDER BY gdp.localoid) AS distrokey
INNER JOIN pg_class AS pgc
ON distrokey.localoid = pgc.oid
INNER JOIN pg_namespace pgn
ON pgc.relnamespace = pgn.oid
LEFT OUTER JOIN pg_attribute pga
ON distrokey.attnum = pga.attnum
AND distrokey.localoid = pga.attrelid
ORDER BY pgn.nspname,
pgc.relname;
Preserve the order in which distribution key is defines. View shows n row for table having composite (n columns) distribution key.

create view dbadmin.v_distribution_key as
SELECT pgn.nspname as schemaname,
pgc.relname as tablename,
pga.attname as distributionkey,distrokey.attnum
FROM (SELECT gdp.localoid,
CASE
WHEN ( Array_upper(gdp.attrnums, 1) > 0 ) THEN
Unnest(gdp.attrnums)
ELSE NULL
END AS attnum
FROM gp_distribution_policy gdp
ORDER BY gdp.localoid) AS distrokey
INNER JOIN pg_class AS pgc
ON distrokey.localoid = pgc.oid
INNER JOIN pg_namespace pgn
ON pgc.relnamespace = pgn.oid
LEFT OUTER JOIN pg_attribute pga
ON distrokey.attnum = pga.attnum
AND distrokey.localoid = pga.attrelid
ORDER by pgn.nspname,pgc.relname,distrokey.attnum ;
Preserve the order in which distribution key is defineds. View shows 1 row for table having composite (n columns) distribution key.

create view dbadmin.v_distribution_key3
as
select
a.schemaname,
a. tablename,
max(pgc.relpages) as relpages,
max(pgc.reltuples) as reltuples
, (select array_to_string(ARRAY(select distributionkey from
(
SELECT pgn.nspname as schemaname,
pgc.relname as tablename,
pga.attname as distributionkey,distrokey.attnum
FROM (SELECT gdp.localoid,
CASE
WHEN ( Array_upper(gdp.attrnums, 1) > 0 ) THEN
Unnest(gdp.attrnums)
ELSE NULL
END AS attnum
FROM gp_distribution_policy gdp
ORDER BY gdp.localoid) AS distrokey
INNER JOIN pg_class AS pgc
ON distrokey.localoid = pgc.oid
INNER JOIN pg_namespace pgn
ON pgc.relnamespace = pgn.oid
LEFT OUTER JOIN pg_attribute pga
ON distrokey.attnum = pga.attnum
AND distrokey.localoid = pga.attrelid
ORDER by pgn.nspname,pgc.relname,distrokey.attnum
) as dkey where schemaname=a.schemaname
and tablename=a.tablename),', ') )as distkey
from dbadmin.v_distribution_key a , pg_class pgc, pg_namespace pgn
where a. tablename=pgc.relname
and pgc.relnamespace = pgn.oid
and a.schemaname=pgn.nspname
group by a.schemaname,a.tablename;

create view dbadmin.v_distribution_key2
as
select
a.schemaname,
a. tablename,
max(pgc.relpages) as relpages,
max(pgc.reltuples) as reltuples
, (select array_to_string(ARRAY(select distributionkey from dbadmin.v_distribution_key where schemaname=a.schemaname
and tablename=a.tablename),', ') )as distkey
from dbadmin.v_distribution_key a , pg_class pgc, pg_namespace pgn
where a. tablename=pgc.relname
and pgc.relnamespace = pgn.oid
and a.schemaname=pgn.nspname
group by a.schemaname,a.tablename;

create view dbadmin.v_distribution_key2
as
select
a.schemaname,
a. tablename,
max(pgc.relpages) as relpages,
max(pgc.reltuples) as reltuples
, (select ARRAY(select distributionkey from dbadmin.v_distribution_key where schemaname=a.schemaname
and tablename=a.tablename))as distkey
from dbadmin.v_distribution_key a , pg_class pgc, pg_namespace pgn
where a. tablename=pgc.relname
and pgc.relnamespace = pgn.oid
and a.schemaname=pgn.nspname
group by a.schemaname,a.tablename;
select distinct
schemaname,
tablename,
threshold,
relpages,
reltuples,
count(distributionkey) over (partition by schemaname,tablename) as noofcolindistkey,
array_to_string(array_agg(distributionkey) over (partition by schemaname,tablename),',') as distkey
from
(
SELECT pgn.nspname as schemaname,
pgc.relname as tablename,
pga.attname as distributionkey,distrokey.attnum,
'72 hours' as threshold,
pgc.relpages,
pgc.reltuples
FROM (SELECT gdp.localoid,
CASE
WHEN ( Array_upper(gdp.attrnums, 1) > 0 ) THEN
Unnest(gdp.attrnums)
ELSE NULL
END AS attnum
FROM gp_distribution_policy gdp
ORDER BY gdp.localoid) AS distrokey
INNER JOIN pg_class AS pgc
ON distrokey.localoid = pgc.oid
INNER JOIN pg_namespace pgn
ON pgc.relnamespace = pgn.oid
LEFT OUTER JOIN pg_attribute pga
ON distrokey.attnum = pga.attnum
AND distrokey.localoid = pga.attrelid
ORDER by pgn.nspname,pgc.relname,distrokey.attnum)
as dkey;

SQL to display table and index bloat in Greenplum

The easiest way to monitor table bloat is run SQL

select * from gp_toolkit.gp_bloat_diag;

Here is the SQL query that runs behind it.

SELECT current_database(), schemaname, tablename, /*reltuples::bigint, relpages::bigint, otta,*/ ROUND(CASE WHEN otta=0 THEN 0.0 ELSE sml.relpages/otta::numeric END,1) AS tbloat, CASE WHEN relpages < otta THEN 0 ELSE bs*(sml.relpages-otta)::bigint END AS wastedbytes, iname, /*ituples::bigint, ipages::bigint, iotta,*/ ROUND(CASE WHEN iotta=0 OR ipages=0 THEN 0.0 ELSE ipages/iotta::numeric END,1) AS ibloat, CASE WHEN ipages < iotta THEN 0 ELSE bs*(ipages-iotta) END AS wastedibytes FROM ( SELECT schemaname, tablename, cc.reltuples, cc.relpages, bs, CEIL((cc.reltuples*((datahdr+ma- (CASE WHEN datahdr%ma=0 THEN ma ELSE datahdr%ma END))+nullhdr2+4))/(bs-20::float)) AS otta, COALESCE(c2.relname,'?') AS iname, COALESCE(c2.reltuples,0) AS ituples, COALESCE(c2.relpages,0) AS ipages, COALESCE(CEIL((c2.reltuples*(datahdr-12))/(bs-20::float)),0) AS iotta -- very rough approximation, assumes all cols FROM ( SELECT ma,bs,schemaname,tablename, (datawidth+(hdr+ma-(case when hdr%ma=0 THEN ma ELSE hdr%ma END)))::numeric AS datahdr, (maxfracsum*(nullhdr+ma-(case when nullhdr%ma=0 THEN ma ELSE nullhdr%ma END))) AS nullhdr2 FROM ( SELECT schemaname, tablename, hdr, ma, bs, SUM((1-null_frac)*avg_width) AS datawidth, MAX(null_frac) AS maxfracsum, hdr+( SELECT 1+count(*)/8 FROM pg_stats s2 WHERE null_frac<>0 AND s2.schemaname = s.schemaname AND s2.tablename = s.tablename ) AS nullhdr FROM pg_stats s, ( SELECT (SELECT current_setting('block_size')::numeric) AS bs, CASE WHEN substring(v,12,3) IN ('8.0','8.1','8.2') THEN 27 ELSE 23 END AS hdr, CASE WHEN v ~ 'mingw32' THEN 8 ELSE 4 END AS ma FROM (SELECT version() AS v) AS foo ) AS constants GROUP BY 1,2,3,4,5 ) AS foo ) AS rs JOIN pg_class cc ON cc.relname = rs.tablename JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname = rs.schemaname AND nn.nspname <> 'information_schema' LEFT JOIN pg_index i ON indrelid = cc.oid LEFT JOIN pg_class c2 ON c2.oid = i.indexrelid ) AS sml ORDER BY wastedbytes DESC
select current_database() as dbname , relid , schemaname , tablename , round(bloat,1) as bloat_ratio , pg_size_pretty(expbytes) as expected_size , pg_size_pretty(relbytes) as relation_size , pg_size_pretty(wastedbytes) as wasted_space , round(1.0 * relbytes / nullif((sum(relbytes) over ())::bigint,0),4) as relative_size , round((bloat-1) * (1.0 * relbytes / nullif((sum(relbytes) over ())::bigint, 0)), 8) as concern from ( SELECT relid , schemaname , tablename , CASE WHEN otta=0 THEN 0.0 ELSE sml.relpages/otta::numeric END AS bloat , reltuples::bigint , relpages::bigint , otta , (bs*otta)::bigint as expbytes , CASE WHEN relpages < otta THEN 0 ELSE (bs*(sml.relpages-otta))::bigint END AS wastedbytes , CASE WHEN relpages < otta THEN 0 ELSE relpages::bigint - otta END AS wastedpages , (bs*relpages)::bigint as relbytes FROM ( SELECT schemaname , tablename , cc.oid as relid , cc.reltuples , cc.relpages , bs , CEIL((cc.reltuples*((datahdr+ma-(CASE WHEN datahdr%ma=0 THEN ma ELSE datahdr%ma END))+nullhdr2+4))/(bs-20::float)) AS otta FROM ( SELECT ma , bs , schemaname , tablename , (datawidth+(hdr+ma-(case when hdr%ma=0 THEN ma ELSE hdr%ma END)))::numeric AS datahdr , (maxfracsum*(nullhdr+ma-(case when nullhdr%ma=0 THEN ma ELSE nullhdr%ma END))) AS nullhdr2 FROM ( SELECT schemaname , tablename , hdr , ma , bs , SUM((1-s.null_frac)*avg_width) AS datawidth , MAX(s.null_frac) AS maxfracsum , hdr+(1+(count(case when s.null_frac<>0 then 1 end))/8) as nullhdr FROM pg_stats s cross join ( SELECT current_setting('block_size')::numeric AS bs , CASE WHEN substring(version(),12,3) IN ('8.0','8.1','8.2') THEN 27 ELSE 23 END AS hdr , CASE WHEN version() ~ 'mingw32' THEN 8 ELSE 4 END AS ma ) AS constants GROUP BY schemaname, tablename, hdr, ma, bs ) AS foo ) AS rs JOIN pg_class cc ON cc.relname = rs.tablename and cc.relkind = 'r' JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname = rs.schemaname ) AS sml ) wrapper where wastedbytes > 2*1024*1024 and bloat >= 1.4 order by (bloat-1) * (1.0 * relbytes / nullif((sum(relbytes) over ())::bigint, 0)) desc;

Table and Index Bloat view
CREATE OR REPLACE VIEW bloat AS
SELECT
schemaname, tablename, reltuples::bigint, relpages::bigint, otta,
ROUND(CASE WHEN otta=0 THEN 0.0 ELSE sml.relpages/otta::numeric END,1) AS tbloat,
relpages::bigint - otta AS wastedpages,
bs*(sml.relpages-otta)::bigint AS wastedbytes,
pg_size_pretty((bs*(relpages-otta))::bigint) AS wastedsize,
iname, ituples::bigint, ipages::bigint, iotta,
ROUND(CASE WHEN iotta=0 OR ipages=0 THEN 0.0 ELSE ipages/iotta::numeric END,1) AS ibloat,
CASE WHEN ipages < iotta THEN 0 ELSE ipages::bigint - iotta END AS wastedipages,
CASE WHEN ipages < iotta THEN 0 ELSE bs*(ipages-iotta) END AS wastedibytes,
CASE WHEN ipages < iotta THEN pg_size_pretty(0) ELSE pg_size_pretty((bs*(ipages-iotta))::bigint) END AS wastedisize
FROM (
SELECT
schemaname, tablename, cc.reltuples, cc.relpages, bs,
CEIL((cc.reltuples*((datahdr+ma-
(CASE WHEN datahdr%ma=0 THEN ma ELSE datahdr%ma END))+nullhdr2+4))/(bs-20::float)) AS otta,
COALESCE(c2.relname,'?') AS iname, COALESCE(c2.reltuples,0) AS ituples, COALESCE(c2.relpages,0) AS ipages,
COALESCE(CEIL((c2.reltuples*(datahdr-12))/(bs-20::float)),0) AS iotta -- very rough approximation, assumes all cols
FROM (
SELECT
ma,bs,schemaname,tablename,
(datawidth+(hdr+ma-(case when hdr%ma=0 THEN ma ELSE hdr%ma END)))::numeric AS datahdr,
(maxfracsum*(nullhdr+ma-(case when nullhdr%ma=0 THEN ma ELSE nullhdr%ma END))) AS nullhdr2
FROM (
SELECT
schemaname, tablename, hdr, ma, bs,
SUM((1-null_frac)*avg_width) AS datawidth,
MAX(null_frac) AS maxfracsum,
hdr+(
SELECT 1+count(*)/8
FROM pg_stats s2
WHERE null_frac<>0 AND s2.schemaname = s.schemaname AND s2.tablename = s.tablename
) AS nullhdr
FROM pg_stats s, (
SELECT
(SELECT current_setting('block_size')::numeric) AS bs,
CASE WHEN substring(v,12,3) IN ('8.0','8.1','8.2') THEN 27 ELSE 23 END AS hdr,
CASE WHEN v ~ 'mingw32' THEN 8 ELSE 4 END AS ma
FROM (SELECT version() AS v) AS foo
) AS constants
GROUP BY 1,2,3,4,5
) AS foo
) AS rs
JOIN pg_class cc ON cc.relname = rs.tablename
JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname = rs.schemaname
LEFT JOIN pg_index i ON indrelid = cc.oid
LEFT JOIN pg_class c2 ON c2.oid = i.indexrelid
) AS sml
WHERE sml.relpages - otta > 0 OR ipages - iotta > 10
ORDER BY wastedbytes DESC, wastedibytes DESC;

SQL to check user details and its activities
1. List user information
sachi=# select
sachi-# a.rolname as "User name",
sachi-# a.oid as "User Oid",
sachi-# a.rolsuper as "Super User",
sachi-# b.rsqname as "Resource queue",
sachi-# a.rolcanlogin as "Can Login",
sachi-# a.rolconnlimit as "Connection limit",
sachi-# b.rsqcountlimit,
sachi-# b.rsqcostlimit,
sachi-# b.rsqovercommit,
sachi-# b.rsqignorecostlimit
sachi-# from
sachi-# pg_roles a,
sachi-# pg_resqueue b
sachi-# where
sachi-# a.rolresqueue=b.oid;
User name | User Oid | Super User | Resource queue | Can Login | Connection limit | rsqcountlimit | rsqcostlimit | rsqovercommit | rsqignorecostlimit
--------------+----------+------------+----------------+-----------+------------------+---------------+--------------+---------------+--------------------
admin | 88044 | f | pg_default | f | -1 | 20 | -1 | f | 0
gpadmin | 10 | t | pg_default | t | -1 | 20 | -1 | f | 0
gpmon | 96279 | t | pg_default | t | -1 | 20 | -1 | f | 0
sachi | 16994 | f | pg_default | t | -1 | 20 | -1 | f | 0
gpuser | 33477 | f | pg_default | t | -1 | 20 | -1 | f | 0
e2e_bdasvc | 180262 | f | e2erq | t | 2 | 2 | -1 | f | 0
(6 rows)

2. ACTIVITIES ON USERS
sachi=# select
sachi-# a.rolname as User,
sachi-# staactionname as Create_alter,
sachi-# stausename as Created_by,
sachi-# stasubtype as Action,
sachi-# statime as When
sachi-# from
sachi-# pg_stat_last_shoperation p,
sachi-# pg_authid a
sachi-# where
sachi-# p.objid=a.oid
sachi-# and
sachi-# classid=1260
sachi-# order by 1 ;
user | create_alter | created_by | action | when
--------------+--------------+------------+------------------+-------------------------------
admin | CREATE | gpadmin | ROLE | 2014-10-17 22:23:34.663169-04
admin | PRIVILEGE | gpadmin | GRANT | 2014-10-17 22:23:50.37452-04
e2e_bdasvc | CREATE | gpadmin | ROLE | 2015-02-25 15:56:40.121114-05
e2e_bdasvc | ALTER | gpadmin | CONNECTION LIMIT | 2015-02-25 16:06:50.401481-05
gpadmin | ALTER | gpadmin | PASSWORD | 2013-11-27 12:54:24.258383-05
gpmon | CREATE | gpadmin | ROLE | 2014-10-25 10:09:36.054814-04
gpuser | CREATE | gpadmin | ROLE | 2014-03-30 19:52:27.89646-04
sachi | ALTER | gpadmin | LOGIN | 2013-11-27 14:20:29.535817-05
sachi | CREATE | gpadmin | ROLE | 2013-11-27 13:00:00.963128-05
(9 rows)
sachi=#

3. User last LOGIN details

sachi=# select
sachi-# logtime as Lastqueryrantime,
sachi-# logsessiontime as Sessloggedintodbtime,
sachi-# loguser as User,
sachi-# logdatabase as Db,
sachi-# loghost as Fromhost,
sachi-# logseverity as Serverity,
sachi-# logmessage as Queryran
sachi-# from
sachi-# gp_toolkit.gp_log_database
sachi-# where
sachi-# loguser='sachi'
sachi-# order by 1 desc
sachi-# limit 1;

lastqueryrantime | sessloggedintodbtime | user | db | fromhost | serverity | queryran

-------------------------------+------------------------+-------+-------+----------+-----------+-------------------------------------------------------------
---------------------
2014-11-04 16:50:52.805827-05 | 2014-11-04 16:50:52-05 | sachi | sachi | [local] | FATAL | no pg_hba.conf entry for host "[local]", user "sachi", datab
ase "sachi", SSL off
(1 row)

sachi=#

4. USER PRIVILEGES

sachi=# select usename as "User name",
sachi-# nspname || '.' || relname as "Object Name",
sachi-# case relkind when 'r' then 'TABLE'
sachi-# when 'v' then 'VIEW'
sachi-# end as "Object Type",
sachi-# priv as "privilege"
sachi-# from pg_class
sachi-# join pg_namespace on pg_namespace.oid = pg_class.relnamespace,
sachi-# pg_user,
sachi-# (values('SELECT', 1),('INSERT', 2),('UPDATE', 3),('DELETE', 4)) privs(priv, privorder)
sachi-# where relkind in ('r', 'v')
sachi-# and has_table_privilege(pg_user.usesysid, pg_class.oid, priv)
sachi-# and not (nspname ~ '^pg_' or nspname = 'information_schema')
sachi-# order by 2, 1, 3, privorder;
User name | Object Name | Object Type | privilege
--------------+---------------------------------------------------------------------------+-------------+-----------
gpadmin | dbadmin.smart_analyze_config | TABLE | SELECT
gpadmin | dbadmin.smart_analyze_config | TABLE | INSERT
gpadmin | dbadmin.smart_analyze_config | TABLE | UPDATE
gpadmin | dbadmin.smart_analyze_config | TABLE | DELETE
gpmon | dbadmin.smart_analyze_config | TABLE | SELECT
gpmon | dbadmin.smart_analyze_config | TABLE | INSERT
gpmon | dbadmin.smart_analyze_config | TABLE | UPDATE
gpmon | dbadmin.smart_analyze_config | TABLE | DELETE
gpadmin | dbadmin.v_dist_key | VIEW | SELECT
gpadmin | dbadmin.v_dist_key | VIEW | INSERT
gpadmin | dbadmin.v_dist_key | VIEW | UPDATE
gpadmin | dbadmin.v_dist_key | VIEW | DELETE
gpmon | dbadmin.v_dist_key | VIEW | SELECT
gpmon | dbadmin.v_dist_key | VIEW | INSERT
gpmon | dbadmin.v_dist_key | VIEW | UPDATE
gpmon | dbadmin.v_dist_key | VIEW | DELETE
gpadmin | dbadmin.v_distribution_key | VIEW | SELECT
gpadmin | dbadmin.v_distribution_key | VIEW | INSERT
gpadmin | dbadmin.v_distribution_key | VIEW | UPDATE
gpadmin | dbadmin.v_distribution_key | VIEW | DELETE

Schema Object Relation level Administration

1. -- Count of objects per schema.

SELECT nspname as "Schema", count(*) as "# of Objects"
FROM pg_class a, pg_namespace b
WHERE a.relnamespace=b.oid
GROUP BY nspname
ORDER BY nspname;
2. Count of objects per schema group by type of relation.

SELECT
nspname as "Schema",
case relkind when 'r' then 'Table'
when 'i' then 'Index'
when 'S' then 'Sequence'
when 't' then 'Toast Table'
when 'v' then 'View'
when 'c' then 'Composite Type'
when 'o' then 'Append-only Tables'
when 's' then 'Special'
end "Object Type",
count(*) as "# of Objects"
FROM
pg_class a, pg_namespace b
WHERE
a.relnamespace=b.oid
GROUP BY
rollup(nspname,relkind);


3. List of all user Index

SELECT
t.relname as "Table Name",
i.relname as "Index name",
array_to_string(array_agg(a.attname), ', ') as "Index Columns",
case ix.indisunique when 't' then 'Unique'
else 'non-unique'
end as "index Type"
FROM pg_class t, pg_class i, pg_index ix, pg_attribute a , pg_stat_user_indexes uix
WHERE
t.oid = ix.indrelid
AND i.oid = ix.indexrelid
AND uix.indexrelid=ix.indexrelid
AND a.attrelid = t.oid
AND a.attnum = ANY(ix.indkey)
AND t.relkind = 'r'
GROUP BY
t.relname,i.relname,ix.indisunique
ORDER BY
t.relname,i.relname;
4. List of Append-only Tables w.r.t to parent table.

SELECT
a.oid as "Table OID",
b.nspname||'.'||a.relname as "Table Name",
c.segrelid as "AO Table OID",
(select 'pg_aoseg.'||relname from pg_class d where d.oid=c.segrelid) as "AO Table",
c.segidxid as "AO Index OID",
(select 'pg_aoseg.'||relname from pg_class d where d.oid=c.segidxid) as "AO Index",
case c.columnstore when 'f' then 'Row Orientation'
      when 't' then 'Column Orientation'
end as "AO Type",
case COALESCE(c.compresstype,'') when '' then 'No Compression'
      else c.compresstype
end as "Compression Type"
FROM pg_class a,pg_namespace b,(select relid,segrelid,segidxid,columnstore,compresstype from pg_appendonly) c
WHERE b.oid=a.relnamespace
and a.oid=c.relid
ORDER BY 4;

5. List of Toast Tables w.r.t to parent table.

SELECT
a.oid as "Table Oid" ,
(select d.nspname||'.'||a.relname as "Table Name" from pg_namespace d where d.oid=a.relnamespace) as "Table Name",
b.relname as "Toast Table" ,
b.oid as "Toast OID" ,
c.relname as "Toast index",
c.oid as "Toast Index OID"
FROM pg_class a , pg_class b , pg_class c
WHERE a.reltoastrelid=b.oid
and b.reltoastidxid=c.oid
ORDER BY 4;
Relation Size/Usage
6. Check the size of a relation (Excluding Index)

SELECT pg_size_pretty(pg_relation_size('< Relation name >'));
-- Check the size of an object (Including Index)

SELECT pg_size_pretty(pg_total_relation_size('< Relation name >'));
-- Size / Total Size of objects in the database.

SELECT
b.nspname ||'.'|| a.relname AS "Table Name",
case relkind when 'r' then 'Table'
when 'i' then 'Index'
end "Object Type",
pg_size_pretty(pg_relation_size(a.oid)) AS "Table Size",
pg_size_pretty(pg_total_relation_size(a.oid)) AS "Total Size"
FROM pg_class a , pg_namespace b
WHERE
b.oid = a.relnamespace
AND relkind in ('r','i')
ORDER BY pg_relation_size(a.oid) DESC;
-- Top 5 User Objects (Excluding index) in descending order.

SELECT
relname "Object Name",
nspname "Schema Name",
case relkind when 'r' then 'Table'
when 'i' then 'Index'
when 'S' then 'Sequence'
when 't' then 'Toast Table'
when 'v' then 'View'
when 'c' then 'Composite Type'
when 'o' then 'Append-only Tables'
when 's' then 'Special'
end "Object Type",
pg_size_pretty(pg_relation_size(a.oid)) AS "size"
FROM
pg_class a , pg_namespace b
WHERE
b.oid = a.relnamespace
AND nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_relation_size(a.oid) DESC
LIMIT 5;
7. Top 5 User objects (including index) in descending order.

SELECT
relname "Object Name",
nspname "Schema Name",
case relkind when 'r' then 'Table'
when 'i' then 'Index'
when 'S' then 'Sequence'
when 't' then 'Toast Table'
when 'v' then 'View'
when 'c' then 'Composite Type'
when 'o' then 'Append-only Tables'
when 's' then 'Special'
end "Object Type",
pg_size_pretty(pg_total_relation_size(a.oid)) AS "size"
FROM
pg_class a , pg_namespace b
WHERE
b.oid = a.relnamespace
and nspname NOT IN ('pg_catalog', 'information_schema')
and a.relkind!='i'
and b.nspname !~ '^pg_toast'
ORDER BY pg_total_relation_size(a.oid) DESC
LIMIT 5;
8. List of User permissions and privileges on the relation.

SELECT
grantor as "Who Gave",
grantee as "To Whom",
table_catalog as "DB Name",
table_schema ||'.'|| table_name as "TABLE NAME",
privilege_type as "Privilege",
is_grantable as "Is it grantable"
FROM information_schema.role_table_grants
ORDER BY 4;
9. Last ANALYZE on the given table

SELECT
objid::regclass as Relation,
staactionname As Command,
Statime as "Time last analyzed"
FROM
pg_stat_last_operation
WHERE
objid='<Relation_name>'::regclass
and staactionname='ANALYZE'
ORDER BY 3 desc
LIMIT 1;
List of DDL operations on a given table

SELECT
objid::regclass as Relation,
staactionname As Command,
Statime as "Time when executed"
FROM
pg_stat_last_operation
WHERE
objid='<Relation_name>'::regclass;
Miscellaneous

Postgress/Pivotal Greenplum(GPDB) has many In-built function/shortcuts to get most of the information related to relation in the database.

For Example:

List of tables in the database.

gpadmin=# \d
List of relations
Schema | Name | Type | Owner | Storage
--------+------------------------------------------------+-------+---------+----------------------
public | a | table | gpadmin | heap
public | b | table | gpadmin | heap
List of functions in the database.

gpadmin=# \df
List of functions
Schema | Name | Result data type | Argument data types | Type
--------+--------------+------------------+---------------------+--------
public | expl | boolean | q text | normal
public | test_create | text | tablename text | normal
public | test_create1 | void | tablename text | normal
(3 rows)
You can also wildcard search to get the relevant information

To list tables starting with process

gpadmin=# \d process*
Table "public.process_err"
Column | Type | Modifiers
----------+--------------------------+-----------
cmdtime | timestamp with time zone |
relname | text |
filename | text |
linenum | integer |
bytenum | integer |
errmsg | text |
rawdata | text |
rawbytes | bytea |
Distributed randomly
To list all the tables with employee in any schema.

gpadmin=# \dt *.employee
List of relations
Schema | Name | Type | Owner | Storage
--------+----------+-------+---------+---------
baby | employee | table | gpadmin | heap
base | employee | table | gpadmin | heap

a quick way to check the list of shortcut available is to use help feature which is retrieved by

\?
Some of the shortcut to get general useful information from the database are

( please note : This is specific to the database , you must be connected to the database to retrieve the information you are looking for)

Informational (options: S = show system objects, + = additional detail)
\d[S+] list tables, views, and sequences
\d[S+] NAME describe table, view, sequence, or index
\da[S] [PATTERN] list aggregates
\db[+] [PATTERN] list tablespaces
\dc[S] [PATTERN] list conversions
\dC [PATTERN] list casts
\dd[S] [PATTERN] show comments on objects
\ddp [PATTERN] list default privileges
\dD[S] [PATTERN] list domains
\des[+] [PATTERN] list foreign servers
\deu[+] [PATTERN] list user mappings
\dew[+] [PATTERN] list foreign-data wrappers
\df[antw][S+] [PATRN] list [only agg/normal/trigger/window] functions
\dF[+] [PATTERN] list text search configurations
\dFd[+] [PATTERN] list text search dictionaries
\dFp[+] [PATTERN] list text search parsers
\dFt[+] [PATTERN] list text search templates
\dg[+] [PATTERN] list roles (groups)
\di[S+] [PATTERN] list indexes
\dl list large objects, same as \lo_list
\dn[+] [PATTERN] list schemas
\do[S] [PATTERN] list operators
\dp [PATTERN] list table, view, and sequence access privileges
\dr[S+] [PATTERN] list foreign tables
\drds [PATRN1 [PATRN2]] list per-database role settings
\ds[S+] [PATTERN] list sequences
\dt[S+] [PATTERN] list tables
\dT[S+] [PATTERN] list data types
\du[+] [PATTERN] list roles (users)
\dv[S+] [PATTERN] list views
\dx [PATTERN] list external tables
\l[+] list all databases
\z [PATTERN] same as \dp

create/generate DDL of each objects available in particular schema in separate SQL file

tbl_list=`psql -At -c "select tablename from pg_tables where schemaname ='${PGSCHEMA}' order by 1;"` # Fetch all table Name
for fname in $tbl_list
do
ddl=`PGPASSWORD='passwd' pg_dump -h hostname -U gpadmin testschema -s -t "${PGSCHEMA}.$fname" >${script_dir}/${output_dir}/$fname.sql` # Fetch ddl for all tables
#pg_dump -h hostname -U gpadmin testschema -s -t "${PGSCHEMA}.$fname" >${script_dir}/${output_dir}/$fname.sql
echo "Table DDL generated : "${PGSCHEMA}.$fname | tee -a ${log_file}
done

To see all the currently active queries for all resource queues,

SELECT rolname, rsqname, locktype, objid, transaction, pid, mode, granted
FROM pg_roles, pg_resqueue, pg_locks
WHERE pg_roles.rolresqueue=pg_locks.objid
AND pg_locks.objid=pg_resqueue.oid;

To see the roles assigned to a resource queue,

SELECT rolname, rsqname
FROM pg_roles, pg_resqueue
WHERE pg_roles.rolresqueue=pg_resqueue.oid;

Delleting all pg_temp schema.
cat /dev/null > execute_drop_on_all.sh | psql -d template1 -Atc "select datname from pg_database where datname != 'template0'" | while read a; do echo "Checking database ${a}"; psql -Atc "select 'drop schema if exists ' || nspname || ' cascade;' from (select nspname from pg_namespace where nspname like 'pg_temp%' union select nspname from gp_dist_random('pg_namespace') where nspname like 'pg_temp%' except select 'pg_temp_' || sess_id::varchar from pg_stat_activity) as foo" ${a} > drop_temp_schema_$a.ddl ; echo "psql -f drop_temp_schema_$a.ddl -d ${a}" >> execute_drop_on_all.sh ; done

- Detect all the orphan process through the unix utility

source /usr/local/greenplum-db/greenplum_path.sh ; ORPHAN="ps auwxx|grep postgres|grep con[0-9]|`echo "egrep -v \\\"wal|con0|gpsyncagent"`|`psql -R '|' -d template1 -Atc "select 'con'||sess_id::text from pg_stat_activity"`\""; gpssh `psql -R' ' -d template1 -Atc " select distinct '-h ' || hostname from gp_segment_configuration "` $ORPHAN

- Check from the database end.

select 'con'|| a.mppsessionid AS "Session ID",
b.total_seg as "Total Segments",
count(a.*) AS "Total Sessions"
from
(select
distinct mppsessionid,
gp_segment_id
from pg_locks
where mppsessionid not in (select sess_id from pg_stat_activity where procpid!=pg_backend_pid() OR current_query!='<IDLE>'
OR waiting='t') and mppsessionid != 0 ) a,
(select count(*) as total_seg from gp_segment_configuration where role='p' ) b
group by 1,2
having count(a.*) < b.total_seg order by 3;

-- Check if the orphan process is blocking any session / query.

SELECT
w.relation::regclass AS "Table",
w.mode AS "Waiters Mode",
w.pid AS "Waiters PID",
w.mppsessionid AS "Waiters SessionID",
b.mode AS "Blockers Mode",
b.pid AS "Blockers PID",
b.mppsessionid AS "Blockers SessionID",
(select 'Hostname: ' || c.hostname ||' Content: '|| c.content || ' Port: ' || port from gp_segment_configuration c where c.content=b.gp_segment_id and role='p') AS "Blocking Segment"
FROM pg_catalog.pg_locks AS w,
pg_catalog.pg_locks AS b
Where ((w."database" = b."database" AND w.relation = b.relation)
OR w.transactionid = b.transaction)
AND w.granted='f'
AND b.granted='t'
AND w.mppsessionid <> b.mppsessionid
AND w.mppsessionid in (SELECT l.mppsessionid FROM pg_locks l WHERE l.granted = true AND relation in ( select relation from pg_locks where granted='f'))
AND w.gp_segment_id = b.gp_segment_id ORDER BY 1;

Orphan process is where there are process (related a gang) that is running on the segments and there is no corresponding process related to the same gang on the master or other segments.
This orphan process can arise when there is query abort , process crash , server / segment crash etc.

Known Issue
If there is a backup/restore (especially gp_dump/gp_restore) running, then to take of the parallel feature each segments connects independently to its segments and issue COPY FROM/TO, so they are not orphan Process.
If the orphan process that is detected on master is IDLE and there is no corresponding process on any segment for that con<sessionID> , its prefectly fine , the segments IDLE process is terminated by the Pivotal Greenplum based on parameter gp_vmem_idle_resource_timeout

Comments