posted Jan 20, 2014, 12:05 PM by Sachchida Ojha   [ updated Jan 20, 2014, 12:15 PM ]
gp_bloat_diag view helps to identify tables that need routine table maintenance (VACUUM and/or ANALYZE). 
gpadmin-# \d gp_toolkit.gp_bloat_diag
  View "gp_toolkit.gp_bloat_diag"
   Column    |  Type   | Modifiers 
 bdirelid    | oid     | 
 bdinspname  | name    | 
 bdirelname  | name    | 
 bdirelpages | integer | 
 bdiexppages | numeric | 
 bdidiag     | text    | 
View definition:
 SELECT bloatsummary.btdrelid AS bdirelid, bloatsummary.fnnspname AS bdinspname, bloatsummary.fnrelname AS bdirelname, bloatsummary.btdrelpages AS bdirelpages, bloatsummary.btdexppages AS bdiexppages, ( AS bdidiag
   FROM ( SELECT fn.fnoid, fn.fnnspname, fn.fnrelname, beg.btdrelid, beg.btdrelpages, beg.btdexppages, gp_toolkit.gp_bloat_diag(beg.btdrelpages, beg.btdexppages::integer, iao.iaotype) AS bd
           FROM gp_toolkit.gp_bloat_expected_pages beg, pg_class pgc, gp_toolkit.__gp_fullname fn, gp_toolkit.__gp_is_append_only iao
          WHERE beg.btdrelid = pgc.oid AND pgc.oid = fn.fnoid AND iao.iaooid = pgc.oid) bloatsummary
  WHERE ( > 0;

bdirelid=>Table object id.
bdinspname=>Schema name.
bdirelname=>Table name.
bdirelpages=>Actual number of pages on disk.
bdiexppages=>Expected number of pages given the table data.
bdidiag=>Bloat diagnostic message.

gpadmin=# select * from  gp_toolkit.gp_bloat_diag;
 bdirelid | bdinspname | bdirelname | bdirelpages | bdiexppages | bdidiag 
(0 rows)

This view shows tables that have bloat (the actual number of pages on disk exceeds the expected number of pages given the table statistics). Tables that are bloated require a VACUUM or a VACUUM FULL in order to reclaim disk space occupied by deleted or obsolete rows. This view is accessible to all users, however non-superusers will only be able to see the tables that they have permission to access.