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. |
gp toolkit schema >