Analyze tables

Job details

Name:	Analyze tables
Platform:	Postgres
Category:	Maintenance
Description:	dbWatch engine framework job (for internal use only). Used for patching or upgrading of dbWatch engine framework.
Long description:	This job runs ANALYZE statement on every database to collect statistics about the contents of tables.
Version:	2.2
Default schedule:	40 6 * *
Requires engine install:	No
Compatibility tag:	.[type=‘instance’ & databasetype=‘postgres’]/.[newer_than_ninetwo = ‘1′]

Name	Default value	Description
ignore_databases	postgres_full_backup.tar	The list of databases to be ignored (separated by comma).

Purpose: The purpose of this job is to execute the ANALYZE statement on all databases within a PostgreSQL instance to update the statistics about tables’ data distribution, which PostgreSQL query planner relies on for optimizing queries.
Why: This job is important because accurate and up-to-date statistics ensure efficient execution plans for database queries which can improve overall performance. Omitting regular analysis could lead to suboptimal query performance and slow response times.
Manual checking: To manually check if `ANALYZE` has been run you can issue these SQL commands:

SELECT relname, last_analyze
FROM pg_stat_all_tables
WHERE schemaname = 'your_schema_name';

This job selects all non-template databases except the ‘rdsadmin’ in a PostgreSQL instance, as specified in the component query.
It checks a list of databases that are to be ignored from the analysis, defined by the “ignore_databases” property.
For each database not in the ignore list, the job executes the ANALYZE statement.
After analysis, the job updates the status and accumulates details of databases that were analyzed.

In case of errors during execution, the job updates the status with an error message indicating the nature of the error.

This job includes a default report titled “Analyze tables”.
- The report displays:

Database	Analysis Status
Default {‘dbwatch’}
Additional databases listed (not ignored)	Analyzed, with datetime of analysis

This textual report is designed to give a quick summary of the job’s execution, focusing on the number of databases analyzed and any exceptions handled (like ignoring certain databases based on configuration).

This job is tailor-made for PostgreSQL instances, especially those versions newer than 9.2 (as indicated by the compatibility notes in the XML content).
It automatically excludes databases typically used for internal processes like ‘rdsadmin’ and respects a user-defined list of databases to ignore from the analysis.