Thanks Melvin, That worked for me. Great.
From: Melvin Davidson [mailto:melvin6...@yahoo.com] Sent: 15 August 2014 15:46 To: farjad.fa...@checknetworks.com; pgsql-general@postgresql.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
