Will the output of the following two queries be same and why the performance of first one is considerably better than the second one?

huangapple go评论66阅读模式
英文:

Will the output of the following two queries be same and why the performance of first one is considerably better than the second one?

问题

我相信这两个查询的输出将始终相同。查询1能够在不到5秒内执行,而查询2需要大约4分钟。表a中的总记录数约为2000万。查询1更快的原因可能是以下几点:

  1. Left Join 顺序不同: 在查询1中,您首先与ve表进行左连接,然后与其他表连接,这可能导致更有效的查询计划。查询2在连接ve表之前与其他表连接,可能导致查询计划的不同,从而影响性能。

  2. 索引优化: 查询性能可能还取决于表的索引和数据分布。如果在查询1的连接顺序中使用了有效的索引,而在查询2中没有,那么查询1可能会更快。

  3. 数据分布差异: 表a中的数据分布可能会导致性能差异。如果查询1的连接条件导致较小的中间结果集,而查询2的连接条件导致较大的中间结果集,那么查询1可能会更快。

要深入了解性能差异的原因,您可能需要查看查询计划和表的索引结构,以进行详细的性能分析。此外,您还可以考虑尝试在查询2中更改连接顺序以优化性能。

英文:

Query 1:

select *  
from a
              LEFT JOIN p ON a.product_id = p.web_id
              LEFT JOIN m ON p.manufacturer_id = m.web_id
              LEFT JOIN a add ON a.address_id = add.web_id
              LEFT JOIN c ON c.web_id = a.owner_id
              LEFT JOIN a ca ON c.main_address_id = ca.web_id
              LEFT JOIN d ON a.web_id = d.equipment_id
              LEFT JOIN s ON d.last_segment_id = s.web_id
              LEFT JOIN oh ON s.order_header_id = oh.web_id
              LEFT JOIN f ON s.folder_id = f.web_id
              LEFT JOIN ve on a.web_id = ve.equipment_id
where ve.date_updated >= now() - interval '10000 min'

Query 2:

select *  
from a
              LEFT JOIN p ON a.product_id = p.web_id
              LEFT JOIN m ON p.manufacturer_id = m.web_id
			  LEFT JOIN ve on a.web_id = ve.equipment_id
              LEFT JOIN a add ON a.address_id = add.web_id
              LEFT JOIN c ON c.web_id = a.owner_id
              LEFT JOIN a ca ON c.main_address_id = ca.web_id
              LEFT JOIN d ON a.web_id = d.equipment_id
              LEFT JOIN s ON d.last_segment_id = s.web_id
              LEFT JOIN oh ON s.order_header_id = oh.web_id
              LEFT JOIN f ON s.folder_id = f.web_id
where ve.date_updated >= now() - interval '10000 min'

The only difference between the two queries is the position of the left join with ve table.

Am I right to believe that the output of both the queries will always be the same?

The query 1 is able to execute in under 5 seconds while the query 2 goes on for about 4mins. The total number of records in table a is around 20million. Is there a reason why query 1 is faster?

答案1

得分: 1

连接条件的顺序不重要,查询在语义上是相同的。执行时间差异必须与 join_collapse_limit 有关:

> 计划器会将显式的 JOIN 结构(除了 FULL JOIN)重写为 FROM 项目列表,只要结果的项目数不超过此值。较小的值会减少计划时间,但可能会产生较差的查询计划。
>
> 默认情况下,此变量设置与 from_collapse_limit 相同,适用于大多数用途。将其设置为 1 可防止重排序显式的 JOIN。因此,查询中指定的显式连接顺序将是关系连接的实际顺序。由于查询计划器并不总是选择最佳的连接顺序,高级用户可以选择将此变量暂时设置为 1,然后明确指定他们所期望的连接顺序。更多信息请参阅 Section 14.3

请注意,此查询连接超过了八个表。

如果您有很多类似的连接,并且不想重写它们,也不介意优化器花费更多时间,您可以增加 join_collapse_limitfrom_collapse_limit

英文:

The order of the join conditions does not matter, and the queries are semantically identical. The difference in execution time must be connected to join_collapse_limit:

> The planner will rewrite explicit JOIN constructs (except FULL JOINs) into lists of FROM items whenever a list of no more than this many items would result. Smaller values reduce planning time but might yield inferior query plans.
>
> By default, this variable is set the same as from_collapse_limit, which is appropriate for most uses. Setting it to 1 prevents any reordering of explicit JOINs. Thus, the explicit join order specified in the query will be the actual order in which the relations are joined. Because the query planner does not always choose the optimal join order, advanced users can elect to temporarily set this variable to 1, and then specify the join order they desire explicitly. For more information see Section 14.3.

Note that the query joins more than eight tables.

If you have many joins like that, and you don't want to rewrite them, and you don't mind the optimizer to take more time, you can increase join_collapse_limit and from_collapse_limit.

huangapple
  • 本文由 发表于 2023年6月15日 16:54:49
  • 转载请务必保留本文链接:https://go.coder-hub.com/76480785.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定