Skip to content

Instantly share code, notes, and snippets.

@next2you
Forked from sriedel/Postgres Index Usage
Created October 15, 2010 20:12
  • Star 13 You must be signed in to star a gist
  • Fork 4 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save next2you/628866 to your computer and use it in GitHub Desktop.
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;
@bjeanes
Copy link

bjeanes commented Aug 11, 2016

I had to replace pg_relation_size( idx.indexrelname::text )/1024/1024 as bytes with pg_relation_size( idx.indexrelid )/1024/1024 as bytes because I had indexes in multiple namespaces and pg_relation_size was erroring trying to report on an index in a different schema.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment