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

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

关于唯一索引的文档说:

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

约束条件的文档说:

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

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

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

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

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

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

关于唯一索引的文档说:

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

约束条件的文档说:

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

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

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

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

这个函数基于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)

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

\d <table_name>

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

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

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

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

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

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

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]可以方便地了解如何查询编目。