Fixing gp_bloat_diag view - showing bloated tables even there are no bloats on those tables

posted Aug 30, 2018, 11:12 AM by Sachchida Ojha
Recently we found that gp_toolkit.gp_bloat_diag view shows some bloated tables which were recently reorg (vacuum full) and analyzed .

Problem was due to incorrect data reported by gp_toolkit.gp_bloat_expected_pages called by gp_toolkit.gp_bloat_diag.  Fix is to recreate gp_toolkit.gp_bloat_expected_pages using following SQL. This fix came from Pivotal support and will be part of next release.

create or replace view  gp_toolkit.gp_bloat_expected_pages as  SELECT subq.btdrelid, subq.btdrelpages,
            WHEN subq.btdexppages < subq.numsegments::numeric THEN subq.numsegments::numeric
            ELSE subq.btdexppages
        END AS btdexppages
   FROM ( SELECT pgc.oid AS btdrelid, pgc.relpages AS btdrelpages, ceil((pgc.reltuples * (25::double precision + btwcols.width))::numeric / current_setting('block_size'::text)::numeric) AS btdexppages, ( SELECT __gp_number_of_segments.numsegments
                   FROM gp_toolkit.__gp_number_of_segments) AS numsegments
           FROM ( SELECT pgc.oid, pgc.reltuples, pgc.relpages
                   FROM pg_class pgc
                  WHERE NOT EXISTS ( SELECT __gp_is_append_only.iaooid
                           FROM gp_toolkit.__gp_is_append_only
                          WHERE __gp_is_append_only.iaooid = pgc.oid AND __gp_is_append_only.iaotype = true)
                 AND NOT EXISTS
                    SELECT parrelid
                    FROM pg_partition
                    WHERE parrelid = pgc.oid
                          ) pgc
      LEFT JOIN ( SELECT pgs.starelid, sum(pgs.stawidth::double precision * (1.0::double precision - pgs.stanullfrac)) AS width
                   FROM pg_statistic pgs
                  GROUP BY pgs.starelid) btwcols ON pgc.oid = btwcols.starelid
     WHERE btwcols.starelid IS NOT NULL) subq;