sql - Redshift/PostgreSQL 中子查询的 GroupAggregate

Question

我注意到 Redshift 的查询优化器中有一些奇怪的行为，我想知道是否有人可以解释它或指出解决方法。

对于大型 group by 查询，让优化器规划 GroupAggregate 而不是 HashAggregate 非常重要，因此它不会尝试将临时结果放入内存中。一般来说，这对我来说效果很好。但是当我尝试使用该group by作为子查询时，它会切换到HashAggregate。

例如，考虑以下查询。

select install_app_version, user_id, max(platform) as plat
from dailies
group by install_app_version, user_id;

表日报具有排序键(install_app_version、user_id)和distkey(user_id)。因此，GroupAggregate 是可能的，并且查询计划看起来应该像这样。

XN GroupAggregate  (cost=0.00..184375.32 rows=1038735 width=51)
  ->  XN Seq Scan on daily_players  (cost=0.00..103873.42 rows=10387342 width=51)

相反，如果我在任何其他查询的子查询中使用上述内容，我会得到一个 HashAggregate。例如，即使是像

这样简单的事情

select count(1) from
(   select install_app_version, user_id, max(platform) as plat
    from daily_players
    group by install_app_version, user_id
);

有查询计划

XN Aggregate  (cost=168794.32..168794.32 rows=1 width=0)
  ->  XN Subquery Scan derived_table1  (cost=155810.13..166197.48 rows=1038735 width=0)
        ->  XN HashAggregate  (cost=155810.13..155810.13 rows=1038735 width=39)
              ->  XN Seq Scan on daily_players  (cost=0.00..103873.42 rows=10387342 width=39)

无论我在外部查询中做什么，相同的模式都会持续存在。我可以按 install_app_version 和 user_id 进行分组，我可以进行聚合，我根本无法在外部进行分组。即使对内部查询进行排序也不起作用。

在我已经证明的情况下，这并不是什么大问题，但我将几个子查询与它们自己的group by连接起来，对其进行聚合 - 它很快就会失控并且非常没有 GroupAggregate 时速度很慢。

如果有人对查询优化器有了解并且可以回答这个问题，我们将不胜感激!谢谢!

Answer 1

不知道您的问题是否仍然存在，但我将其放在这里是因为我认为其他人可能会感兴趣。

默认情况下，Redshift 似乎使用 HashAggregate 执行 GROUP BY 聚合(即使 GroupAggregate 的条件正确)，并且仅当聚合进行的至少一项计算需要解析才能返回查询时才切换到 GroupAggregate。我的意思是，在前面的示例中，“max(platform) as plat”对于查询的最终“COUNT(1)”结果没有用处。我相信，在这种情况下，MAX() 函数的聚合计算根本不会被计算。

我使用的解决方法是添加一个无用的 HAVING 子句，该子句不执行任何操作但仍需要计算(例如“HAVING COUNT(1)”)。这始终返回 true(因为每个组的 COUNT(1) 至少等于 1，因此也是 true)，但使查询计划能够使用 GroupAggregate。

示例:

EXPLAIN SELECT COUNT(*) FROM (SELECT mycol FROM mytable GROUP BY 1);

XN Aggregate  (cost=143754365.00..143754365.00 rows=1 width=0)
   ->  XN Subquery Scan derived_table1  (cost=141398732.80..143283238.56 rows=188450576 width=0)
         ->  XN HashAggregate  (cost=141398732.80..141398732.80 rows=188450576 width=40)
               ->  XN Seq Scan on mytable  (cost=0.00..113118986.24 rows=11311898624 width=40)


EXPLAIN SELECT COUNT(*) FROM (SELECT mycol FROM mytable GROUP BY 1 HAVING COUNT(1));

XN Aggregate  (cost=171091871.18..171091871.18 rows=1 width=0)
   ->  XN Subquery Scan derived_table1  (cost=0.00..171091868.68 rows=1000 width=0)
         ->  XN GroupAggregate  (cost=0.00..171091858.68 rows=1000 width=40)
               Filter: ((count(1))::boolean = true)
                ->  XN Seq Scan on mytable  (cost=0.00..113118986.24 rows=11311898624 width=40)

这是有效的，因为“mycol”既是“mytable”的 distkey 又是 sortkey。

正如你所看到的，查询计划估计比使用 GroupAggregate 的查询比使用 HashAggregate 的查询成本更高(这一定是查询计划选择 HashAggregate 的原因)。不要相信，在我的示例中，第二个查询的运行速度比第一个查询快 7 倍!最酷的事情是 GroupAggregate 不需要太多内存来计算，因此几乎永远不会执行“基于磁盘的聚合”。

事实上，我意识到使用子查询 GroupAggregate 执行 COUNT(DISTINCT x) 甚至比使用标准 COUNT(DISTINCT x) 更好(在我的示例中，“mycol”是 NOT NULL 列):

EXPLAIN SELECT COUNT(DISTINCT mycol) FROM mytable ;

XN Aggregate  (cost=143754365.00..143754365.00 rows=1 width=72)
->  XN Subquery Scan volt_dt_0  (cost=141398732.80..143283238.56 rows=188450576 width=72)
         ->  XN HashAggregate  (cost=141398732.80..141398732.80 rows=188450576 width=40)
               ->  XN Seq Scan on mytable  (cost=0.00..113118986.24 rows=11311898624 width=40)

3分46秒

EXPLAIN SELECT COUNT(*) FROM (SELECT mycol FROM mytable GROUP BY 1 HAVING COUNT(1));

XN Aggregate  (cost=171091871.18..171091871.18 rows=1 width=0)
   ->  XN Subquery Scan derived_table1  (cost=0.00..171091868.68 rows=1000 width=0)
         ->  XN GroupAggregate  (cost=0.00..171091858.68 rows=1000 width=40)
               Filter: ((count(1))::boolean = true)
               ->  XN Seq Scan on mytable  (cost=0.00..113118986.24 rows=11311898624 width=40)

40秒

希望有帮助!