gp_toolkit.gp_stats_missing

posted Jan 20, 2014, 12:09 PM by Sachchida Ojha   [ updated Jan 20, 2014, 12:12 PM ]
gp_toolkit.gp_stats_missing view helps to identify tables that need routine table maintenance (VACUUM and/or ANALYZE). 

gpadmin-# \d gp_toolkit.gp_stats_missing 
View "gp_toolkit.gp_stats_missing"
  Column   |  Type   | Modifiers 
-----------+---------+-----------
 smischema | name    | 
 smitable  | name    | 
 smisize   | boolean | 
 smicols   | bigint  | 
 smirecs   | bigint  | 
View definition:
 SELECT aut.autnspname AS smischema, aut.autrelname AS smitable, 
        CASE
            WHEN aut.autrelpages = 0 OR aut.autreltuples = 0::double precision THEN false
            ELSE true
        END AS smisize, attrs.attcnt AS smicols, COALESCE(bar.stacnt, 0::bigint) AS smirecs
   FROM gp_toolkit.__gp_user_tables aut
   JOIN ( SELECT pg_attribute.attrelid, count(*) AS attcnt
           FROM pg_attribute
          WHERE pg_attribute.attnum > 0
          GROUP BY pg_attribute.attrelid) attrs ON aut.autoid = attrs.attrelid
   LEFT JOIN ( SELECT pg_statistic.starelid, count(*) AS stacnt
      FROM pg_statistic
     GROUP BY pg_statistic.starelid) bar ON aut.autoid = bar.starelid
  WHERE (aut.autrelkind = 'r'::"char" AND (aut.autrelpages = 0 OR aut.autreltuples = 0::double precision)) OR (bar.stacnt IS NOT NULL AND attrs.attcnt > bar.stacnt);

smischema => Schema name.
smitable => Table name.
smisize => Does this table have statistics? False if the table does not have row count and row sizing statistics recorded in the system catalog, which may indicate that the table needs to be analyzed. This will also be false if the table does not contain any rows. For example, the parent tables of partitioned tables are always empty and will always return a false result.
smicols=> Number of columns in the table.
smirecs=> Number of rows in the table.

gpadmin=# select * from gp_toolkit.gp_stats_missing;
 smischema | smitable | smisize | smicols | smirecs 
-----------+----------+---------+---------+---------
(0 rows)

This view shows tables that do not have statistics and therefore may require an ANALYZE be run on the table.
Comments