Finding distribution keys of all tables in the greenplum database

Post date: Jul 25, 2014 11:59:56 AM

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;