Purpose of Query
This query can be relevant when you have performance issues while reading data from the schema.
This query will retrieve the size of the index and table associated with the schema.
Applicable CAST Version
Release | Yes/No |
|---|---|
| 8.3.x |  |
| 8.2.x | |
Applicable RDBMS
RDBMS | Yes/No |
|---|---|
| CSS | |
Query for CSS
select s.nspname schema_name,
t.relname table_name,
i.indexrelname,
pg_size_pretty(pg_table_size(t.oid)) table_size,
pg_size_pretty(pg_relation_size(i.index_oid)) index_size
from pg_catalog.pg_class t
join pg_catalog.pg_namespace s
on t.relnamespace = s.oid
left join (select ipg.oid index_oid, c.oid table_oid, ipg.relname indexrelname
from pg_index x
join pg_class c
on c.oid = x.indrelid
join pg_class ipg
on ipg.oid = x.indexrelid
) i
on i.table_oid = t.oid
where t.relkind in ('v', 'r', 'p')
and s.nspname in (<schema list>)
order by s.nspname, pg_relation_size(t.oid) desc;
Query result example
Query result interpretation
Every index associated to the schema is displayed along with index size and table size
Notes/comments
Related Pages
