sql - 关于外键和主键的 Postgres 和索引

Question

Postgres 会自动在外键和主键上建立索引吗？我怎么知道？是否有一个命令可以返回表上的所有索引？

Answer 1

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

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

documentation on unique indexes说:

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.

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

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

虽然在(或包括)您的引用端外键列上创建索引通常是个好主意，但这不是必需的。您添加的每个索引都会稍微减慢 DML 操作的速度，因此您需要为每次 INSERT、UPDATE 或 DELETE 支付性能成本。如果索引很少被使用，它可能不值得拥有。