WITH base_tables AS (SELECT 
  pg_class.relname AS table_name, 
  pg_namespace.nspname AS table_schema,
 reloptions AS reloptions
FROM 
  pg_catalog.pg_class, 
  pg_catalog.pg_namespace
WHERE 
  pg_class.relnamespace = pg_namespace.oid AND relkind='r'),
  base_tables_reloptions AS (SELECT table_name, table_schema, unnest(reloptions) AS reloptions
 FROM  base_tables),
base_tables_fillfactor AS (SELECT table_schema, table_name, regexp_replace(reloptions,'[^0-9]','','g')::int AS fillfactor
FROM base_tables_reloptions
WHERE reloptions ILIKE 'fillfactor%'),
high_ff AS (SELECT table_schema, table_name
FROM base_tables AS bt
WHERE 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 NOT EXISTS (SELECT 1
FROM base_tables_fillfactor AS btf
WHERE bt.table_schema=btf.table_schema 
AND bt.table_name=btf.table_name
AND btf.fillfactor<>100)),
state_classif AS (SELECT A.table_schema, A.table_name , A.column_name
FROM information_schema.columns A INNER JOIN information_schema.tables T USING (table_schema, table_name)
INNER JOIN information_schema.schemata S
ON A.table_schema=S.schema_name
WHERE T.table_type='BASE TABLE'
AND A.column_name ~*'(seisund|state|olek)'
AND (A.table_schema, A.table_name , A.column_name) NOT IN
(
SELECT 
  table_constraints.table_schema, 
  table_constraints.table_name, 
  constraint_column_usage.column_name
FROM 
  information_schema.table_constraints INNER JOIN 
  information_schema.constraint_column_usage
  USING (table_schema, table_name, constraint_name)
WHERE 
  table_constraints.constraint_type IN ('PRIMARY KEY', 'UNIQUE')
) AND (A.table_schema = 'public'
OR S.schema_owner<>'postgres')),
reasons AS (SELECT table_schema, table_name, 'Contains optional columns' AS reason
FROM high_ff AS f
WHERE EXISTS (SELECT 1
FROM information_schema.columns AS c
WHERE c.table_schema=f.table_schema AND c.table_name=f.table_name
AND c.is_nullable='YES')
UNION SELECT table_schema, table_name, 'Contains boolean columns' AS reason
FROM high_ff AS f
WHERE EXISTS (SELECT 1
FROM information_schema.columns AS c WHERE c.table_schema=f.table_schema AND c.table_name=f.table_name
AND c.data_type = 'boolean')
UNION SELECT table_schema, table_name, 'Contains long textual columns' AS reason
FROM high_ff AS f
WHERE EXISTS (SELECT 1
FROM information_schema.columns AS c WHERE c.table_schema=f.table_schema AND c.table_name=f.table_name
AND ((c.data_type = 'text') OR (c.data_type='character varying' AND c.character_maximum_length IS NULL)))
UNION SELECT table_schema, table_name, 'Have state classifier' AS reason
FROM high_ff AS f
WHERE EXISTS (SELECT 1
FROM state_classif AS c WHERE c.table_schema=f.table_schema AND c.table_name=f.table_name)
UNION SELECT table_schema, table_name, 'Contains last update time' AS reason
FROM high_ff AS f
WHERE EXISTS (SELECT 1
FROM information_schema.columns AS c WHERE c.table_schema=f.table_schema AND c.table_name=f.table_name
AND c.data_type ~*'(timestamp|date)'
AND c.column_name~*'(muutmis|update|change|modify)')
UNION SELECT table_schema, table_name, 'Date or timestamp could be initially missing, i.e., it will be added later' AS reason
FROM high_ff AS f
WHERE EXISTS (SELECT 1
FROM information_schema.columns AS c WHERE c.table_schema=f.table_schema AND c.table_name=f.table_name
AND c.data_type ~*'(timestamp|date)'
AND c.column_default~*'infinity')
)
SELECT table_schema, table_name, string_agg(reason, ';' ORDER BY reason) AS indications_that_the_table_will_be_updated, 
Count(*) AS the_number_of_different_indications, 
(SELECT Count(*) AS cnt FROM pg_indexes AS pi WHERE pi.schemaname=r.table_schema AND pi.tablename=r.table_name) AS number_of_indexes
FROM reasons AS r
GROUP BY table_schema, table_name
ORDER BY Count(*) DESC, number_of_indexes DESC, table_schema, table_name;