List all index names, column names and its table name of a PostgreSQL database

Question

What is the query to get the list all index names, its column name and its table name of a postgresql database?

I have tried to get the list of all indexes in a db by using this query but how to get the list of indexes, its column names and its table names?

 SELECT *
 FROM pg_class, pg_index
 WHERE pg_class.oid = pg_index.indexrelid
 AND pg_class.oid IN (
     SELECT indexrelid
     FROM pg_index, pg_class
     WHERE pg_class.oid=pg_index.indrelid
     AND indisunique != 't'
     AND indisprimary != 't'
     AND relname !~ '^pg_');

Denis de Bernardy · Accepted Answer · 2011-07-21T14:50:22.843

This will output all indexes with details (extracted from my view definitions):

SELECT i.relname as indname,
       i.relowner as indowner,
       idx.indrelid::regclass,
       am.amname as indam,
       idx.indkey,
       ARRAY(
       SELECT pg_get_indexdef(idx.indexrelid, k + 1, true)
       FROM generate_subscripts(idx.indkey, 1) as k
       ORDER BY k
       ) as indkey_names,
       idx.indexprs IS NOT NULL as indexprs,
       idx.indpred IS NOT NULL as indpred
FROM   pg_index as idx
JOIN   pg_class as i
ON     i.oid = idx.indexrelid
JOIN   pg_am as am
ON     i.relam = am.oid;

Optionally add an extra join to the end so as to trim the namespaces:

SELECT i.relname as indname,
       i.relowner as indowner,
       idx.indrelid::regclass,
       am.amname as indam,
       idx.indkey,
       ARRAY(
       SELECT pg_get_indexdef(idx.indexrelid, k + 1, true)
       FROM generate_subscripts(idx.indkey, 1) as k
       ORDER BY k
       ) as indkey_names,
       idx.indexprs IS NOT NULL as indexprs,
       idx.indpred IS NOT NULL as indpred
FROM   pg_index as idx
JOIN   pg_class as i
ON     i.oid = idx.indexrelid
JOIN   pg_am as am
ON     i.relam = am.oid
JOIN   pg_namespace as ns
ON     ns.oid = i.relnamespace
AND    ns.nspname = ANY(current_schemas(false));

great its working; for me i need only user defined indexes so i have another condition `WHERE i.relname !~ '^(pg_|sql_)'` — vchitta, Jul 21 '11 at 15:14
You probably don't need that condition if you're using the second query I posted. It strips out anything that doesn't live in your path (minus the system schemas). — Denis de Bernardy, Jul 21 '11 at 15:23

score 54 · Answer 2 · edited Jun 16 '15 at 22:08

More human friendly version of @Denis solution:

SELECT
  U.usename                AS user_name,
  ns.nspname               AS schema_name,
  idx.indrelid :: REGCLASS AS table_name,
  i.relname                AS index_name,
  idx.indisunique          AS is_unique,
  idx.indisprimary         AS is_primary,
  am.amname                AS index_type,
  idx.indkey,
       ARRAY(
           SELECT pg_get_indexdef(idx.indexrelid, k + 1, TRUE)
           FROM
             generate_subscripts(idx.indkey, 1) AS k
           ORDER BY k
       ) AS index_keys,
  (idx.indexprs IS NOT NULL) OR (idx.indkey::int[] @> array[0]) AS is_functional,
  idx.indpred IS NOT NULL AS is_partial
FROM pg_index AS idx
  JOIN pg_class AS i
    ON i.oid = idx.indexrelid
  JOIN pg_am AS am
    ON i.relam = am.oid
  JOIN pg_namespace AS NS ON i.relnamespace = NS.OID
  JOIN pg_user AS U ON i.relowner = U.usesysid
WHERE NOT nspname LIKE 'pg%'; -- Excluding system tables

The solution is nice. The problem is: in 'index_keys' there are not showing the ' DESC' when the index is descending. Is anyone know, where can I found that bit what shows the descending of a field? — Jettero, Mar 31 '18 at 19:45

score 7 · Answer 3 · edited Jun 16 '15 at 22:03

7

The Query to list all the indexes of a database

SELECT
  tablename,
  indexes [1],
  indexes [2],
  indexes [3],
  indexes [4],
  indexes [5],
  indexes [6],
  indexes [7],
  indexes [8],
  indexes [9],
  indexes [10]
FROM (SELECT
  tablename,
  array_agg(indexname) AS indexes
FROM pg_indexes
WHERE schemaname = 'public'
GROUP BY tablename) as sub;

edited Jun 16 '15 at 22:03

Asclepius

57,944
17
167
143

answered Feb 26 '15 at 12:38

Ramkrishnan

71
1
1

5

This is so much simpler. My variation: `SELECT schemaname as schema, tablename as table, indexname as index FROM pg_indexes WHERE schemaname='myschema';` – Chris Jul 16 '15 at 17:46
2

What about tables having unknown (probably >10) number of indexes? – Vladislav Rastrusny Feb 17 '16 at 13:44

Lukas Eder · Answer 4 · 2018-04-02T10:50:01.683

6

Here's a version that simplifies things compared to other answers by

avoiding nested selects
avoiding built-in functions (maybe hard to remember)
using LATERAL and UNNEST(...) WITH ORDINALITY features available in later PostgreSQL versions (9.4+)

SELECT
  tnsp.nspname AS schema_name,
  trel.relname AS table_name,
  irel.relname AS index_name,
  array_agg (
     a.attname 
  || ' ' || CASE o.option & 1 WHEN 1 THEN 'DESC' ELSE 'ASC' END
  || ' ' || CASE o.option & 2 WHEN 2 THEN 'NULLS FIRST' ELSE 'NULLS LAST' END
    ORDER BY c.ordinality
  ) AS columns
FROM pg_index AS i
JOIN pg_class AS trel ON trel.oid = i.indrelid
JOIN pg_namespace AS tnsp ON trel.relnamespace = tnsp.oid
JOIN pg_class AS irel ON irel.oid = i.indexrelid
CROSS JOIN LATERAL unnest (i.indkey) WITH ORDINALITY AS c (colnum, ordinality)
LEFT JOIN LATERAL unnest (i.indoption) WITH ORDINALITY AS o (option, ordinality)
  ON c.ordinality = o.ordinality
JOIN pg_attribute AS a ON trel.oid = a.attrelid AND a.attnum = c.colnum
GROUP BY tnsp.nspname, trel.relname, irel.relname

edited Apr 02 '18 at 10:50

answered Jun 09 '17 at 14:23

Lukas Eder

211,314
129
689
1,509

This solution is also nice. The problem is: in 'columns' there are not showing the ' DESC' when the index is descending. Is anyone know, where can I find that bit what shows the descending of a field? – Jettero Mar 31 '18 at 19:49
1

@Jettero: Updated the query to display also `ASC` / `DESC` and `NULLS FIRST` / `NULLS LAST` – Lukas Eder Apr 02 '18 at 10:50
I did put two fields, to have full information of indexes: ", i.indisunique, i.indisprimary," – Jettero Apr 06 '18 at 20:37
this doesn't include expressions in columns – Michael Baldry Jun 03 '20 at 15:53

score 4 · Answer 5 · answered Apr 28 '16 at 13:20

If you are also interested in index size, you may use this query from the PostgreSQL Wiki.

SELECT
    t.tablename,
    indexname,
    c.reltuples AS num_rows,
    pg_size_pretty(pg_relation_size(quote_ident(t.tablename)::text)) AS table_size,
    pg_size_pretty(pg_relation_size(quote_ident(indexrelname)::text)) AS index_size,
    CASE WHEN indisunique THEN 'Y'
       ELSE 'N'
    END AS UNIQUE,
    idx_scan AS number_of_scans,
    idx_tup_read AS tuples_read,
    idx_tup_fetch AS tuples_fetched
FROM pg_tables t
LEFT OUTER JOIN pg_class c ON t.tablename=c.relname
LEFT OUTER JOIN
    ( SELECT c.relname AS ctablename, ipg.relname AS indexname, x.indnatts AS number_of_columns, idx_scan, idx_tup_read, idx_tup_fetch, indexrelname, indisunique FROM pg_index x
           JOIN pg_class c ON c.oid = x.indrelid
           JOIN pg_class ipg ON ipg.oid = x.indexrelid
           JOIN pg_stat_all_indexes psai ON x.indexrelid = psai.indexrelid )
    AS foo
    ON t.tablename = foo.ctablename
WHERE t.schemaname='public'
ORDER BY 1,2;

score 1 · Answer 6 · edited Nov 20 '20 at 13:11

For Non-Composite Indexes

select  t.relname,i.relname ,
     STRING_AGG(pga.attname||'', ','order by i.relname,pga.attnum)   as columnName          
    from pg_class t inner join pg_index ix
              on t.oid = ix.indrelid
              inner join  pg_class i
              on i.oid = ix.indexrelid
               inner join  pg_attribute pga
              on
               pga.attrelid = i.oid 
            inner join pg_indexes pgidx
                on pgidx.indexname=i.relname
             where 
               t.relkind = 'r' 
            and pgidx.schemaname='asit_cm'
             and t.relname ='accessory'
             group by  t.relname,i.relname having count(*)=1

For Composite Indexes

select  t.relname,i.relname ,
     STRING_AGG(pga.attname||'', ','order by i.relname,pga.attnum)   as columnName          
    from pg_class t inner join pg_index ix
              on t.oid = ix.indrelid
              inner join  pg_class i
              on i.oid = ix.indexrelid
               inner join  pg_attribute pga
              on
               pga.attrelid = i.oid 
            inner join pg_indexes pgidx
                on pgidx.indexname=i.relname
             where 
               t.relkind = 'r' 
            and pgidx.schemaname='asit_cm'
             and t.relname ='accessory'
             group by  t.relname,i.relname having count(*)=1

List all index names, column names and its table name of a PostgreSQL database

6 Answers6

Linked

Related