postgresql 列出一个PostgreSQL数据库的所有索引名、列名及其表名

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

提问by vchitta

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

获取 postgresql 数据库的所有索引名、列名和表名的查询是什么？

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_');

回答by Denis de Bernardy

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));

回答by Vladislav Rastrusny

More human friendly version of @Denis solution:

@Denis 解决方案的更人性化版本：

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

回答by Ramkrishnan

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;

回答by Lukas Eder

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

与其他答案相比，这是一个简化了事情的版本

• avoiding nested selects
• avoiding built-in functions (maybe hard to remember)
• using LATERALand UNNEST(...) WITH ORDINALITYfeatures available in later PostgreSQL versions (9.4+)

• 避免嵌套选择
• 避免内置函数（可能很难记住）
• 在更高版本的 PostgreSQL 版本 (9.4+) 中使用LATERAL和UNNEST(...) WITH ORDINALITY可用的功能

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

回答by Alphaaa

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

如果您也对索引大小感兴趣，您可以使用来自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;