Thanks Melvin, That worked for me. Great.
From: Melvin Davidson
[mailto:***@yahoo.com]
Sent: 15 August 2014 15:46
To: ***@checknetworks.com;
pgsql-***@postgresql.org
Subject: Re: [GENERAL] list of index
Post by FarjadFarid(ChkNet)On a Postgresql database i like to obtain (using
an sql
Post by FarjadFarid(ChkNet)statement) the list of all user defined indexes
and their
Post by FarjadFarid(ChkNet)details specially the column "order by" sort
order. e.g.
Post by FarjadFarid(ChkNet)ASC or DESC. Any help would be much appreciated.
either of the following queries should help:
SELECT pg_get_indexdef(idx.indexrelid) || ';'
FROM pg_stat_all_indexes i
JOIN pg_class c ON (c.oid = i.relid)
JOIN pg_namespace n ON (n.oid = c.relnamespace)
JOIN pg_index idx ON (idx.indexrelid =
i.indexrelid )
WHERE NOT idx.indisprimary
AND NOT idx.indisunique
AND i.relname NOT LIKE 'pg_%'
AND i.idx_scan = 0
ORDER BY n.nspname,
i.relname;
SELECT n.nspname as schema,
i.relname as table,
i.indexrelname as index,
i.idx_scan,
i.idx_tup_read,
i.idx_tup_fetch,
pg_size_pretty(pg_relation_size(quote_ident(n.nspn
ame) || '.' || quote_ident(i.relname))) AS
table_size,
pg_size_pretty(pg_relation_size(quote_ident(n.nspn
ame) || '.' || quote_ident(i.indexrelname))) AS
index_size,
pg_get_indexdef(idx.indexrelid) as
idx_definition
FROM pg_stat_all_indexes i
JOIN pg_class c ON (c.oid = i.relid)
JOIN pg_namespace n ON (n.oid = c.relnamespace)
JOIN pg_index idx ON (idx.indexrelid =
i.indexrelid )
WHERE n.nspname NOT LIKE 'pg_%'
ORDER BY 1, 2, 3;
Melvin Davidson
Cell 720-320-0155
I reserve the right to fantasize. Whether or not
you
wish to share my fantasy is entirely up to you.
<http://us.i1.yimg.com/us.yimg.com/i/mesg/tsmileys
2/01.gif>
www.youtube.com/unusedhero
Folk Alley - All Folk - 24 Hours a day
www.folkalley.com
