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


Comments