gp_toolkit.gp_bloat_diag

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, (bloatsummary.bd).bltdiag 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 (bloatsummary.bd).bltidx > 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.