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

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

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

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

下面是一个bash脚本，它使用@sergeyB的SQL生成SQL，为外键上缺失的索引创建索引。

#!/bin/bash

read -r -d '' SQL <<EOM

WITH fk_actions ( code, action ) AS (
    VALUES ( 'a', 'error' ),
        ( 'r', 'restrict' ),
        ( 'c', 'cascade' ),
        ( 'n', 'set null' ),
        ( 'd', 'set default' )
),
fk_list AS (
    SELECT pg_constraint.oid as fkoid, conrelid, confrelid as parentid,
        conname, relname, nspname,
        fk_actions_update.action as update_action,
        fk_actions_delete.action as delete_action,
        conkey as key_cols
    FROM pg_constraint
        JOIN pg_class ON conrelid = pg_class.oid
        JOIN pg_namespace ON pg_class.relnamespace = pg_namespace.oid
        JOIN fk_actions AS fk_actions_update ON confupdtype = fk_actions_update.code
        JOIN fk_actions AS fk_actions_delete ON confdeltype = fk_actions_delete.code
    WHERE contype = 'f'
),
fk_attributes AS (
    SELECT fkoid, conrelid, attname, attnum
    FROM fk_list
        JOIN pg_attribute
            ON conrelid = attrelid
            AND attnum = ANY( key_cols )
    ORDER BY fkoid, attnum
),
fk_cols_list AS (
    SELECT fkoid, array_to_string(array_agg(attname), ':') as cols_list
    FROM fk_attributes
    GROUP BY fkoid
),
index_list AS (
    SELECT indexrelid as indexid,
        pg_class.relname as indexname,
        indrelid,
        indkey,
        indpred is not null as has_predicate,
        pg_get_indexdef(indexrelid) as indexdef
    FROM pg_index
        JOIN pg_class ON indexrelid = pg_class.oid
    WHERE indisvalid
),
fk_index_match AS (
    SELECT fk_list.*,
        indexid,
        indexname,
        indkey::int[] as indexatts,
        has_predicate,
        indexdef,
        array_length(key_cols, 1) as fk_colcount,
        array_length(indkey,1) as index_colcount,
        round(pg_relation_size(conrelid)/(1024^2)::numeric) as table_mb,
        cols_list
    FROM fk_list
        JOIN fk_cols_list USING (fkoid)
        LEFT OUTER JOIN index_list
            ON conrelid = indrelid
            AND (indkey::int2[])[0:(array_length(key_cols,1) -1)] @> key_cols

),
fk_perfect_match AS (
    SELECT fkoid
    FROM fk_index_match
    WHERE (index_colcount - 1) <= fk_colcount
        AND NOT has_predicate
        AND indexdef LIKE '%USING btree%'
),
fk_index_check AS (
    SELECT 'no index' as issue, *, 1 as issue_sort
    FROM fk_index_match
    WHERE indexid IS NULL
    UNION ALL
    SELECT 'questionable index' as issue, *, 2
    FROM fk_index_match
    WHERE indexid IS NOT NULL
        AND fkoid NOT IN (
            SELECT fkoid
            FROM fk_perfect_match)
),
parent_table_stats AS (
    SELECT fkoid, tabstats.relname as parent_name,
        (n_tup_ins + n_tup_upd + n_tup_del + n_tup_hot_upd) as parent_writes,
        round(pg_relation_size(parentid)/(1024^2)::numeric) as parent_mb
    FROM pg_stat_user_tables AS tabstats
        JOIN fk_list
            ON relid = parentid
),
fk_table_stats AS (
    SELECT fkoid,
        (n_tup_ins + n_tup_upd + n_tup_del + n_tup_hot_upd) as writes,
        seq_scan as table_scans
    FROM pg_stat_user_tables AS tabstats
        JOIN fk_list
            ON relid = conrelid
)
SELECT relname as table_name,
    cols_list
FROM fk_index_check
    JOIN parent_table_stats USING (fkoid)
    JOIN fk_table_stats USING (fkoid)
ORDER BY issue_sort, table_mb DESC, table_name;
EOM

DB_NAME="dbname"
DB_USER="dbuser"
DB_PASSWORD="dbpass"
DB_HOSTNAME="hostname"
DB_PORT=5432

export PGPASSWORD="$DB_PASSWORD"
psql -h $DB_HOSTNAME -p $DB_PORT -U $DB_USER -d $DB_NAME -t -A -F"," -c "$SQL" | while read -r line; do
  IFS=','
  parts=($line)
  unset IFS
  tableName=${parts[0]}
  colsList=${parts[1]}

  indexName="${tableName}_${colsList//:/_}_index"
  printf -- "\n--Index: %s\nDROP INDEX IF EXISTS %s;\n
CREATE INDEX %s\n\t\tON %s USING btree\n\t(%s);
  " "$indexName" "$indexName" "$indexName" "$tableName" "$colsList"
done

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

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

关于唯一索引的文档说:

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

约束条件的文档说:

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

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

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

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

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

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

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

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

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

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

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

\d <table_name>

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