问题描述
Postgres会自动将索引放在外键和主键上吗？我怎么能告诉？是否有一个命令将返回表上的所有索引？
Does Postgres automatically put indexes on Foreign Keys and Primary Keys? How can I tell? Is there a command that will return all indexes on a table?
推荐答案
PostgreSQL自动创建主键和唯一约束的索引，不在外键关系的引用侧。
PostgreSQL automatically creates indexes on primary keys and unique constraints, but not on the referencing side of foreign key relationships.
当Pg创建隐式索引时，它会发出一个 NOTICE $ c> psql 和/或系统日志，所以你可以看到它发生的时间。自动创建的索引在 \d 输出中也可见。
When Pg creates an implicit index it will emit a NOTICE-level message that you can see in psql and/or the system logs, so you can see when it happens. Automatically created indexes are visible in \d output for a table, too.
说：
和说：
因此，如果你想要外键，你必须自己创建索引。
Therefore you have to create indexes on foreign-keys yourself if you want them.
请注意，如果使用主外键，例如2个FK作为M到N表中的PK，您将在PK上有一个索引，并且可能不需要创建任何额外的索引。
Note that if you use primary-foreign-keys, like 2 FK's as a PK in a M-to-N table, you will have an index on the PK and probably don't need to create any extra indexes.
虽然通常最好在引用侧外键列上创建索引（或包含），但这不是必需的。您添加的每个索引会略微减少DML操作，因此您对每个 INSERT ， UPDATE 或 DELETE 。如果索引很少使用，它可能不值得拥有。
While it's usually a good idea to create an index on (or including) your referencing-side foreign key columns, it isn't required. Each index you add slows DML operations down slightly, so you pay a performance cost on every INSERT, UPDATE or DELETE. If the index is rarely used it may not be worth having.
这篇关于外键和主键上的Postgres和索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！