请尝试下面的查询以深入到所需的索引

查询如下-我亲自尝试过，并经常使用它。

SELECT n.nspname as "Schema",
  c.relname as "Name",
  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' 
THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as "Type",
  u.usename as "Owner",
 c2.relname as "Table"
FROM pg_catalog.pg_class c
     JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid
     JOIN pg_catalog.pg_class c2 ON i.indrelid = c2.oid
     LEFT JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('i','')
      AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
      AND pg_catalog.pg_table_is_visible(c.oid)
      AND c2.relname like '%agg_transaction%' --table name
      AND nspname = 'edjus' -- schema name 
ORDER BY 1,2;

创建一些测试数据…

create table test (a int, b int, c int, constraint pk_test primary key(a, b));
create table test2 (a int, b int, c int, constraint uk_test2 unique (b, c));
create table test3 (a int, b int, c int, constraint uk_test3b unique (b), constraint uk_test3c unique (c),constraint uk_test3ab unique (a, b));

列出索引的索引和列:

select
    t.relname as table_name,
    i.relname as index_name,
    a.attname as column_name
from
    pg_class t,
    pg_class i,
    pg_index ix,
    pg_attribute a
where
    t.oid = ix.indrelid
    and i.oid = ix.indexrelid
    and a.attrelid = t.oid
    and a.attnum = ANY(ix.indkey)
    and t.relkind = 'r'
    and t.relname like 'test%'
order by
    t.relname,
    i.relname;

 table_name | index_name | column_name
------------+------------+-------------
 test       | pk_test    | a
 test       | pk_test    | b
 test2      | uk_test2   | b
 test2      | uk_test2   | c
 test3      | uk_test3ab | a
 test3      | uk_test3ab | b
 test3      | uk_test3b  | b
 test3      | uk_test3c  | c

卷起列名:

select
    t.relname as table_name,
    i.relname as index_name,
    array_to_string(array_agg(a.attname), ', ') as column_names
from
    pg_class t,
    pg_class i,
    pg_index ix,
    pg_attribute a
where
    t.oid = ix.indrelid
    and i.oid = ix.indexrelid
    and a.attrelid = t.oid
    and a.attnum = ANY(ix.indkey)
    and t.relkind = 'r'
    and t.relname like 'test%'
group by
    t.relname,
    i.relname
order by
    t.relname,
    i.relname;

 table_name | index_name | column_names
------------+------------+--------------
 test       | pk_test    | a, b
 test2      | uk_test2   | b, c
 test3      | uk_test3ab | a, b
 test3      | uk_test3b  | b
 test3      | uk_test3c  | c

查询结果:

table |     column     |          type          | notnull |  index_name  | is_index | primarykey | uniquekey | default
-------+----------------+------------------------+---------+--------------+----------+-   -----------+-----------+---------
 nodes | dns_datacenter | character varying(255) | f       |              | f        | f          | f         |
 nodes | dns_name       | character varying(255) | f       | dns_name_idx | t        | f          | f         |
 nodes | id             | uuid                   | t       | nodes_pkey   | t        | t          | t         |
(3 rows)

查询:

SELECT  
c.relname AS table,
f.attname AS column,  
pg_catalog.format_type(f.atttypid,f.atttypmod) AS type,
f.attnotnull AS notnull,  
i.relname as index_name,
CASE  
    WHEN i.oid<>0 THEN 't'  
    ELSE 'f'  
END AS is_index,  
CASE  
    WHEN p.contype = 'p' THEN 't'  
    ELSE 'f'  
END AS primarykey,  
CASE  
    WHEN p.contype = 'u' THEN 't' 
    WHEN p.contype = 'p' THEN 't' 
    ELSE 'f'
END AS uniquekey,
CASE
    WHEN f.atthasdef = 't' THEN d.adsrc
END AS default  FROM pg_attribute f  
JOIN pg_class c ON c.oid = f.attrelid  
JOIN pg_type t ON t.oid = f.atttypid  
LEFT JOIN pg_attrdef d ON d.adrelid = c.oid AND d.adnum = f.attnum  
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace  
LEFT JOIN pg_constraint p ON p.conrelid = c.oid AND f.attnum = ANY (p.conkey)  
LEFT JOIN pg_class AS g ON p.confrelid = g.oid
LEFT JOIN pg_index AS ix ON f.attnum = ANY(ix.indkey) and c.oid = f.attrelid and c.oid = ix.indrelid 
LEFT JOIN pg_class AS i ON ix.indexrelid = i.oid 

WHERE c.relkind = 'r'::char  
AND n.nspname = 'public'  -- Replace with Schema name 
--AND c.relname = 'nodes'  -- Replace with table name, or Comment this for get all tables
AND f.attnum > 0
ORDER BY c.relname,f.attname;

原始信息在pg_index中。

@cope360的精彩回答，转换为使用连接语法。

select t.relname as table_name
     , i.relname as index_name
     , array_to_string(array_agg(a.attname), ', ') as column_names
from pg_class t
join pg_index ix
on t.oid = ix.indrelid
join pg_class i
on i.oid = ix.indexrelid
join pg_attribute a
on a.attrelid = t.oid
and a.attnum = ANY(ix.indkey)
where t.relkind = 'r'
and t.relname like 'test%'
group by t.relname
       , i.relname
order by t.relname
       , i.relname
;

\d tablename显示了8.3.8版本中的列名。

 "username_idx" UNIQUE, btree (username), tablespace "alldata1"