关于我们如何优化 PostgreSQL 查询以及由此产生的结果。
为什么你必须这样做？ 是的，因为在过去的四年里，一切都安静、平静地进行，就像时钟滴答作响。
作为一个题词。
根据真实事件改编。
所有名字均已更改，巧合纯属偶然。
当你取得某种成果时，记住开始的动力是什么，一切是从哪里开始的，总是很有趣的。
因此，文章中简要描述了结果发生的情况““。
重新创建之前的事件链可能会很有趣。
历史记录保存了确切的开始日期 - 2018-09-10 18:02:48。
此外，故事中还有一个请求，这一切都是从这个请求开始的：
问题请求选择
p.“PARAMETER_ID”作为parameter_id，
pd."PD_NAME" AS pd_name,
pd.“CUSTOMER_PARTNUMBER”AS customer_partnumber，
w.“LRM”作为 LRM，
w.“LOTID”作为lotid，
w.“RTD_VALUE”作为RTD_值，
w.“LOWER_SPEC_LIMIT” AS lower_spec_limit，
w.“UPPER_SPEC_LIMIT” AS upper_spec_limit，
p.“TYPE_CALCUL”AS type_calcul，
s.“SPENT_NAME”作为花名，
s.“SPENT_DATE”作为花费日期，
提取（“SPENT_DATE”中的年份）AS年份，
提取（“SPENT_DATE”中的月份）作为月份，
s."REPORT_NAME" AS 报告名称，
p.“STPM_NAME”AS stpm_name，
p.“CUSTOMERPARAM_NAME” AS customerparam_name
来自 wdata w,
花费了 s,
时间点 p,
花费_pd sp，
PD PD
其中 s.“SPENT_ID” = w.“SPENT_ID”
AND p."PARAMETER_ID" = w."PARAMETER_ID"
AND s.“SPENT_ID” = sp.“SPENT_ID”
AND pd."PD_ID" = sp."PD_ID"
AND s.“SPENT_DATE” >= '2018-07-01' AND s.“SPENT_DATE” <= '2018-09-30'
和 s.“SPENT_DATE” = (SELECT MAX(s2.“SPENT_DATE”)
从花费的 s2 开始，
数据w2
其中 s2.“SPENT_ID” = w2.“SPENT_ID”
且 w2.“LRM” = w.“LRM”);
问题的描述不出所料是标准的——“一切都很糟糕。 告诉我问题是什么。”
我立刻想起了3英寸半驱动器时代的一件轶事：
拉默来找黑客。
- 对我来说没有任何作用，请告诉我问题出在哪里。
-DNA中...
但当然，这不是解决性能事件的方法。 “他们可能不理解我们“ （和）。 我们需要弄清楚。
好吧，让我们来挖掘一下。 也许结果会积累一些东西。
调查开始
所以，用肉眼就能立即看到什么，甚至不需要解释。
1) 不使用 JOIN。 这很糟糕，尤其是当连接数超过一个时。
2）但更糟糕的是关联子查询和聚合。 这真是太糟了。
这当然是不好的。 但这只是一方面。 另一方面，这很好，因为问题显然有解决方案和可以改进的要求。
不要去找算命先生 (C)。
查询计划并不复杂，但很有指示性：
执行计划
像往常一样，最有趣和最有用的是在开头和结尾。
嵌套循环（成本=935.84..479763226.18行=3322宽度=135）（实际时间=31.536..8220420.295行=8111656循环=1）
规划时间：3.807 ms
执行时间：8222351.640 毫秒
完成时间超过2小时。
需要时间的错误假设
假设 1 - 优化器犯了错误并制定了错误的计划。
为了可视化执行计划，我们将使用该网站 。 然而，该网站没有显示任何有趣或有用的内容。 乍一看和第二眼，没有什么能真正有帮助的。 全面扫描是否可能是最小的。 前进。
假设2-从自动真空侧对底座的冲击，需要摆脱制动器。
但 autovacuum 守护进程表现良好，没有长期挂起的进程。 没有严重的负载。 我们需要寻找其他东西。
假设3——统计数据已经过时，一切都需要重新计算
再说一次，不是那样。 统计数据是最新的。 考虑到 autovacuum 不存在问题，这并不奇怪。
让我们开始优化
主表'wdata'当然不小，将近3万条记录。
全盘扫描遵循的就是这张表。
哈希条件：((w."SPENT_ID" = s."SPENT_ID") AND ((SubPlan 1) = s."SPENT_DATE"))
-> 顺序扫描 在wdata w上（成本= 0.00..574151.49行= 26886249宽度= 46）（实际时间= 0.005..8153.565行= 26873950循环= 1）
我们做标准的事情：“来吧，让我们做一个索引，一切都会顺利。”
在“SPENT_ID”字段上创建索引
结果：
使用索引查询执行计划
嗯，有帮助吗？
是： 8 222 351.640 毫秒 （2小时多一点）
后： 6 985 431.575 毫秒（近 2 小时）
一般来说，同一个苹果，侧面看。
让我们记住经典：
“你有同样的，但没有翅膀吗？ 会寻求”。
原则上，这可以称得上是一个好的结果，嗯，不好，但可以接受。 至少，向客户提供一份大型报告，描述已经完成了多少工作以及为什么所做的事情是好的。
但距离最终决定仍然遥远。 非常远。
现在最有趣的事情 - 我们继续优化，我们将完善请求
第一步 - 使用 JOIN
重写的请求现在看起来像这样（好吧，至少更漂亮):
使用 JOIN 进行查询选择
p.“PARAMETER_ID”作为parameter_id，
pd."PD_NAME" AS pd_name,
pd.“CUSTOMER_PARTNUMBER”AS customer_partnumber，
w.“LRM”作为 LRM，
w.“LOTID”作为lotid，
w.“RTD_VALUE”作为RTD_值，
w.“LOWER_SPEC_LIMIT” AS lower_spec_limit，
w.“UPPER_SPEC_LIMIT” AS upper_spec_limit，
p.“TYPE_CALCUL”AS type_calcul，
s.“SPENT_NAME”作为花名，
s.“SPENT_DATE”作为花费日期，
提取（“SPENT_DATE”中的年份）AS年份，
提取（“SPENT_DATE”中的月份）作为月份，
s."REPORT_NAME" AS 报告名称，
p.“STPM_NAME”AS stpm_name，
p.“CUSTOMERPARAM_NAME” AS customerparam_name
FROM wdata w INNER JOIN 花费了 s ON w.“SPENT_ID”=s.”“SPENT_ID”
INNER JOIN pmtr p ON p.“PARAMETER_ID” = w.“PARAMETER_ID”
INNER JOIN花费_pd sp ON s.“SPENT_ID”= sp.“SPENT_ID”
INNER JOIN pd pd ON pd.“PD_ID” = sp.“PD_ID”
在
s.“SPENT_DATE”>=“2018-07-01”并且 s.“SPENT_DATE”<=“2018-09-30”并且
s.“SPENT_DATE” = (SELECT MAX(s2.“SPENT_DATE”)
FROM wdata w2 INNER JOIN 在 w2.“SPENT_ID”=s2.“SPENT_ID”上花费了 s2
内连接 wdata w
ON w2.“LRM” = w.“LRM” );
规划时间：2.486 ms
执行时间：1223680.326 毫秒
所以，第一个结果。
是： 6 毫秒（近 985 小时）。
后： 1 223 680.326 毫秒（仅 20 多分钟）。
好结果。 原则上，我们再次可以就此打住。 但这太无趣了，你根本停不下来。
BECAUSE
第二步 - 删除相关子查询
更改后的请求文本：
没有相关子查询选择
p.“PARAMETER_ID”作为parameter_id，
pd."PD_NAME" AS pd_name,
pd.“CUSTOMER_PARTNUMBER”AS customer_partnumber，
w.“LRM”作为 LRM，
w.“LOTID”作为lotid，
w.“RTD_VALUE”作为RTD_值，
w.“LOWER_SPEC_LIMIT” AS lower_spec_limit，
w.“UPPER_SPEC_LIMIT” AS upper_spec_limit，
p.“TYPE_CALCUL”AS type_calcul，
s.“SPENT_NAME”作为花名，
s.“SPENT_DATE”作为花费日期，
提取（“SPENT_DATE”中的年份）AS年份，
提取（“SPENT_DATE”中的月份）作为月份，
s."REPORT_NAME" AS 报告名称，
p.“STPM_NAME”AS stpm_name，
p.“CUSTOMERPARAM_NAME” AS customerparam_name
FROM wdata w INNER JOIN 花费 s ON s.“SPENT_ID” = w.“SPENT_ID”
INNER JOIN pmtr p ON p.“PARAMETER_ID” = w.“PARAMETER_ID”
INNER JOIN花费_pd sp ON s.“SPENT_ID”= sp.“SPENT_ID”
INNER JOIN pd pd ON pd.“PD_ID” = sp.“PD_ID”
INNER JOIN (SELECT w2.“LRM”, MAX(s2.“SPENT_DATE”)
FROM 花费的 s2 INNER JOIN wdata w2 ON s2.“SPENT_ID” = w2.“SPENT_ID”
按 w2.“LRM”分组
) md on w.“LRM” = md.“LRM”
在
s."SPENT_DATE" >= '2018-07-01' 并且 s."SPENT_DATE" <= '2018-09-30';
规划时间：2.291 ms
执行时间：165021.870 毫秒
是： 1 223 680.326 毫秒（仅 20 多分钟）。
后： 165 021.870 毫秒（仅 2 分钟多一点）。
这已经很好了。
然而，正如英国人所说“但是，总有一个但是” 太好的结果自然会引起怀疑。 这里不对劲。
关于纠正查询以消除相关子查询的假设是正确的。 但您需要稍微调整它才能使最终结果正确。
结果，第一个中间结果：
没有相关子查询的编辑查询选择
p.“PARAMETER_ID”作为parameter_id，
pd."PD_NAME" AS pd_name,
pd.“CUSTOMER_PARTNUMBER”AS customer_partnumber，
w.“LRM”作为 LRM，
w.“LOTID”作为lotid，
w.“RTD_VALUE”作为RTD_值，
w.“LOWER_SPEC_LIMIT” AS lower_spec_limit，
w.“UPPER_SPEC_LIMIT” AS upper_spec_limit，
p.“TYPE_CALCUL”AS type_calcul，
s.“SPENT_NAME”作为花名，
s.“SPENT_DATE”作为花费日期，
提取（s.“SPENT_DATE”中的年份）AS年份，
提取（s.“SPENT_DATE”中的月份）作为月份，
s."REPORT_NAME" AS 报告名称，
p.“STPM_NAME”AS stpm_name，
p.“CUSTOMERPARAM_NAME” AS customerparam_name
FROM wdata w INNER JOIN 花费 s ON s.“SPENT_ID” = w.“SPENT_ID”
INNER JOIN pmtr p ON p.“PARAMETER_ID” = w.“PARAMETER_ID”
INNER JOIN花费_pd sp ON s.“SPENT_ID”= sp.“SPENT_ID”
INNER JOIN pd pd ON pd.“PD_ID” = sp.“PD_ID”
INNER JOIN ( SELECT w2.“LRM”, MAX(s2.“SPENT_DATE”) AS “SPENT_DATE”
FROM 花费的 s2 INNER JOIN wdata w2 ON s2.“SPENT_ID” = w2.“SPENT_ID”
按 w2.“LRM”分组
) md ON md.“SPENT_DATE” = s.“SPENT_DATE” AND md.“LRM” = w.“LRM”
在
s."SPENT_DATE" >= '2018-07-01' 并且 s."SPENT_DATE" <= '2018-09-30';
规划时间：3.192 ms
执行时间：208014.134 毫秒
所以，我们最终得到的是第一个可以接受的结果，向客户展示这并不丢脸：
开始于： 8 222 351.640 ms（超过2小时）
我们设法达到：1 毫秒（略多于 223 分钟）。
结果（临时）： 208 014.134 毫秒（仅 3 分钟多一点）。
Отличныйрезультат。
总
我们本来可以停在那里。
但......
食欲是随着吃东西而来的。 行走的人，才能掌握道路。 任何结果都是中间的。 停下来就死了。 ETC。
我们继续优化。
好想法。 特别是考虑到客户甚至不介意。 甚至强烈支持它。
因此，是时候重新设计数据库了。 查询结构本身无法再优化（尽管后来发现，有一个选项可以确保一切实际上都会失败）。 但开始优化和开发数据库设计已经是一个非常有前途的想法。 最重要的是有趣。 再次，记住你的青春。 毕竟，我并没有立即成为一名 DBA，我是作为一名程序员（BASIC、汇编、C、双加 C、Oracle、plsql）长大的。 当然，这是一个有趣的话题，适合单独的回忆录;-)。
不过，我们不要分心。
因此，
或者也许分区会对我们有帮助？
剧透 - “是的，它有帮助，包括优化性能。”
但这是一个完全不同的故事...
待续…
来源： habr.com