如何强制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:
- Examining index usage
enable_parameters
这些不适合正在进行的生产用途。如果您在选择查询计划时遇到问题，则应该看到用于跟踪的文档查询性能问题。不要只设置启用_ 参数并离开。
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屋！
