我们如何使用Postgres扩展统计数据实现3000倍的加速

与DMV非常相似，PostgreSQL查询规划器是一个强大而神秘的实体，我们半盲目地将自己的福祉托付给它。它肩负着为每个查询挑选最有效的执行计划的重要责任。在这里，我们将探索在创建查询计划时要考虑哪些data Postgres，以及我们如何使用该上下文来帮助查询规划者为我们的一些最重要的查询模式制定更有效的计划。

下面是从我们的Web服务器发出的一个慢查询示例，以及Postgres选择的低效查询计划。你能找出查询计划员犯下的关键错误吗？

Postgres估计这一步将返回大约1行，这是一个疯狂的低估-它实际上返回了1958行，花费了大约122秒。(有关如何解释Postgres查询计划的更多背景信息，请参阅此处。)。

通过明智地使用Postgres统计数据，我们将此查询的时间从2分钟缩短到42毫秒-几乎加速了3000倍！在我们深入研究所做的统计调整之前，让我们确保了解Postgres计划器是如何工作的。

统计数据是Postgres收集的数据，用于通知其查询计划的选择。Postgres现成地对每个表的每一列的可能值进行采样，以创建直方图和最常见值的列表(以及其他内容)。这些用于估计将某组筛选器应用于表将产生多少行。

对于较大的表，计划器无法跟踪列包含的每个值。取而代之的是，它对每列的值进行采样，并使用这些值进行估计。我们可以使用以下命令调整Postgres对每个表上的每列执行的采样数量。

其中-1将其设置为默认值100(Docs)。此数字设置直方图中使用了多少个存储桶，以及存储了多少个最常用的值。

增加列的统计数据的缺点是必须在pg_statistic中存储更多的数据，并且在列的表上运行分析所需的时间更长。

扩展统计信息是用户定义的对象，它告诉Postgres为多组列收集特定类型的数据，而不是收集单个列的数据。

在没有扩展统计信息的情况下，Postgres通过独立考虑每个筛选器来估计筛选器对表的影响。例如，假设一个数据库包含10个Artist记录，每个记录都有10个专辑记录引用它，每个记录都有10首歌曲引用它。总共有10位艺术家，100张专辑和1000首歌曲。现在，考虑运行以下查询：

对歌曲(成本=0.28..6.05行=1Wid159)(实际时间=5.555..5.562行=10个循环=1)索引条件：((Artists_id=1)AND(ALLUAL_ID=1))索引扫描(成本=0.28..6.05行=1Wid159)索引条件：((Artists_id=1)AND(ALLUAL_ID=1))规划时间：311.482毫秒执行时间：9.266毫秒(4行)。

(成本=0.28..6.05行=1宽度=159时)是指计划员的估算，而(实际时间=5.555..5.562行=10循环=1)是指执行计划的实际结果。规划者估计将返回1行，但实际上返回了10行。

规划器通过首先获取歌曲总数(1000)，然后考虑Artists_id过滤器来计算其行估计。10%的歌曲的Artists_id=1，因此剩下100首歌曲。接下来，它考虑alicalid过滤器。1%的歌曲的Alignment_id=1，所以只剩下1首歌了。

Postgres遗漏的关键信息是艺术家id和相册id是强相关的。事实上，知道相册_id就可以唯一地确定艺术家_id。如果Postgres知道这一点，它在估计时可能只使用alical_id=1过滤器，并得出10首歌曲的正确结果。

这种相关性可以使用依赖统计信息指示给Postgres。此统计信息存储每列唯一确定另一列的频率。(Artist_id，Alignment_id)的依赖项统计信息可能会产生以下结果：

从歌曲中创建统计数据Alignment_id_Artist_id_dep_stt(依赖项)；分析歌曲；从PG_STATISTICS_EXT选择stxname、stxkey、stxd依赖项加入PG_STATISTICAL_EXT_DATA on(oid=stxoid)where stxname='；STTS'；StxName|stxKey|stxd依赖-+-+--STT|1 5|{"；1=>；5"；：0.1，"；5=>；1"；：1}(1行)

Stxkey和stxd依赖项下的1和5分别指的是歌曲表上的第1列和第5列，它们分别是艺术家id和专辑id。"；1=>；5"；的值为0.1，因为艺术家id在10%的时间内决定alicel_id。"；5=>；1"；的值为1.0，因为alog_id始终决定艺术家id。当Postgres按具有匹配的依赖关系统计信息的列进行过滤时，它可以使用它来进行更准确的估计。

当然，还有其他类型的扩展统计信息，但是依赖关系统计信息对于这种数据分布最有意义。

扩展统计信息的一个警告是，Postgres只知道在过滤统计信息中引用的列以及使用简单的相等条件进行过滤时才使用它们，例如Artist_id=5，而不是Artist_id IN(5，6)或Artist_id<；10。

使用扩展统计信息可能会导致非直观的索引选择。如果依赖项统计信息向postgres表明列过滤器是冗余的，就像艺术家_id和专辑_id的情况一样，它可以选择使用仅引用其中一列的索引。在歌曲的情况下，如果两者都存在，则它可以仅使用索引(Alical_Id)，而不是索引(Artist_id，alical_id)。

嵌套循环联接。使用此连接策略，Postgres循环访问左关系中的每一行，并扫描右关系以查找满足连接条件的行，理想情况下使用索引。当左关系中只有很少的行时，这是一种有效的策略。

合并联接。从文档中可以看到：“在连接开始之前，根据连接属性对每个关系进行排序。然后并行扫描这两个关系，并组合匹配的行以形成联接行。这种联接更有吸引力，因为每个关系只需扫描一次。所需的排序可以通过显式排序步骤来实现，也可以通过使用连接键上的索引以正确的顺序扫描关系来实现。“。

散列联接。文档中写道：“首先扫描正确的关系并将其连接属性作为散列键加载到散列表中。接下来，扫描LEFT关系，并将找到的每一行的适当值用作散列键，以定位表中匹配的行。“。

就我们的目的而言，这里要注意的主要事情是，与其他连接策略相比，嵌套循环连接的优势是开销非常小。但是，如果左关系中有很多行，则此联接可能会出错。例如，假设左关系中有1,000行，并且Postgres使用索引来访问右关系。如果每个索引访问耗时4ms，则整个连接将耗时4s，这在响应用户请求的上下文中太慢了。

现在我们了解了不同类型的联接，让我们再来看看我们认为有问题的嵌套循环联接。不详细介绍Affinity的数据模型，您只需要知道在表Entity_Values和List_Entries上，列org_id由list_id或entity_attribute_id唯一确定，这意味着为了估计一组筛选器对这些列的选择性，不应该单独考虑筛选器。我们的缓慢查询是由于Postgres低估了应用过滤条件而导致的行数，并且由于低估而选择使用嵌套循环连接的结果。

让我们回顾一下最初的问题查询。到目前为止，最昂贵的步骤是对entity_values_org_id_entity_attribute_id_company_id_index的索引访问循环高达13,769次。

为了鼓励规划者使用不同的联接策略，我们需要改进其对LISTS_ENTRIES和ENTITY_VALUES过滤器的估计。根据应用的筛选器，我们最大限度地增加了以下各项的每列统计信息：

在其他表和列的其他依赖项统计信息中，因为list_id和entity_attribute_id都唯一地确定org_id。

在我们进行这些调整之后，Postgres为我们的原始查询选择了以下查询计划：

在这里，估计要准确得多，规划者为内部连接选择了散列连接-查询花费了42毫秒，而不是原来的2分钟。

增加每列统计信息和添加依赖项统计信息有很大帮助，但仍有需要改进的地方。正如您在改进的查询计划中可能已经注意到的那样，规划器低估了内连接产生的行数。虽然这次外部嵌套循环连接花费的时间不长，但是不难想象这样一个查询：内部连接导致许多行，而外部连接成为瓶颈。

我们希望这篇文章给了你一些关于如何改进你的查询计划的想法，或者至少教会了你一些Postgres的魔力！