Greenplum: Identify partitions with missing stats from gp_toolkit.gp_stats_missing view

Products

• VMware Tanzu Data Suite

Issue/Introduction

What is the best way to identify partitions in a table that are missing stats?

In GP v6 using the view gp_toolkit.gp_stats_missing shows empty partitions also as missing stats. 

<database_name>=# SELECT * FROM gp_toolkit.gp_stats_missing where smischema='<schema_name>' and smitable like '<table_prefix>_%';
 smischema  |              smitable               | smisize | smicols | smirecs
------------+-------------------------------------+---------+---------+---------
 <schema_name> | <table_name_partition_suffix>       | f       |      <col_count> |       0

<database_name>=# select count(*) from <table_name_partition_suffix>;
 count
-------
     0
(1 row)

 

 

 

Cause

 

Skipping ANALYZE on table partitions will result in partitions with missing stats. Truncating tables can also set relpages to zero.

 

 

 

Resolution

A quick way to check if tables or leaf partitions are missing stats is by running the query below. However, this will not apply for root partitioned tables.

select relname from pg_class c LEFT JOIN pg_catalog.pg_partition p ON c.oid = p.parrelid where relpages = 0 and relkind = 'r' and p.parrelid is null;

 

The key here is that relpages being 0 means that a table has not been analyzed, except for intermediate/root partitioned tables, which will always have 0 for relpages.

There will be no changes to gp_toolkit views in Greenplum v 6X as it's part of the catalog .

Greenplum v7x has updated the `gp_stats_missing` view to be more accurate.