Find base tables that have more than five indexes. Indexes can be used to increase the speed of queries (SELECT statements). However, the amount of indexes shouldn't be too large. Otherwise it may reduce the speed of operations that are used to modify data.
Type
Problem detection (Each row in the result could represent a flaw in the design)
SELECT n.nspname AS schema_name,
c2.relname AS table_name,
Count(*) AS number_of_indexes,
Count(*) FILTER (WHERE i.indisunique=true) AS number_of_unique_indexes
FROM pg_catalog.pg_class c INNER JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid
INNER JOIN pg_catalog.pg_class c2 ON i.indrelid = c2.oid
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 ('i') AND c2.relkind='r'
AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
AND (n.nspname = 'public' OR u.rolname <> 'postgres')
GROUP BY n.nspname, c2.relname
HAVING Count(*)>5
ORDER BY Count(*) DESC, n.nspname, c2.relname;
Categories
This query is classified under the following categories:
Name
Description
Performance
Queries of this category provide information about indexes in a database.