SELECT schemaname, relname, last_analyze
FROM pg_catalog.pg_stat_all_tables
WHERE (schemaname, relname) IN
(SELECT tables.table_schema, tables.table_name
FROM information_schema.tables
WHERE tables.table_type IN ('BASE TABLE') AND
table_schema NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL)) AND
(last_analyze IS NULL OR (current_date-last_analyze::date)>40)
ORDER BY last_analyze, schemaname, relname;
SQL statements that help generate fixes for the identified problem.
SQL Query to Generate Fix
Description
SELECT format('ANALYZE %1$I.%2$I;', schemaname, relname) AS statements
FROM pg_catalog.pg_stat_all_tables
WHERE (schemaname, relname) IN
(SELECT tables.table_schema, tables.table_name
FROM information_schema.tables
WHERE tables.table_type IN ('BASE TABLE') AND
table_schema NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL)) AND
(last_analyze IS NULL OR (current_date-last_analyze::date)>40)
ORDER BY schemaname, relname;
Analyze the table.
SELECT 'ANALYZE;' AS statements;
Analyze the entire database.
Categories
This query is classified under the following categories:
Name
Description
Performance
Queries of this category provide information about indexes in a database.