Script- Object and Relation Level Administration

Products

• VMware Tanzu Greenplum

Issue/Introduction

This article applies to Pivotal Greenplum Database (GPDB) all versions.

This article provides a simple script to help the administrator in identifying relation level information.

Note: Please verify the script on a test cluster before running it on production.

 

Resolution

Relation Counts

-- Count of objects per schema.

select
   nspname as "schema",
   count(*) as "num_of_objects"
FROM pg_class a, pg_namespace b
WHERE a.relnamespace = b.oid
GROUP BY nspname 
ORDER BY nspname;

-- 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);

-- 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; 

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; 

List of Toast Tables w.r.t to the 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 and Usage

-- 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; 

-- 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;       

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;

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;

List of objects

SELECT
   c.oid as object_oid,
   quote_ident(n.nspname) as Schema_name,
   quote_ident(c.relname) as Object_Name,
   CASE c.relkind
      WHEN 'r' THEN 'table'
      WHEN 'v' THEN 'view'
      WHEN 'i' THEN 'index'
      WHEN 'S' THEN 'sequence'
      WHEN 's' THEN 'special'
      WHEN 'c' THEN 'composite type'
   END as object_Type,
   pg_catalog.pg_get_userbyid(c.relowner) as object_Owner,
   CASE c.relstorage
      WHEN 'h' THEN 'heap'
      WHEN 'x' THEN 'external'
      WHEN 'a' THEN 'append only'
      WHEN 'v' THEN 'none'
      WHEN 'c' THEN 'append only columnar'
      WHEN 'f' THEN 'foreign'
   END as object_Storage,
   substring(array_to_string(reloptions, ',') from 'compresslevel=([0-9])') AS compresslevel,
   substring(array_to_string(reloptions, ',') from 'compresstype=([a-z0-9])') AS compresstype,
   coalesce(substring(array_to_string(reloptions, ',') from 'orientation=([a-z0-9]*)'),'row') AS orientation,
   relacl as object_grants
FROM pg_catalog.pg_class c
INNER JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
ORDER BY Schema_name, Object_Name;

Miscellaneous

Postgres and Pivotal Greenplum(GPDB) has many In-built function/shortcuts to get most of the information related to the 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 the 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 an 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 shortcuts to get general useful information from the database are

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