From: Melvin Davidson [mailto:melvin6925@yahoo.com] Sent: 15 August 2014 15:46 To: farjad.farid@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.nspname) || '.' || quote_ident(i.relname))) AS table_size, pg_size_pretty(pg_relation_size(quote_ident(n.nspname) || '.' || 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.