Re: list of index

Thanks Melvin, That worked for me. Great.

From: Melvin Davidson
[mailto:melvin6925(at)yahoo(dot)com]
Sent: 15 August 2014 15:46
To: farjad(dot)farid(at)checknetworks(dot)com;
pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] list of index

>On a Postgresql database i like to obtain (using
an sql

>statement) the list of all user defined indexes
and their

>details specially the column "order by" sort
order. e.g.

>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