Postgres: Determine table/index size
SELECT idx.relname as table, | |
idx.indexrelname as index, | |
pg_relation_size( idx.indexrelname::text )/1024/1024 as bytes, | |
cls.relpages as pages, | |
cls.reltuples as tuples, | |
idx.idx_scan as scanned, | |
idx.idx_tup_read as read, | |
idx.idx_tup_fetch as fetched | |
FROM pg_stat_user_indexes idx, | |
pg_class cls , | |
pg_index | |
WHERE cls.relname = idx.relname | |
AND idx.indexrelid = pg_index.indexrelid | |
AND pg_index.indisunique is not true | |
AND pg_index.indisprimary is not true | |
AND idx.indexrelname not ilike '%slony%' | |
AND idx.indexrelname not like 'sl\_%' | |
ORDER BY idx.relname, | |
idx.indexrelname; |
select procpid, | |
query_start, | |
client_addr, | |
client_port, | |
current_query | |
from pg_stat_activity | |
where current_query not like '%IDLE%' | |
and current_query not like '%autova%' | |
and query_start < current_timestamp - interval '5 minutes' | |
order by query_start asc; | |
select procpid | |
from pg_stat_activity | |
where current_query not like '%IDLE%' | |
and current_query not like '%autova%' | |
and query_start < current_timestamp - interval '5 minutes' | |
order by query_start asc; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
This comment has been minimized.
I had to replace
pg_relation_size( idx.indexrelname::text )/1024/1024 as bytes
withpg_relation_size( idx.indexrelid )/1024/1024 as bytes
because I had indexes in multiple namespaces andpg_relation_size
was erroring trying to report on an index in a different schema.