The list of all the queries

Logical size of a schema

Query goal: The schema size is the sum of the sizes of all tables in the schema.
Query type: Sofware measure (Numeric values (software measures) about the database)
Query license: MIT License
Data source: INFORMATION_SCHEMA+system catalog
SQL query: Click on query to copy it

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

Collection nameCollection description
Find quick numeric overview of the databaseQueries that return numeric values showing mostly the number of different types of database objects in the database

Categories where the query belongs to

Category nameCategory description
Comfortability of database evolutionQueries of this category provide information about the means that influence database evolution.

Reference materials for further reading

Reference
Piattini, M., Calero, C., Sahraoui, H. A., & Lounis, H. (2001). Object-relational database metrics. L'Objet, 7(4), 477-496.

The list of all the queries