The list of all the queries

FILLFACTOR is probably too small (2)

Query goal: Find base tables that probably implement many to many relationship type, have no clear sign that there are columns that should be updated, but still have fillfactor less than 100.
Query type: Problem detection (Each row in the result could represent a flaw in the design)
Query reliability: Medium (Medium number of false-positive results)
Query license: MIT License
Fixing suggestion: If the rows in the table are never updated (i.e., only operations are INSERT and DELETE, then set the fillfactor back to 100).
Data source: INFORMATION_SCHEMA+system catalog
SQL query: Click on query to copy it

with keys as (select 
(select nspname from pg_namespace where oid=c.relnamespace) as key_schema,
c.relname as key_table, 
c.oid as key_table_oid,
o.conkey AS key_col
from pg_constraint o inner join pg_class c on c.oid = o.conrelid
where o.contype in ('u', 'p') and cardinality(o.conkey)=1),
keys_unnest as (select key_schema, key_table, key_table_oid, key_col, key_col_num, ordin
from keys, unnest(keys.key_col) with ordinality as k(key_col_num, ordin)),
keys_with_names AS (select key_schema AS table_schema, key_table AS table_name, a_key.attname AS column_name
from keys_unnest k inner join pg_attribute a_key on k.key_col_num = a_key.attnum and k.key_table_oid = a_key.attrelid and a_key.attisdropped = false
),

fk as (select 
(select nspname from pg_namespace where oid=c.relnamespace) as key_schema,
c.relname as key_table, 
c.oid as key_table_oid,
o.conkey AS key_col
from pg_constraint o inner join pg_class c on c.oid = o.conrelid
where o.contype='f'),
fk_unnest as (select key_schema, key_table, key_table_oid, key_col, key_col_num, ordin
from fk, unnest(fk.key_col) with ordinality as k(key_col_num, ordin)),
fk_with_names AS (select key_schema AS table_schema, key_table AS table_name, a_key.attname AS column_name
from fk_unnest k inner join pg_attribute a_key on k.key_col_num = a_key.attnum and k.key_table_oid = a_key.attrelid and a_key.attisdropped = false),
key_not_fk AS (select table_schema, table_name, column_name
from keys_with_names
except select table_schema, table_name, column_name
from fk_with_names),

surrogate_key AS (SELECT c.table_schema, c.table_name , c.column_name
FROM information_schema.columns c
LEFT JOIN information_schema.domains d USING (domain_schema, domain_name)
INNER JOIN information_schema.schemata s
ON c.table_schema=s.schema_name
WHERE  (coalesce (c.column_default, d.domain_default) ILIKE '%nextval%'  OR c.is_identity='YES')
AND c.data_type IN ('smallint','integer','bigint')
AND (c.table_schema = 'public'
OR s.schema_owner<>'postgres')
AND EXISTS (SELECT *
FROM key_not_fk AS k
WHERE k.table_schema=c.table_schema
AND k.table_name=c.table_name
AND k.column_name=c.column_name)),

allowed_cols AS (SELECT table_schema, table_name, column_name
FROM surrogate_key
UNION SELECT table_schema, table_name, column_name
FROM fk_with_names),

allowed_cols_grouped AS (SELECT table_schema, table_name, string_agg(column_name, ',' ORDER BY column_name) AS columns
FROM allowed_cols
GROUP BY table_schema, table_name),
at_least_two_fk as (select n.nspname as table_schema, c.relname as table_name
from pg_constraint o inner join pg_class c on c.oid = o.conrelid
inner join pg_namespace as n on n.oid=c.relnamespace
where o.contype = 'f'
group by n.nspname, c.relname
having count(*)>1),

potential_tables AS (SELECT table_schema, table_name, string_agg(column_name, ',' ORDER BY column_name) AS columns
FROM INFORMATION_SCHEMA.columns AS c
WHERE (table_schema, table_name) IN (SELECT table_schema, table_name
FROM INFORMATION_SCHEMA.tables
WHERE table_type='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 (table_schema, table_name) IN (SELECT table_schema, table_name
FROM  at_least_two_fk)
GROUP BY table_schema, table_name),

many_to_many_tables AS (SELECT table_schema, table_name
FROM potential_tables INNER JOIN allowed_cols_grouped USING (table_schema, table_name, columns)),

base_tables_reloptions AS (SELECT 
  pg_class.relname AS table_name, 
  pg_namespace.nspname AS table_schema,
unnest(reloptions) AS reloptions
FROM 
  pg_catalog.pg_class, 
  pg_catalog.pg_namespace
WHERE 
  pg_class.relnamespace = pg_namespace.oid AND relkind='r'),
  
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%'),

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'),
  
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 base_tables 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 base_tables 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 base_tables 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 base_tables 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 base_tables 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 base_tables 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, fillfactor 
FROM base_tables_fillfactor
WHERE fillfactor<>100
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 (table_schema, table_name) IN (
SELECT table_schema, table_name
FROM many_to_many_tables
) 
AND (table_schema, table_name) NOT IN (SELECT table_schema, table_name
FROM reasons)
ORDER BY fillfactor, table_schema, table_name;

Collections where the query belongs to

Collection nameCollection description
Find problems automaticallyQueries, that results point to problems in the database. Each query in the collection produces an initial assessment. However, a human reviewer has the final say as to whether there is a problem or not .

Categories where the query belongs to

Category nameCategory description
Data at the database physical levelQueries of this category provide information about the disk usage.
PerformanceQueries of this category provide information about indexes in a database.

The list of all the queries