This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

Custom Alert - Last AutoAnalyze

Description

ANALYZE gathers statistics for the query planner to create the most efficient query execution paths. Per PostgreSQL documentation, accurate statistics will help the planner to choose the most appropriate query plan, and thereby improve the speed of query processing. 

This alert utilizes pg_stat_user_tables to let you know how old a given relation(s) statistics are.  

Alert Definition

To create the alert, click on Alerts > Manage Alerts tab and create a Custom Alert of type Custom SQL Alert - Multiple Numeric Return. Configure the alert with values similar to these:

Alert Name: Last AutoAnalyze

Execution Interval: 8 hours -- You may want to change the interval based on your database workload.  

Notification Text: Empty

SQL Statement:

select schemaname ||'.'||relname, extract(hour FROM now()-last_autoanalyze) from pg_stat_user_tables
where last_autoanalyze is not null;

Execute Against: Monitored Instance

Units: Hours

High Threshold: Min 72, Max <blank> 

Medium Threshold: Min 24, Max 72

Low Threshold: Min 8, Max 24