编辑 ：因此，我在下面编写了查询，然后想到…“等等，Postgresql要求外键目标必须具有唯一索引。”
所以我想我误会了你的意思？您可以使用以下查询通过将“ conrelid”替换为“ confrelid”，将“ conkey”替换为“
confkey”来检查外键的 源 是否具有索引（是，是，查询中没有别名…）

好吧，我想应该可以遍历系统目录…像往常一样，对系统目录的最佳指南是使用psql并执行“ \ set ECHO_HIDDEN 1”，然后查看它为有趣的“
\”生成的SQL。 d”命令。这是用于查找表（“ \ d tablename”）的外键的SQL：

-- $1 is the table OID, e.g. 'tablename'::regclass
SELECT conname, conrelid::pg_catalog.regclass,
  pg_catalog.pg_get_constraintdef(c.oid, true) as condef
FROM pg_catalog.pg_constraint c
WHERE c.confrelid = $1 AND c.contype = 'f' ORDER BY 1;

似乎pg_constraint有列conkey，confkey并且看起来像是定义键所跨越的列号。可能confkey是外表中的列号，因为外键只为非空。另外，花了一段时间我才意识到这是SQL来显示
引用 给定表的外键。无论如何，这就是我们想要的。

因此，此查询显示数据开始成形：

select confrelid, conname, column_index, attname
from pg_attribute
     join (select confrelid::regclass, conname, unnest(confkey) as column_index
           from pg_constraint
           where confrelid = 'ticket_status'::regclass) fkey
          on fkey.confrelid = pg_attribute.attrelid
             and fkey.column_index = pg_attribute.attnum

我将使用8.4功能，例如unnest
…您也许可以相处得很好。

我最终得到了：

select pg_index.indexrelid::regclass, 'create index ' || relname || '_' ||
         array_to_string(column_name_list, '_') || '_idx on ' || confrelid ||
         ' (' || array_to_string(column_name_list, ',') || ')'
from (select distinct
       confrelid,
       array_agg(attname) column_name_list,
       array_agg(attnum) as column_list
     from pg_attribute
          join (select confrelid::regclass,
                 conname,
                 unnest(confkey) as column_index
                from (select distinct
                        confrelid, conname, confkey
                      from pg_constraint
                        join pg_class on pg_class.oid = pg_constraint.confrelid
                        join pg_namespace on pg_namespace.oid = pg_class.relnamespace
                      where nspname !~ '^pg_' and nspname <> 'information_schema'
                      ) fkey
               ) fkey
               on fkey.confrelid = pg_attribute.attrelid
                  and fkey.column_index = pg_attribute.attnum
     group by confrelid, conname
     ) candidate_index
join pg_class on pg_class.oid = candidate_index.confrelid
left join pg_index on pg_index.indrelid = confrelid
                      and indkey::text = array_to_string(column_list, ' ')

好的，此怪兽会打印出候​​选索引命令，并尝试将其与现有索引匹配。因此，您只需在末尾添加“ where
indexrelid为null”，即可获得创建似乎不存在的索引的命令。

该查询不能很好地处理多列外键；但是，恕我直言，如果您使用的是这些，那您应该得到麻烦。

LATER EDIT ：这是带有建议的编辑的查询，位于顶部。因此，这显示了在不作为外键（而不是目标）来源的列上创建不存在的索引的命令。

select pg_index.indexrelid::regclass, 'create index ' || relname || '_' ||
         array_to_string(column_name_list, '_') || '_idx on ' || conrelid ||
         ' (' || array_to_string(column_name_list, ',') || ')'
from (select distinct
       conrelid,
       array_agg(attname) column_name_list,
       array_agg(attnum) as column_list
     from pg_attribute
          join (select conrelid::regclass,
                 conname,
                 unnest(conkey) as column_index
                from (select distinct
                        conrelid, conname, conkey
                      from pg_constraint
                        join pg_class on pg_class.oid = pg_constraint.conrelid
                        join pg_namespace on pg_namespace.oid = pg_class.relnamespace
                      where nspname !~ '^pg_' and nspname <> 'information_schema'
                      ) fkey
               ) fkey
               on fkey.conrelid = pg_attribute.attrelid
                  and fkey.column_index = pg_attribute.attnum
     group by conrelid, conname
     ) candidate_index
join pg_class on pg_class.oid = candidate_index.conrelid
left join pg_index on pg_index.indrelid = conrelid
                      and indkey::text = array_to_string(column_list, ' ')
where indexrelid is null

我的经验是，这实际上并没有那么有用。它建议为诸如引用代码之类的东西创建索引，这些索引实际上不需要索引。