外键和主键的 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 级别的消息，您可以在 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.

constraints 上的文档说:

因为从被引用的表中删除了一行或者更新了一个表被引用的列将需要扫描引用表与旧值匹配的行，索引引用列.因为这并不总是需要，而且有关于如何索引、外键声明的许多选择约束不会自动在引用上创建索引列.

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.

请注意，如果您在 M-to-N 表中使用主外键，例如 2 个 FK 作为 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屋！