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