WITH materialized_views AS (SELECT nspname, relname, 'MATERIALIZED VIEW' AS table_type, 1 AS weight
FROM pg_class INNER JOIN pg_namespace ON pg_class.relnamespace=pg_namespace.oid
INNER JOIN pg_attribute ON pg_class.oid=pg_attribute.attrelid INNER JOIN pg_type ON
pg_attribute.atttypid=pg_type.oid
WHERE relkind = 'm' AND attnum>=1 AND typrelid=0
UNION SELECT nspname, relname, 'MATERIALIZED VIEW' AS table_type,
(SELECT Count(*) AS cnt FROM pg_class AS c INNER JOIN pg_attribute AS a ON c.oid=a.attrelid
WHERE c.oid=pg_type.typrelid) AS weight
FROM pg_class INNER JOIN pg_namespace ON pg_class.relnamespace=pg_namespace.oid
INNER JOIN pg_attribute ON pg_class.oid=pg_attribute.attrelid INNER JOIN pg_type ON
pg_attribute.atttypid=pg_type.oid
WHERE relkind = 'm' AND attnum>=1 AND typrelid<>0),
materialized_views_summary AS (SELECT nspname, Sum(weight) AS ts
FROM materialized_views
GROUP BY nspname),
other_tables_summary AS (SELECT A.table_schema,
SUM (CASE
WHEN A.data_type = 'USER-DEFINED'
AND B.relnatts>=0
THEN B.relnatts
ELSE 1
END) AS ts
FROM information_schema.columns A
LEFT JOIN (
SELECT n.nspname, c.relname, c.relnatts
FROM pg_class c
INNER JOIN pg_catalog.pg_authid u
ON u.oid = c.relowner
INNER JOIN pg_catalog.pg_namespace n
ON n.oid = c.relnamespace
WHERE c.relkind IN ('c','r')
AND (n.nspname = 'public'
OR u.rolname <> 'postgres')
) B
ON A.udt_name = B.relname
AND A.udt_schema = B.nspname
AND A.data_type = 'USER-DEFINED'
INNER JOIN information_schema.tables T
ON A.table_schema = T.table_schema
AND A.table_name = T.table_name
INNER JOIN information_schema.schemata S
ON A.table_schema=S.schema_name
WHERE (A.table_schema = 'public'
OR S.schema_owner<>'postgres')
GROUP BY A.table_schema)
SELECT table_schema, Sum(ts) AS ts
FROM (SELECT table_schema, ts
FROM other_tables_summary
UNION SELECT nspname, ts
FROM materialized_views_summary) AS foo
GROUP BY table_schema
ORDER BY ts DESC, table_schema;
Collections
This query belongs to the following collections:
Name
Description
Find quick numeric overview of the database
Queries that return numeric values showing mostly the number of different types of database objects in the database
Categories
This query is classified under the following categories:
Name
Description
Comfortability of database evolution
Queries of this category provide information about the means that influence database evolution.
Further reading and related materials:
Reference
Piattini, M., Calero, C., Sahraoui, H. A., & Lounis, H. (2001). Object-relational database metrics. L'Objet, 7(4), 477-496.