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;

----------+------------+------------+-------------+-------------+---------

(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.