如何加快分区表上的服务器端聚合

Question

背景

我正在从RDS上的Postgres10.6中的一个规范化的、分区表中生成具有3*10^9行的Tukey盒和晶须图。

首先，我连续使用多个视图，包括聚合步骤和随后的异常值检测步骤。首先，在聚合步骤中，我计算了中位，25%，75%，IQR，(25% - 1.5*IQR)下晶须和(75% + 1.5*IQR)上晶须。其次，在异常点检测步骤中，我在表中搜索位于胡须之外的值。

聚合

create view aggregation as
select
    a.a_name,
    b.b_name,
    c.c_name,
    percentile_cont(0.5) within group (order by d.D) as median,
    etc for 75%, IQR, whiskers
from dtable as d
join atable as a on a.a_id = d.a_id
join etable as e on e.e_id = d.e_id
join ftable as f on f.f_id = e.f_id
join btable as b on b.b_id = f.b_id
join ctable as c on c.c_id = b.c_id
where (d.e_id between 3440500 and 3459500)
and (c.c_name = 'this_c_in_particular')
and (b.b_name in ('first_b', 'second_b', 'third_b'))
group by
a.a_name,
b.b_name,
c.c_name
;

注意，dtable是由e_id分区的。

离群点检测

create view outliers as
select d.*
from dtable as d
join atable, etable, ftable, btable, ctable
join aggregation as agg on
    agg.a_name = atable.a_name,
    agg.b_name = btable.b_name,
    agg.c_name = ctable.c_name
where d.value < agg.lower_whisker or d.value > agg.upper_whisker
;

结果

目前，我可以使用一个扁平的客户端熊猫数据集，在网络传输和服务器端下采样之后的10秒钟内完成这些聚合。然而，在客户端，这些聚合至少需要1分钟才能运行。

(解释分析)计划可在这里获得：https://explain.depesz.com/s/0gAu

问题

1. 是否有一种快速计算这些集合的标准方法？
2. 有没有办法让Postgres并行计算这些东西，每组一个工人？

任何见解或讨论都是非常受欢迎的--谢谢你的阅读。

Answer 1

执行计划有一些我不明白的事情：

• 如果没有计划并行工作人员，为什么会有一个Gather节点？从loops，我希望有两个工人。
• 为什么expain.depesz.com不计算底部节点的895693次迭代(也许它和我一样被上面的内容搞混了)？

尽管如此，还是可以立即发现一些问题：

• 有可怕的错误估计(725行而不是实际行895693行！)
• 您的大部分时间都花在了一种溢出到磁盘上的类型上。

因此，您可以在不重写查询的情况下进行改进：

• 增加work_mem，直到排序为quicksort memory为止。这应该是最大的收获。 你不必在全球范围内增加它，你可以运行这样的东西： 开始；设置本地work_mem = '1GB'；选择/*查询*/；提交；
• 有些表似乎有过时的统计数据。尝试ANALYZE所有有问题的表，也许这会有一些好处。
• 通过避免错误引导的嵌套循环联接，您可能还可以节省几秒钟时间。也许ANALYZE会解决这个问题。 最后，您可以简单地禁用该查询的嵌套循环，方法是为一个查询设置enable_nestloop = off，这与我在上面为work_mem展示的技巧相同。

对分区表的扫描不是您的问题，所以您不必担心并行化(PostgreSQL v11已经变得更聪明了)。

如果上述所有内容都不能使查询足够快，则可以考虑使用物化视图。然后你会得到一些陈腐的数据，但速度很快。