Post date: Jan 20, 2014 8:5:58 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.