更方便地了解 PostgreSQL 查询计划

半年前 我们提出了 解释.tensor.ru - 民众 用于解析和可视化查询计划的服务 到 PostgreSQL。

在过去的几个月里我们对他做了很多报道 PGConf.Russia 2020 报告，准备了一份总结 关于加速 SQL 查询的文章 基于它给出的建议......但最重要的是，我们收集了您的反馈并查看了真实的用例。

现在我们准备讨论您可以利用的新机会。

支持不同的计划格式

根据日志和请求进行计划

直接从控制台选择整个块，从以下行开始 查询文本，所有前导空格：

        Query Text: INSERT INTO  dicquery_20200604  VALUES ($1.*) ON CONFLICT (query)
                           DO NOTHING;
        Insert on dicquery_20200604  (cost=0.00..0.05 rows=1 width=52) (actual time=40.376..40.376 rows=0 loops=1)
          Conflict Resolution: NOTHING
          Conflict Arbiter Indexes: dicquery_20200604_pkey
          Tuples Inserted: 1
          Conflicting Tuples: 0
          Buffers: shared hit=9 read=1 dirtied=1
          ->  Result  (cost=0.00..0.05 rows=1 width=52) (actual time=0.001..0.001 rows=1 loops=1)

...并将所有复制的内容直接放入计划字段中，而不分离任何内容：

最后我们得到了拆解计划的奖励， “上下文”选项卡，我们的要求在这里得到了充分的体现：

JSON 和 YAML

EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT * FROM pg_class;

"[
  {
    "Plan": {
      "Node Type": "Seq Scan",
      "Parallel Aware": false,
      "Relation Name": "pg_class",
      "Alias": "pg_class",
      "Startup Cost": 0.00,
      "Total Cost": 1336.20,
      "Plan Rows": 13804,
      "Plan Width": 539,
      "Actual Startup Time": 0.006,
      "Actual Total Time": 1.838,
      "Actual Rows": 10266,
      "Actual Loops": 1,
      "Shared Hit Blocks": 646,
      "Shared Read Blocks": 0,
      "Shared Dirtied Blocks": 0,
      "Shared Written Blocks": 0,
      "Local Hit Blocks": 0,
      "Local Read Blocks": 0,
      "Local Dirtied Blocks": 0,
      "Local Written Blocks": 0,
      "Temp Read Blocks": 0,
      "Temp Written Blocks": 0
    },
    "Planning Time": 5.135,
    "Triggers": [
    ],
    "Execution Time": 2.389
  }
]"

无论是带外部引号，如 pgAdmin 副本，还是不带外部引号 - 我们将其放入同一个字段中，输出很漂亮：

高级可视化

计划时间/执行时间

现在您可以更好地了解执行查询的额外时间花费在哪里：

I/O 时序

有时您必须处理这样的情况：就资源而言，似乎没有太多的读取和写入，但执行时间似乎很长。

在这里我们不得不说：“哦，可能是那个时候服务器的磁盘超载了，所以才读了这么久！“但不知何故，这不是很准确......

但这是可以绝对可靠地确定的。 事实上，PG 服务器配置选项中有 track_io_timing:

启用 I/O 操作的计时。 默认情况下禁用此选项，因为它需要不断查询操作系统的当前时间，这可能会显着降低某些平台上的性能。 要估计平台上的计时成本，您可以使用 pg_test_timing 实用程序。 I/O统计信息可以通过pg_stat_database视图获得， 在 EXPLAIN 输出中（当使用 BUFFERS 参数时） 并通过 pg_stat_statements 视图。

也可以在本地会话中启用此选项：

SET track_io_timing = TRUE;

好吧，现在最好的部分是我们已经学会了理解和显示这些数据，同时考虑到执行树的所有转换：

在这里您可以看到，在总执行时间的 0.790 毫秒中，读取一个数据页花费了 0.718 毫秒，写入一个数据页花费了 0.044 毫秒，而只有 0.028 毫秒花费在所有其他有用的活动上！

PostgreSQL 13 的未来

您可以找到创新的完整概述 在一篇详细的文章中，我们专门谈论计划的变化。

规划缓冲区

分配给调度程序的资源的核算反映在与 pg_stat_statements 无关的另一个补丁中。 带有 BUFFERS 选项的 EXPLAIN 将报告规划阶段使用的缓冲区数量：

 Seq Scan on pg_class (actual rows=386 loops=1)
   Buffers: shared hit=9 read=4
 Planning Time: 0.782 ms
   Buffers: shared hit=103 read=11
 Execution Time: 0.219 ms

增量排序

在需要对多个键（k1、k2、k3...）进行排序的情况下，规划器现在可以利用数据已在多个前键（例如 k1 和 k2）上进行排序的知识。 在这种情况下，你不能再次对所有数据进行重新排序，而是将其分成具有相同 k1 和 k2 值的连续组，并通过键 k3 将其“重新排序”。

这样，整个排序就被分成几个连续的较小尺寸的排序。 这减少了所需的内存量，并且还允许在整个排序完成之前输出第一个数据。

 Incremental Sort (actual rows=2949857 loops=1)
   Sort Key: ticket_no, passenger_id
   Presorted Key: ticket_no
   Full-sort Groups: 92184 Sort Method: quicksort Memory: avg=31kB peak=31kB
   ->  Index Scan using tickets_pkey on tickets (actual rows=2949857 loops=1)
 Planning Time: 2.137 ms
 Execution Time: 2230.019 ms

用户界面/用户体验改进

截图，无处不在！

现在，在每个选项卡上都有机会快速 将选项卡的屏幕截图保存到剪贴板 标签的整个宽度和深度 - 右上角的“视线”:

事实上，本刊的大部分图片都是通过这种方式获得的。

节点推荐

不仅变得越来越多，而且还可以逐一讨论 详细阅读文章通过以下链接：

从存档中删除

有些人确实要求添加该选项 删除“完全” 即使计划未在存档中发布 - 请单击相应的图标：

好吧，别忘了我们有 支援团队，您可以在这里写下您的意见和建议。

来源： habr.com