I would like editor access to the wiki, my username is skunkworker and I would like to modify the "Index/size usage statistics" query to better support multiple schemas that include the same structure.
Currently the query works as expected if there are not cloned schemas.
I am not sure if >= 8.1 is still applicable as I have not checked.
Below is my proposed query change:
SELECT
t.schemaname,
t.tablename,
c.reltuples::bigint AS num_rows,
pg_size_pretty(pg_relation_size(c.oid)) AS table_size,
pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size,
psai.indexrelname AS index_name,
CASE WHEN i.indisunique THEN 'Y' ELSE 'N' END AS "unique",
psai.idx_scan AS number_of_scans,
psai.idx_tup_read AS tuples_read,
psai.idx_tup_fetch AS tuples_fetched
FROM
pg_tables t
LEFT JOIN (select distinct relname, relnamespace, nspname, c.reltuples, c.oid from pg_class c LEFT JOIN pg_namespace n ON c.relnamespace = n.oid) c ON t.tablename = c.relname AND t.schemaname = c.nspname
LEFT JOIN pg_index i ON c.oid = i.indrelid
LEFT JOIN pg_stat_all_indexes psai ON i.indexrelid = psai.indexrelid
WHERE
t.schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY 1, 2;
This allows for a use case where you have the same index name in two different schemas, eg if a database host contains multiple schemas with the same structure.
Currently the query on the wiki page will show duplicates as is it joins against the tablename, instead of using the table oid.
John