如果你想列出程序模式中所有表的索引，所有信息都在目录中:

select
     n.nspname  as "Schema"
    ,t.relname  as "Table"
    ,c.relname  as "Index"
from
          pg_catalog.pg_class c
     join pg_catalog.pg_namespace n on n.oid        = c.relnamespace
     join pg_catalog.pg_index i     on i.indexrelid = c.oid
     join pg_catalog.pg_class t     on i.indrelid   = t.oid
where
        c.relkind = 'i'
    and n.nspname not in ('pg_catalog', 'pg_toast')
    and pg_catalog.pg_table_is_visible(c.oid)
order by
     n.nspname
    ,t.relname
    ,c.relname

如果想深入研究(如列和排序)，需要查看pg_catalog.pg_index。使用psql -E [dbname]可以方便地了解如何查询编目。

PostgreSQL自动在主键和唯一约束上创建索引，但不会在外键关系的引用端创建索引。

当Pg创建一个隐式索引时，它将发出一个notice级别的消息，您可以在psql和/或系统日志中看到该消息，因此您可以看到它何时发生。自动创建的索引在表的\d输出中也是可见的。

关于唯一索引的文档说:

PostgreSQL自动为每个唯一的约束和主键约束创建索引，以加强唯一性。因此，没有必要显式地为主键列创建索引。

约束条件的文档说:

从引用表中的一行的DELETE或引用表中的UPDATE开始 所引用的列需要对引用表进行扫描 与旧值匹配的行，通常是一个好主意 引用列。因为这并不总是需要的，而且有 关于如何索引、声明外键有许多选择 约束不会自动在引用上创建索引 列。

因此，如果需要外键，就必须自己创建索引。

注意，如果使用主外键，比如在M-to-N表中使用2个FK作为PK，您将在PK上有一个索引，可能不需要创建任何额外的索引。

虽然在引用端外键列上创建索引(或包含)通常是个好主意，但这并不是必需的。您添加的每个索引都会略微降低DML操作的速度，因此在每次INSERT、UPDATE或DELETE上都要付出性能代价。如果索引很少被使用，那么它可能就不值得拥有。

对于PRIMARY KEY，索引将被创建，并带有以下消息:

NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "index" for table "table" 

对于一个FOREIGN KEY，如果引用的表上没有索引，约束将不会被创建。

引用表上的索引不是必需的(尽管是需要的)，因此不会隐式创建。

这个函数基于Laurenz Albe在https://www.cybertec-postgresql.com/en/index-your-foreign-key/上的工作，列出所有缺少索引的外键。表的大小如图所示，对于小表，扫描性能可能优于索引表。

--
-- function:    missing_fk_indexes
-- purpose:     List all foreing keys in the database without and index in the referencing table.
-- author:      Based on the work of Laurenz Albe
-- see:         https://www.cybertec-postgresql.com/en/index-your-foreign-key/
--
create or replace function missing_fk_indexes () 
returns table (
  referencing_table regclass,
  fk_columns        varchar,
  table_size        varchar,
  fk_constraint     name,
  referenced_table  regclass
)
language sql as $$
  select
    -- referencing table having ta foreign key declaration
    tc.conrelid::regclass as referencing_table,
    
    -- ordered list of foreign key columns
    string_agg(ta.attname, ', ' order by tx.n) as fk_columns,
    
    -- referencing table size
    pg_catalog.pg_size_pretty (
      pg_catalog.pg_relation_size(tc.conrelid)
    ) as table_size,
    
    -- name of the foreign key constraint
    tc.conname as fk_constraint,
    
    -- name of the target or destination table
    tc.confrelid::regclass as referenced_table
    
  from pg_catalog.pg_constraint tc
  
  -- enumerated key column numbers per foreign key
  cross join lateral unnest(tc.conkey) with ordinality as tx(attnum, n)
  
  -- name for each key column
  join pg_catalog.pg_attribute ta on ta.attnum = tx.attnum and ta.attrelid = tc.conrelid
  
  where not exists (
    -- is there ta matching index for the constraint?
    select 1 from pg_catalog.pg_index i
    where 
      i.indrelid = tc.conrelid and 
      -- the first index columns must be the same as the key columns, but order doesn't matter
      (i.indkey::smallint[])[0:cardinality(tc.conkey)-1] @> tc.conkey) and 
      tc.contype = 'f'
    group by 
      tc.conrelid, 
      tc.conname, 
      tc.confrelid
    order by 
      pg_catalog.pg_relation_size(tc.conrelid) desc
$$;

这样测试，

select * from missing_fk_indexes();

你会看到一个这样的列表。

   referencing_table    |    fk_columns    | table_size |                fk_constraint                 | referenced_table 
------------------------+------------------+------------+----------------------------------------------+------------------
 stk_warehouse          | supplier_id      | 8192 bytes | stk_warehouse_supplier_id_fkey               | stk_supplier
 stk_reference          | supplier_id      | 0 bytes    | stk_reference_supplier_id_fkey               | stk_supplier
 stk_part_reference     | reference_id     | 0 bytes    | stk_part_reference_reference_id_fkey         | stk_reference
 stk_warehouse_part     | part_id          | 0 bytes    | stk_warehouse_part_part_id_fkey              | stk_part
 stk_warehouse_part_log | dst_warehouse_id | 0 bytes    | stk_warehouse_part_log_dst_warehouse_id_fkey | stk_warehouse
 stk_warehouse_part_log | part_id          | 0 bytes    | stk_warehouse_part_log_part_id_fkey          | stk_part
 stk_warehouse_part_log | src_warehouse_id | 0 bytes    | stk_warehouse_part_log_src_warehouse_id_fkey | stk_warehouse
 stk_product_part       | part_id          | 0 bytes    | stk_product_part_part_id_fkey                | stk_part
 stk_purchase           | parent_id        | 0 bytes    | stk_purchase_parent_id_fkey                  | stk_purchase
 stk_purchase           | supplier_id      | 0 bytes    | stk_purchase_supplier_id_fkey                | stk_supplier
 stk_purchase_line      | reference_id     | 0 bytes    | stk_purchase_line_reference_id_fkey          | stk_reference
 stk_order              | freighter_id     | 0 bytes    | stk_order_freighter_id_fkey                  | stk_freighter
 stk_order_line         | product_id       | 0 bytes    | stk_order_line_product_id_fkey               | cnt_product
 stk_order_fulfillment  | freighter_id     | 0 bytes    | stk_order_fulfillment_freighter_id_fkey      | stk_freighter
 stk_part               | sibling_id       | 0 bytes    | stk_part_sibling_id_fkey                     | stk_part
 stk_order_part         | part_id          | 0 bytes    | stk_order_part_part_id_fkey                  | stk_part

对于那些决定在每个引用列上系统地创建和索引的人来说，这个版本可能更有效:

--
-- function:    missing_fk_indexes2
-- purpose:     List all foreing keys in the database without and index in the referencing table.
--              The listing contains create index sentences
-- author:      Based on the work of Laurenz Albe
-- see:         https://www.cybertec-postgresql.com/en/index-your-foreign-key/
--
create or replace function missing_fk_indexes2 () 
returns setof varchar
language sql as $$
  select
    -- create index sentence
    'create index on ' || 
    tc.conrelid::regclass || 
    '(' || 
    string_agg(ta.attname, ', ' order by tx.n) || 
    ')' as create_index
        
  from pg_catalog.pg_constraint tc
  
  -- enumerated key column numbers per foreign key
  cross join lateral unnest(tc.conkey) with ordinality as tx(attnum, n)
  
  -- name for each key column
  join pg_catalog.pg_attribute ta on ta.attnum = tx.attnum and ta.attrelid = tc.conrelid
  
  where not exists (
    -- is there ta matching index for the constraint?
    select 1 from pg_catalog.pg_index i
    where 
      i.indrelid = tc.conrelid and 
      -- the first index columns must be the same as the key columns, but order doesn't matter
      (i.indkey::smallint[])[0:cardinality(tc.conkey)-1] @> tc.conkey) and 
      tc.contype = 'f'
    group by 
      tc.conrelid, 
      tc.conname, 
      tc.confrelid
    order by 
      pg_catalog.pg_relation_size(tc.conrelid) desc
$$;

现在输出的是必须添加到数据库中的创建索引语句。

select * from missing_fk_indexes2();

                   missing_fk_indexes2                    
----------------------------------------------------------
 create index on stk_warehouse(supplier_id)
 create index on stk_reference(supplier_id)
 create index on stk_part_reference(reference_id)
 create index on stk_warehouse_part(part_id)
 create index on stk_warehouse_part_log(dst_warehouse_id)
 create index on stk_warehouse_part_log(part_id)
 create index on stk_warehouse_part_log(src_warehouse_id)
 create index on stk_product_part(part_id)
 create index on stk_purchase(parent_id)
 create index on stk_purchase(supplier_id)
 create index on stk_purchase_line(reference_id)
 create index on stk_order(freighter_id)
 create index on stk_order_line(product_id)
 create index on stk_order_fulfillment(freighter_id)
 create index on stk_part(sibling_id)
 create index on stk_order_part(part_id)

我很喜欢EclipseLink 2.5的酷炫性能特性这篇文章中对此的解释

Indexing Foreign Keys The first feature is auto indexing of foreign keys. Most people incorrectly assume that databases index foreign keys by default. Well, they don't. Primary keys are auto indexed, but foreign keys are not. This means any query based on the foreign key will be doing full table scans. This is any OneToMany, ManyToMany or ElementCollection relationship, as well as many OneToOne relationships, and most queries on any relationship involving joins or object comparisons. This can be a major perform issue, and you should always index your foreign keys fields.

是-主键，否-外键(更多在文档中)。

\d <table_name>

在“psql”中显示了一个表的描述，包括它的所有索引。