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

Question

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

I tried to get a list of all indexes in db using this query, but how to get a 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_');` 

Answer 1

This will output all indexes with details (extracted from my 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; 

Additionally, add an extra join at the end to trim 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)); 

Answer 2

A 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 

Answer 3

Query to list all database indexes

 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; 

Answer 4

If you are also interested in the size of the index , you can 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; 

Answer 5

Here is a version that simplifies things compared to other answers

• avoid nested selections
• avoid built-in functions (maybe hard to remember)
• using awesome LATERAL and UNNEST(...) WITH ORDINALITY functions available in later versions of PostgreSQL (9.4 +)

 SELECT tnsp.nspname AS schema_name, trel.relname AS table_name, irel.relname AS index_name, array_agg (a.attname 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) JOIN pg_attribute AS a ON trel.oid = a.attrelid AND a.attnum = c.colnum GROUP BY tnsp.nspname, trel.relname, irel.relname