how to check user table distribution key.

Products

• VMware Tanzu Greenplum

Issue/Introduction

Sometimes users may want to generate a list of user table distribution key, then we can use the query listed below.

Environment

greenplum v6.x

Resolution

If interested in user table distribution key, we can use below query:

gpadmin=# create table ab(id int) distributed by (id);
CREATE TABLE
gpadmin=# select n.nspname "schema name",c.relname "table name",t.attname "distribution key" from pg_class c , pg_namespace n ,gp_distribution_policy p,pg_attribute t
where n.oid=c.relnamespace and c.relname='ab'  and c.oid=p.localoid and c.oid=t.attrelid and  t.attnum = any(p.distkey) ;
 schema name | table name | distribution key
-------------+------------+------------------
 public      | ab         | id
(1 row)

 

To list all user table distribution key, we can use below query

select n.nspname "schema name",c.relname "table name",t.attname "distribution key" from pg_class c , pg_namespace n ,gp_distribution_policy p,pg_attribute t
where n.oid=c.relnamespace  and c.oid=p.localoid and c.oid=t.attrelid and  t.attnum = any(p.distkey) ;

Additional Information

Please note:
For distributed replicated table, there is no distribution key, but there is a record in gp_distribution_policy table.

gpadmin=# create table abrep (id int) distributed replicated;
CREATE TABLE
gpadmin=# select oid,relname from pg_class where relname='abrep';
  oid  | relname
-------+---------
 93334 | abrep
(1 row)
gpadmin=# select * from gp_distribution_policy where localoid=93334;
 localoid | policytype | numsegments | distkey | distclass
----------+------------+-------------+---------+-----------
    93334 | r          |           4 |         |
(1 row)

 

For catalog table, external table, there is no record at all in gp_distribution_policy table.