postgresql - 向索引列添加外键会提高性能吗？

Question

一位同事声称他过去使用外键来优化查询。我认为只有在表中插入或更新数据时才使用外键。我不知道如何使用它们来加快搜索速度。

外键如何帮助创建执行计划？我错过了什么吗？如果是，那么它在什么情况下有帮助？

(我们使用 PostgreSQL，我对它没有太多经验。它的行为是否可能与 Oracle 或 MySQL 不同？)

Answer 1

是的，外键绝对可以提高查询的性能，但这取决于您使用的数据库以及这些键是否“强制”。

在具有外键的 Oracle 和 SQL Server 中，当读取/加入外键上的多个表时，肯定可以提高性能

为什么？拥有一个checked/validated 外键可以为查询优化器提供有关 2 个表具有的关系的额外信息。

它知道，当子表内部连接到父表时:

1. 与子表相比，父表的记录数相同或更少。
2. 子项中的所有键都存在于父项中。

这一切都有助于查询优化器估计将要处理的行。这种正确的估计对于大多数(如果不是全部)查询优化器来说非常重要。

证明这个一般事实可以从最近以仅元数据的形式添加到 Hadoop Hive 的外键中看出。此添加的目标是帮助 CBO(基于成本的优化器)，this Hive Jira entry解释...

此外，在外键上使用(位图)索引还可以提高 Oracle 在使用事实表时的性能:“应该在一个或多个事实表的每个外键列上构建位图索引”。查看following链接...

外键，由于显而易见的原因，在插入/更新数据时会花费您的额外费用:与没有外键相比，数据库必须完成额外的工作

您可以通过调查解释计划轻松地在 SQL 服务器(例如)中看到这一点。

我不了解 Postgresql，但我验证 FK 效果的方法是查看解释计划。当启用/禁用/删除 FK 时，它们是否不同？

[编辑]我实际上找到了 this证明 FK 可以在 Postgresql 中启用读取性能，但原因有所不同:因为 FK 已启用，示例中的查询可以更改为更高性能。