postgres外键是否暗示索引？ [英] Does a postgres foreign key imply an index?

问题描述

我有一个postgres表（让我们将此表称为 Events ）和一个到另一个表的复合外键（让我们将此表称为 Logs ）。事件表如下所示：

I have a postgres table (lets call this table Events) with a composite foreign key to another table (lets call this table Logs). The Events table looks like this:

CREATE TABLE Events (
   ColPrimary UUID,
   ColA VARCHAR(50),
   ColB VARCHAR(50),
   ColC VARCHAR(50),
   PRIMARY KEY (ColPrimary),
   FOREIGN KEY (ColA, ColB, ColC) REFERENCES Logs(ColA, ColB, ColC)
);

在这种情况下，我知道我可以通过主键有效地搜索事件，并加入日志。

In this case, I know that I can efficiently search for Events by the primary key, and join to Logs.

我感兴趣的是，如果此外键在事件表上创建了一个索引，即使没有加入该索引也很有用。例如，以下查询会从FK中受益吗？

What I am interested in is if this foreign key creates an index on the Events table which can be useful even without joining. For example, would the following query benefit from the FK?

SELECT * FROM Events
WHERE ColA='foo' AND ColB='bar'

注意：我已经针对类似的情况运行了POSTGRES EXPLAIN ，并看到该查询将导致全表扫描。我不确定这是因为FK对该查询没有帮助，还是因为我的数据量较小并且在当前规模下扫描效率更高。

Note: I have run the POSTGRES EXPLAIN for a very similar case to this, and see that the query will result in a full table scan. I am not sure if this is because the FK is not helpful for this query, or if my data size is small and a scan is more efficient at my current scale.

推荐答案

PostgreSQL不会（ not ）在定义外键的列上自动创建索引。如果需要这样的索引，则必须自己创建。

PostgreSQL does not automatically create an index on the columns on which a foreign key is defined. If you need such an index, you will have to create it yourself.

拥有这样的索引通常是一个好主意，以便对父表进行修改影响引用列的效率。

It is usually a good idea to have such an index, so that modifications on the parent table that affect the referenced columns are efficient.

这篇关于postgres外键是否暗示索引？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！