外键和主键上的Postgres和索引 [英] Postgres and Indexes on Foreign Keys and Primary Keys

问题描述

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.

关于唯一索引的文档说：

PostgreSQL自动为每个唯一约束和主键约束创建一个索引以强制唯一性。因此，不必为主键列显式创建索引。

PostgreSQL automatically creates an index for each unique constraint and primary key constraint to enforce uniqueness. Thus, it is not necessary to create an index explicitly for primary key columns.

和约束说：

由于引用表的行的DELETE或
引用列的UPDATE将需要扫描与旧值匹配的
行的引用表，所以它通常是好的想要索引
引用列。因为这不是总是需要的，并且有
有许多关于如何索引的选择，外键的声明
约束不会在引用
列上自动创建索引。

Since a DELETE of a row from the referenced table or an UPDATE of a referenced column will require a scan of the referencing table for rows matching the old value, it is often a good idea to index the referencing columns. Because this is not always needed, and there are many choices available on how to index, declaration of a foreign key constraint does not automatically create an index on the referencing columns.

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

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和索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！