Skip to content

Instantly share code, notes, and snippets.

@mmattozzi

mmattozzi/List postgres indexes and sizes

Forked from next2you/Postgres Index Usage.sql
Last active December 15, 2015 12:09
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 1 You must be signed in to fork a gist
  • Save mmattozzi/5258203 to your computer and use it in GitHub Desktop.
Save mmattozzi/5258203 to your computer and use it in GitHub Desktop.
Download ZIP
Displaying indexes in a postgres db
Raw
List postgres indexes and sizes
SELECT idx.relname as table,
idx.indexrelname as index,
pg_relation_size( idx.indexrelname::text ) 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 idx.indexrelname not ilike '%slony%'
AND idx.indexrelname not like 'sl\_%'
ORDER BY idx.relname, idx.indexrelname;
Raw
Postgres indexes and size without unique or primary indexes
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;
Raw
Postgres long running queries.sql
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