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