为什么有组的聚合这么慢?

Question

我在Redshift实例中有一个表[order]，行数为780,000。在表上运行下面的group by子句需要60秒以上的时间。在MSSQL中，完全相同的查询需要1秒。任何关于为什么红移需要这么长时间以及如何改进查询的建议都将非常感谢。

select
salesorderid
,max(orderid)           as max_order_id
,min(latestdelivery)    as min_latestdelivery
,max(latestdelivery)    as max_latestdelivery
,min(sourceid)          as min_sourceid
,max(sourceid)          as max_sourceid
,min(salesitem)         as min_salesitem
,max(salesitem)         as max_salesitem
,min(qty)               as min_qty
,max(qty)               as max_qty
,min(weight)            as min_weight
,max(weight)            as max_weight
,min(refb)              as min_refb
,max(refb)              as max_refb
,min(blocked)           as min_blocked
,max(blocked)           as max_blocked
,min(updatemode)        as min_updatemode
from public.order o
where o.datecreated >= getdate() - interval '24 month'
group by salesorderid;

解释:

XN HashAggregate  (cost=35513.57..52310.29 rows=419918 width=99)
->  XN Seq Scan on "order" o  (cost=0.00..9738.60 rows=606470 width=99)
Filter: (datecreated >= '2019-10-17 11:52:14'::timestamp without time zone)

Answer 1

虽然这里有一些不理想的事情，但很可能导致这花费1分钟的原因不是SQL。我以后会逐项列出我看到的问题。

你的源表少于1M行-非常小的红移标准。排序顺序或元数据过时不太可能导致大问题。查询是一个简单的聚合，解释计划显示Redshift期望这个"应该"。用不到一秒钟的时间。所有这些都导致我关注外部混杂因素。

我将假设您从工作台而不是通过系统表数据测量查询时间。如果不是这样，请告诉我们。如果是这样，您将查看整个数据库的往返过程，而不仅仅是执行时间。要完成查询，需要执行多个步骤。它需要编译、通过WLM队列调度、执行并返回结果。这些步骤中的任何一个都可能花费很长时间，每个步骤都有许多原因。

因此，第一步是找出查询在每个步骤中所花费的时间。我喜欢从中间开始(排队和执行，因为这通常是事情变慢的地方，但并非总是如此)。STL_WLM_QUERY系统表将显示查询在队列中花费的时间以及执行时间。一种可能性是，您的查询在队列中等待挂起，这将使您应该执行此操作。它还将显示总执行时间，以便您可以看到运行所需的时间(这是EXPLAIN计划所关注的全部内容)。这里可能有问题，但我会继续读下去。

如果事情很快通过队列并在合理的时间内执行，那么我们需要查看编译和返回。SVL_COMPILE将显示查询编译所需的时间，但考虑到该查询的简单性，这是非常不可能的，这是您的问题所在。

返回步骤中可能存在原因。EXPLAIN期望您接收有意义宽度的419K行-这可能代表相当大的数据量。如果你的Redshift和你自己之间的网络带宽有限，那么大量的数据可能会占用你大量的运行时间来传输。STL_RETURN保存了Redshift在这一步中的数据。您还可以通过重新运行查询并将结果放入临时表中来测试这一点，看看这是否极大地改善了运行时间。这只是我的猜测。

正确的方法是收集时间花在哪里的数据，并根据这些信息采取行动。如果在这一点上需要更多的指导，请反馈这个问题。

现在对查询本身进行一些观察。我建议不要使用时间戳作为表的分发键。您不太可能使用JOIN或GROUP BY时间戳，这对执行查询没有任何帮助。由于您是按salesorderid分组的，因此对于分发键来说，这可能是一个不错的选择。此外，您应该使用常见的WHERE子句列作为排序键。您已经这样做了，但将其作为salesorderid之后的第二个排序键。这可能会窃取数据创建的所有权力，并阻止它提供任何好处。我建议您只使用salesorderid作为此排序键，但这是基于查看您的查询中的1个，因此将此作为一组信息贫乏的建议。这一切都表明，考虑到桌子的大小，这些都不太可能产生太大的影响。这里没有足够的数据让这些键产生大的影响。