Goal Find base tables where statistics has not been collected at all or it has been lastly collected more than 40 days ago.
Type Problem detection (Each row in the result could represent a flaw in the design)
Reliability High (Few or no false-positive results)
License MIT (opens in new tab)
Fixing Suggestion Analyze the table.
Data Source INFORMATION_SCHEMA+system catalog
SQL Query 
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.

Fix Action #1
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.

Fix Action #2
SELECT 'ANALYZE;' AS statements;

Analyze the entire database.

SQL Query to Generate FixDescription
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:

Performance

Queries of this category provide information about indexes in a database.

NameDescription
PerformanceQueries of this category provide information about indexes in a database.