EDB博客

如何在PostgreSQL中使用EXPLAIN ANALYZE计划和优化查询性能

四月

如何在PostgreSQL中使用EXPLAIN ANALYZE计划和优化查询性能

如今,由于冠状病毒大流行,许多人在家工作,因此很难从同事那里获得帮助。确保有Slack和各种协作工具,但与走到小隔间并不太一样而让第二只眼睛去看一个问题,更不用说我们的同事可能正忙于在家里处理最后期限和不守规矩的孩子当涉及到不良的数据库和查询性能时,这是一项艰巨的任务,要冒险查询计划和优化的黑暗洞穴,但不要害怕EXPLAIN是我们在那些黑暗和孤独的地方的朋友

我们最近收到了一位客户的请求,该客户担心对其JSON列之一进行缓慢查询。他们在开发环境中发现性能下降,并且可以理解的是,他们担心如果查询不佳而进入生产环境,将会产生影响性能我们有权利去帮助他们,我们首先要解决的是让他们将其EXPLAIN ANALYZE输出发送给我们,以产生查询

postgres解释SELECT FROM org,其中SELECT jsonb数组元素中的文本信息部门名称查询计划Seq扫描org成本行宽度Filter SubPlan SubPlan Result成本行宽度ProjectSet成本行宽度结果Set成本行宽度

他们知道他们已经创建了索引,并对为什么不使用索引感到好奇。我们要收集的下一个数据点是有关索引本身的信息,事实证明他们像这样创建了索引

创建索引idx组织部门打开组织信息部门文本名称文本

注意任何事情他们的查询将信息部包装在一个名为jsonb数组元素的函数中,该函数使查询计划者认为它不应该使用索引。修复很简单,经过相当快速的调整,我们能够使客户回到他们的路上客户查询一旦客户将查询更改为以下内容,索引就开始被扫描

postgres从组织中选择信息输入部门名称中的文本postgres解释从组织中选择信息输入部门名称中的文本查询查询计划索引使用idx org dept对组织成本行宽度进行索引扫描cond aa文本信息输入部门名称文本行

正如我们所看到的,在故障排除库中拥有和使用EXPLAIN可能是无价的

什么是解释

EXPLAIN是在查询之前添加的关键字,以向用户显示查询计划者计划如何执行给定查询,具体取决于查询的复杂性,它将显示联接策略方法,该策略从涉及执行查询的估计行的表中提取数据以及与ANALYZE EXPLAIN一起使用的其他许多有用信息,也将显示执行查询排序和合并所花费的时间,而这是无法在内存中完成的,甚至更多。此信息对于确定查询性能瓶颈和机会以及帮助我们了解查询计划员为我们做出决策时使用的信息

基于成本的方法

对于查询计划者来说,磁盘上的所有数据基本上是相同的。要确定到达特定数据的最快方式,需要估算一下进行全表扫描合并两个表以及执行其他操作所花费的时间。将数据返回给用户PostgreSQL通过为每个执行任务分配成本来完成此任务,并且这些值是从postgresql conf文件派生的,请参见以cost结尾或以enable开头的参数当查询发送到数据库时,查询计划者将计算累积成本针对不同的执行策略,并选择最佳方案,而不一定是成本最低的方案

bash pgbench i psql postgres解释从pgbench帐户中选择一个JOIN pgbench分支b在一个投标b一个投标上查询计划嵌套循环成本行宽度Join过滤一个投标b投标Seq在pgbench分支上扫描b成本行宽度Seq在pgbench帐户上扫描成本行宽度助滤器行

在这里,我们看到对pgbench帐户进行Seq扫描会花费执行任务的成本。该值从何而来。如果我们查看一些设置并进行计算,我们发现

成本块seq页面成本记录cpu元组成本记录cpu过滤器成本postgres选择pg关系大小pgbench帐户pg关系尺寸块大小kB典型OS块关系大小块大小记录seq页面成本默认cpu元组成本默认cpu过滤器成本默认成本

如我们所见,成本直接基于查询计划者可以使用的一些内部统计数据

关于统计的注意事项

查询计划者根据存储在pg统计信息中的统计信息来计算成本。不要在其中查看任何内容。如果要查看表和行统计信息,请尝试查看pg统计信息。如果这些内部统计信息中有任何一个关闭,即显示a肿表或太多的联接会导致Generic Query Optimizer实施次优计划,可能会导致查询性能不佳。统计数据不一定一定是问题,并非总是实时更新统计信息,并且大部分取决于PostgreSQL内部维护因此,必须定期进行数据库维护,这意味着需要频繁进行VACUUM和ANALYZEING。

postgres解释从pgbench历史记录中选择的地方辅助工具查询计划seq扫描pgbench历史记录成本行宽度过滤辅助

在上面的示例中,数据库经历了相当多的活动,并且统计信息不准确,而ANALYZE不是VACUUM ANALYZE或EXPLAIN ANALYZE,而只是简单的ANALYZE,统计信息是固定的,查询计划者现在选择Index Scan

postgres解释从pgbench历史记录中选择查询的地方辅助查询计划索引在pgbench历史记录上使用foo进行扫描成本行宽度索引康迪辅助

EXPLAIN ANALYZE如何提供帮助

当对查询使用EXPLAIN时,将打印查询计划,但查询无法运行我们不会知道存储在数据库中的统计信息是否正确,也不会知道某些操作是否需要昂贵的IO而不是完全运行在内存中当与ANALYZE一起使用时,查询实际上正在运行,并且查询计划以及一些底层活动被打印出来

如果我们查看上面的第一个查询并运行EXPLAIN ANALYZE而不是普通的EXPLAIN,我们将得到

postgres从pgbench帐户中分析选择SELECT JOIN pgbench分支b在投标b投标上b在何处查询查询计划嵌套循环成本行宽度实际时间行循环加入过滤投标b投标seq扫描pgbench分支b成本行宽度实际时间行循环在pgbench上进行Seq扫描时,出现了一个成本行宽度实际时间行循环

您会在这里注意到,还有更多信息实际时间和行以及计划和执行时间。如果我们添加诸如EXPLAIN ANALYZE BUFFERS之类的缓冲区,我们甚至会在输出中获得缓存命中未命中统计信息

postgres EXPLAIN BUFFERS分析从pgbench帐户中进行的选择加入pgbench分支b投标b投标哪里有帮助查询计划嵌套循环成本行宽度实际时间行循环加入过滤投标b投标共享命中读取seq扫描pgbench分支b成本行width实际时间行循环缓冲区共享命中pgbench帐户上的Seq扫描成本行宽度实际时间行循环过滤器辅助行被filter缓冲区删除的共享命中行读取计划时间ms执行时间ms行

很快您就会发现EXPLAIN对于希望了解其数据库性能行为的人们而言可能是一个有用的工具

扫描类型和联接的快速回顾

重要的是要知道每种连接类型和扫描类型都有其时间和位置,有些人寻找“顺序扫描”一词,然后立即跳回去,因为担心没有考虑是否值得再访问一次数据,例如一个带有行的表。对于查询计划者来说,扫描索引然后返回并从磁盘检索数据是没有意义的,因为它可以快速扫描表并在不接触索引的情况下拉出数据。在这种情况下,对于大多数其他小表进行顺序扫描会更有效,以便快速查看PostgreSQL使用的连接和扫描类型

  • 扫描方式
    • 顺序扫描
      • 基本上是从磁盘上进行蛮力检索
      • 扫描整个桌子
      • 小桌子快
    • 索引扫描
      • 扫描索引中的所有某些行以查找堆中的行
      • 导致随机查找,这对于基于老式主轴的磁盘可能会造成高昂的成本
      • 为大型表提取少量行时,比顺序扫描更快
    • 仅索引扫描
      • 扫描索引中的所有行
      • 不需要查找表中的行,因为所需的值已经存储在索引本身中
    • 位图堆扫描
      • 扫描索引建立要访问的页面位图
      • 然后仅在表格中查找相关页面以查找所需的行
  • 联接类型
    • 嵌套循环
      • 对于外部表中的每一行,扫描内部表中的匹配行
      • 快速启动,适合小桌子
    • 合并加入
      • 对排序的数据集进行拉链操作
      • 适合大桌子
      • 如果需要其他种类,则启动成本高
    • 哈希加入
      • 内部表值的构建哈希扫描外部表以查找匹配项
      • 仅适用于平等条件
      • 启动成本高但执行速度快

我们可以看到每种扫描类型和联接类型都有其位置,最重要的是查询计划程序具有良好的统计数据,如前所述

我们仅讨论了EXPLAIN帮助发现问题并给出解决方案的一个实例。在EDB支持中,我们看到了EXPLAIN可以帮助识别诸如以下情况的许多情况:

  • 统计信息不正确导致联接扫描选择不正确
  • 维护活动VACUUM和ANALYZE不够积极
  • 损坏的索引需要REINDEX
  • 索引定义v查询不匹配
  • 工作记忆库设置得太低,阻止内存排序和连接
  • 编写查询时由于加入订单列表而导致性能不佳
  • ORM配置不正确

对于使用PostgreSQL的任何人来说,EXPLAIN无疑是最宝贵的工具之一,使用得当可以节省大量时间

加入Postgres Pulse Live

我们利用解决的问题和与Postgres的对话来帮助人们,这是在动手EXPLAIN的另一个例子,查询计划器并没有从我们在这里概述的内容开始或停止,因此如果您还有其他问题我们在这里为您服务您可以找到我们所有的博客和YouTube系列这里您随时可以加入我们的下届会议

请在5月1日星期一加入我们的下一个Pulse Live课程我们将深入探讨本周有关EXPLAIN使用的问题和疑问句,并从参加活动的人那里提问。您可以通过以下电子邮件地址提问postgrespulse enterprisedb comTwitter上的主题标签或活动期间的现场直播就在这儿

理查德·日元企业数据库的图片

Richard是EnterpriseDB的高级支持工程师,并支持整个EnterpriseDB产品套件。在加入EnterpriseDB之前,Richard曾是数据库工程师和Web开发人员,主要负责可扩展性和可伸缩性方面的操作。