We have migrated to new Google sites!
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.