Home 2018 February Greenplum: gp_stats_missing to get the list of pending ANALYZE of Tables

Greenplum: gp_stats_missing to get the list of pending ANALYZE of Tables

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

In this post, I am sharing an important gp_toolkit view to finding a list of a table on which ANALYZE is required to be run in Greenplum Database Server.

An ANALYZE command is used to update the object or database statistics information. The query planner always uses the statistics for building the execution plans.

A gp_toolkit is an administrative schema of Greenplum Database server. It provides the list of various views to monitor the Greenplum server.

A gp_stats_missing is one of the views of a gp_toolkit schema which gives us a list of tables which may require an ANALYZE to be run.

Below is a query:

After executing the above query, you can get the list of five columns. A column “smisize” is an important column to check the status of table statistics.

If it contains value ‘f’, a table does not have row count and row sizing statistics recorded in the system catalog, which indicates that the table needs to be analyzed.

The meaning of other columns like “simcols”: number of columns in the table, “simrecs”: number of rows in the table.

Anvesh Patel
Greenplum: How to Troubleshoot running Database Backup ProcessGreenplum: gp_bloat_diag to get the list of pending VACUUM of Tables
Comments: 2
  1. Anuraag
    February 6, 2018 at 6:49 pm

    Hi Anvesh,

    Hope you are doing good.

    I have gone through the post related to “Greenplum: gp_stats_missing to get the list of pending ANALYZE of Tables” and you mentioned that
    ”
    A column “smisize” is an important column to check the status of table statistics.
    If it contains value ‘f’, a table does not have row count and row sizing statistics recorded in the system catalog, which indicates that the table needs to be analyzed

    ”
    In the below pivotal link, they mentioned that
    “smisize = “t” meaning relpages or reltuples are not zero , “f” means either relpages or reltuples are zero
    smicols = Number of columns in the table
    smirecs = number of column in the table that has statistics information in the pg_statistic table
    ”
    From what I understand, if a table has entry in the view then
    1. Table has no statistics and should perform analyze operation
    2. Table has “0” records and that will be showed in “smisize” column. Even after analyze operation, table will not be removed from the view as it has zero records

    Some useful points:
    1. gp_stats_missing tells you which table needs statistics/analyze if table with data have not been analyzed anytime in its lifecycle / or when the table has altered to add / drop column.
    2. gp_stats_missing doesn’t remove the entry from its view when the table has no rows / or when tuples / pages has been reduced to zero after deleting of data
    3. gp_stats_missing doesn’t tell you if the table needs statistics after updates / delete / bulk loading of data , since it only checks for relpages / reltuples = 0

    ReplyCancel

Leave a Reply

CAPTCHA

*

Anvesh Patel
Anvesh Patel

Database Engineer

February 4, 2018 2 Comments Greenplum, , , , , , , , , , , , , , , , ,