Re: list of index

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.

www.youtube.com/unusedhero

Folk Alley - All Folk - 24 Hours a day
www.folkalley.com