How to find the date of when tables were last Vacuumed and Analyzed in Tanzu Greenplum

Products

• VMware Tanzu Greenplum

Issue/Introduction

This article covers how to find the date of when tables were last Vacuumed and Analyzed.

Environment

Product Version: 5.28

Resolution

Run the following commands in the database that you want the information about.

Table

Run this query to get information of a specific table:

select a.schemaname,a.relname,b.staactionname,b.statime
from pg_stat_all_tables a
   left join pg_stat_last_operation b
      on b.objid = a.relid
where b.staactionname in ( 'VACUUM','ANALYZE') and a.relname = '<table_name>'
order by 1,2,3;

Note: Replace <table_name> with an appropriate value.

Schema

Run this query to get information of a specific schema:

select a.schemaname,a.relname,b.staactionname,b.statime
from pg_stat_all_tables a
   left join pg_stat_last_operation b
      on b.objid = a.relid
where b.staactionname in ( 'VACUUM','ANALYZE') and a.schemaname='<schema_name>'
order by 4;

Note: Replace <schema_name> with an appropriate value

Database

Run this query to get information of all tables in the database with the most recent showing first:

select a.schemaname,a.relname,b.staactionname,b.statime
from pg_stat_all_tables a
   left join pg_stat_last_operation b
      on b.objid = a.relid
where b.staactionname in ( 'VACUUM','ANALYZE')
order by 4 DESC;

Vacuums

Run this query to get a list of Vacuums and the type of vacuum (Full, Freeze, normal):

select a.schemaname,a.relname,b.staactionname,b.stasubtype,b.statime
from pg_stat_all_tables a
   left join pg_stat_last_operation b
      on b.objid = a.relid
where b.staactionname = 'VACUUM'      
order by 1,2,3;

Note: If stasubtype is empty, then it is a normal Vacuum.

Reindex

Run this query to get the date of the last REINDEX of an index:

select nspname, relname, staactionname, stasubtype, statime
from pg_class c
   left join pg_stat_last_operation o on (c.oid = o.objid)
   join pg_namespace n on ( c.relnamespace = n.oid)
where c.relkind='i'
and statime < now() - '2 days'::interval
order by 1,2;

Create VACUUM and ANALYZE commands

To create VACUUM query for tables that have not been VACUUM'ed in the last 7 days:

select 'VACUUM ' || a.schemaname || '.' || a.relname || ';'
from pg_stat_all_tables a
   left join pg_stat_last_operation b
      on b.objid = a.relid
where b.staactionname = 'VACUUM'
    and b.statime < now() - '7 days'::interval
order by 1;

To create ANALYZE query for tables that have not been ANALYZE'd in the last 7 days:

select 'ANALYZE ' || a.schemaname || '.' || a.relname || ';'
from pg_stat_all_tables a
   left join pg_stat_last_operation b
      on b.objid = a.relid
where b.staactionname = 'ANALYZE'
    and b.statime < now() - '7 days'::interval
order by 1;

Note: The interval of "7 days" can be increased or decreased as required.