Background
我正在从 RDS 上的 Postgres 10.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
;
结果
目前,使用平面客户端 pandas 数据帧,我可以在网络传输和服务器端缩减采样后的 10 秒内执行这些聚合。但是,在客户端,这些聚合至少需要 1 分钟才能运行。
(解释分析)计划可在此处获得:https://explain.depesz.com/s/0gAu
问题
- 是否有快速计算这些聚合的标准方法?
- 有没有办法让 Postgres 并行计算这些事情,每组 1 个工人?
任何见解或讨论都非常受欢迎 - 感谢您的阅读。
执行计划有一些我不明白的地方:
-
如果没有计划并行工作器,为什么会有
Gather
节点?从loops
我预计有两个工人。 -
为什么 expain.depesz.com 不计算底部节点的895693次迭代(也许和我一样被上面弄糊涂了)?
尽管如此,可以立即检测到一些问题:
-
有可怕的错误估计(725 而不是实际895693行!
-
您的大部分时间都花在溢出到磁盘的某种方式上。
因此,您可以在不重写查询的情况下进行以下改进:
-
增加
work_mem
,直到排序quicksort memory
。 这应该是最大的收获。您不必全局增加它,您可以运行如下内容:
BEGIN; SET LOCAL work_mem = '1GB'; SELECT /* your query */; COMMIT;
-
一些表格似乎有陈旧的统计数据。尝试
ANALYZE
所有有问题的表格,也许这有一些好处。 -
您可以通过避免引导不良的嵌套循环连接来多刮几秒钟。也许
ANALYZE
会照顾到这一点。作为最后的手段,您可以简单地禁用该查询的嵌套循环,方法是使用我为上面
work_mem
展示的相同技巧为一个查询设置enable_nestloop = off
。
对分区表的扫描不是你的问题,所以你不必担心并行化(PostgreSQL v11 已经变得更加智能)。
如果上述所有内容都不能使查询足够快,则可以考虑使用实例化视图。然后你会得到稍微过时的数据,但速度很快。