如何强制Postgres使用特定索引？

如果它坚持进行顺序扫描,我如何强制Postgres使用索引？

1> Patryk Kordy..：

---

假设您询问许多数据库中常见的"索引提示"功能,PostgreSQL不提供这样的功能.这是PostgreSQL团队做出的有意识的决定.您可以在此处找到有关原因以及可以执行的操作的详细概述.原因基本上是它是一个性能黑客,随着数据的变化,往往会导致更多的问题,而PostgreSQL的优化器可以根据统计数据重新评估计划.换句话说,今天可能是一个好的查询计划可能不会是一个好的查询计划,并且索引提示会强制执行特定的查询计划.

作为一个非常钝的锤子,对测试很有用,你可以使用enable_seqscan和enable_indexscan参数.看到:

检查索引使用情况

enable_ 参数

这些不适合正在进行的生产使用.如果您在选择查询计划时遇到问题,则应该会看到用于跟踪查询性能问题的文档.不要只是设置enable_参数并走开.

除非你有充分的理由使用索引,否则Postgres可能会做出正确的选择.为什么？

对于小型表,执行顺序扫描的速度更快.

当数据类型不正确匹配时,Postgres不使用索引,您可能需要包含适当的强制转换.

您的计划程序设置可能会导致问题.

另见这篇旧的新闻组帖子.

---

凭借在Oracle,Teradata和MSSQL等平台上超过30年的经验,我发现PostgreSQL 10的优化器并不是特别聪明.即使使用最新的统计数据,它也会产生效率低于执行计划的效率.提供结构提示来补偿这些问题将提供一个解决方案,使PostgreSQL能够在更多细分市场中发展.恕我直言.

---

同意,强迫postgres以你的方式去做通常意味着你做错了.9/10计划者将击败你能想到的任何东西.另一次是因为你做错了.

---

我讨厌复兴一个旧问题,但我经常在Postgres文档,讨论和这里看到,但是对于*小表*的资格是否有一个概括的概念？是5000行还是50000等？

2> 小智..：

---

可能是使用的唯一正当理由

set enable_seqscan=false

是在你编写查询时,想要快速查看查询计划实际上是什么,表中有大量数据.或者当然,如果您需要快速确认您的查询没有使用索引只是因为数据集太小.

---

这个简短的回复实际上提供了一个很好的测试目的提示

---

没有人回答这个问题!

---

@BrianHellekin更好，`SET SESSION enable_seqscan = false`只影响自己

3> Ziggy Cruelt..：

---

有时PostgreSQL无法为特定条件做出最佳的索引选择.例如,假设有一个包含数百万行的事务表,其中任何给定日期都有几百行,该表有四个索引:transaction_id,client_id,date和description.您想要运行以下查询:

SELECT client_id, SUM(amount)
FROM transactions
WHERE date >= 'yesterday'::timestamp AND date < 'today'::timestamp AND
      description = 'Refund'
GROUP BY client_id

PostgreSQL可以选择使用索引transactions_description_idx而不是transactions_date_idx,这可能导致查询花费几分钟而不是一秒钟.如果是这种情况,您可以通过捏造条件来强制使用日期索引,如下所示:

SELECT client_id, SUM(amount)
FROM transactions
WHERE date >= 'yesterday'::timestamp AND date < 'today'::timestamp AND
      description||'' = 'Refund'
GROUP BY client_id

4> emkey08..：

---

简短答案

当索引扫描的估计成本太高并且不能正确反映现实时，通常会发生此问题。您可能需要降低random_page_cost配置参数来解决此问题。从Postgres文档中：

降低此值将使系统偏向于索引扫描；提高它会使索引扫描看起来相对更昂贵。

您可以检查一个较小的值是否实际上会使Postgres使用索引（但只能将其用于测试）：

EXPLAIN ;              # Uses sequential scan
SET random_page_cost = 1;
EXPLAIN ;              # May use index scan now

您可以SET random_page_cost = DEFAULT;再次使用恢复默认值。

背景

索引扫描需要非顺序磁盘页读取。Postgres用于random_page_cost估计与顺序获取有关的此类非顺序获取的成本。默认值为4.0，因此假设与顺序读取相比，平均成本因子为4（考虑了缓存效果）。

但是，问题在于此默认值不适用于以下重要的实际场景：

1）固态驱动器

正如文档所承认的：

相对于顺序驱动（例如固态驱动器）而言，具有较低随机读取成本的存储可以用较低的值更好地建模random_page_cost。

根据最后一点这张幻灯片从谈一谈PostgresConf 2018年，random_page_cost应设置之间的事情1.0和2.0固态驱动器。

2）缓存的数据

如果所需的索引数据已经缓存在RAM中，则索引扫描将始终比顺序扫描快得多。该文件说：

相应地，如果您的数据很可能完全在缓存中，则减少数据丢失random_page_cost是适当的。

问题在于，您当然不能轻易知道相关数据是否已被缓存。但是，如果经常查询特定索引，并且系统具有足够的RAM，则可能会缓存数据，因此random_page_cost应将其设置为较低的值。您将不得不尝试不同的值，然后看看哪种方法对您有用。

您可能还希望将pg_prewarm扩展用于显式数据缓存。

---

5> 小智..：

---

问题本身就非常无效.强制(通过执行enable_seqscan = off)是非常糟糕的主意.检查它是否会更快可能是有用的,但生产代码永远不应该使用这些技巧.

相反 - 做解释分析您的查询,阅读它,并找出PostgreSQL选择不好(在您看来)计划的原因.

网上有一些工具可以帮助阅读解释分析输出 - 其中一个是explain.depesz.com - 由我编写.

另一种选择是在freenode irc网络上加入#postgresql频道,并与那里的人交谈以帮助你 - 因为优化查询不是"问一个问题,让答案快乐"的问题.它更像是一次谈话,需要检查很多东西,还有很多东西需要学习.