Base tables, which statistics is probably not up to date

Query goal:

Find base tables where statistics has not been collected at all or it has been lastly collected more than 40 days ago.

Query type:

Problem detection (Each row in the result could represent a flaw in the design)

Query reliability:

High (Few or no false-positive results)

Query license:

MIT License

Fixing suggestion:

Analyze the table.

Data source:

INFORMATION_SCHEMA+system catalog

SQL query:

Click on query to copy it

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 for generating SQL statements that help us to fix the problem

SQL query	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.

SQL query

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 where the query belongs to

Category name	Category description
Performance	Queries of this category provide information about indexes in a database.

Category name

Category description

Performance

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