如何强制 Postgres 使用特定索引? [英] How do I force Postgres to use a particular index?
问题描述
如果 Postgres 坚持执行顺序扫描，我如何强制它使用索引?
How do I force Postgres to use an index when it would otherwise insist on doing a sequential scan?
推荐答案
假设您要问的是许多数据库中常见的索引提示"功能，PostgreSQL 并没有提供这样的功能.这是 PostgreSQL 团队有意识的决定.此处可以很好地概述为什么以及您可以做什么.原因基本上是它是一个性能黑客，随着您的数据更改，它往往会在以后导致更多问题，而 PostgreSQL 的优化器可以根据统计信息重新评估计划.换句话说，今天可能是一个好的查询计划可能不会一直都是一个好的查询计划，索引提示会强制执行一个特定的查询计划.
Assuming you're asking about the common "index hinting" feature found in many databases, PostgreSQL doesn't provide such a feature. This was a conscious decision made by the PostgreSQL team. A good overview of why and what you can do instead can be found here. The reasons are basically that it's a performance hack that tends to cause more problems later down the line as your data changes, whereas PostgreSQL's optimizer can re-evaluate the plan based on the statistics. In other words, what might be a good query plan today probably won't be a good query plan for all time, and index hints force a particular query plan for all time.
作为一个非常钝的锤子，对测试很有用，您可以使用 enable_seqscan 和 enable_indexscan 参数.见:
As a very blunt hammer, useful for testing, you can use the enable_seqscan and enable_indexscan parameters. See:
这些不适合持续的生产使用.如果您在选择查询计划时遇到问题，您应该查看 跟踪文档查询性能问题.不要只是设置 enable_ 参数然后走开.
These are not suitable for ongoing production use. If you have issues with query plan choice, you should see the documentation for tracking down query performance issues. Don't just set enable_ params and walk away.
除非您有充分的理由使用索引，否则 Postgres 可能会做出正确的选择.为什么?
Unless you have a very good reason for using the index, Postgres may be making the correct choice. Why?
- 对于小表，顺序扫描速度更快.
- 当数据类型不正确匹配时，Postgres 不使用索引，您可能需要包含适当的强制转换.
- 您的计划器设置可能会导致问题.
另见这篇旧的新闻组帖子.
这篇关于如何强制 Postgres 使用特定索引?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
