gp_toolkit.gp_stats_missing

Post date: Jan 20, 2014 8:9:4 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.