postgresql - CTE RECURSIVE 优化,怎么做?
问题描述
我需要优化公共WITH RECURSIVE
查询的性能...我们可以限制树的深度并在许多更新中分解,还可以更改表示(使用数组)...我尝试了一些选项,但也许有一个“经典优化解决方案”,我没有意识到。
所有细节
有一个t_up
要更新的表,带有一个复合主键(pk1,pk2),一个属性attr
和一组对主键的引用......以及一个未嵌套的表示t_scan
,带有引用;像这样:
pk1 | pk2 | attr | ref_pk1 | ref_pk2
n | 123 | 1 | |
n | 456 | 2 | |
r | 123 | 1 | w | 123
w | 123 | 5 | n | 456
r | 456 | 2 | n | 123
r | 123 | 1 | n | 111
n | 111 | 4 | |
... | ...| ... | ... | ...
没有循环。
UPDATE t_up SET x = pairs
FROM (
WITH RECURSIVE tree as (
SELECT pk1, pk2, attr, ref_pk1, ref_pk2,
array[array[0,0]]::bigint[] as all_refs
FROM t_scan
UNION ALL
SELECT c.pk1, c.pk2, c.attr, c.ref_pk1, c.ref_pk2
,p.all_refs || array[c.attr,c.pk2]
FROM t_scan c JOIN tree p
ON c.ref_pk1=p.pk1 AND c.ref_pk2=p.pk2 AND c.pk2!=p.pk2
AND array_length(p.all_refs,1)<5 -- 5 or 6 avoiding endless loops
)
SELECT pk1, pk2, array_agg_cat(all_refs) as pairs
FROM (
SELECT distinct pk1, pk2, all_refs
FROM tree
WHERE array_length(all_refs,1)>1 -- ignores initial array[0,0].
) t
GROUP BY 1,2
ORDER BY 1,2
) rec
WHERE rec.pk1=t_up.pk1 AND rec.pk2=t_up.pk2
;
去测试:
CREATE TABLE t_scan(
pk1 char,pk2 bigint, attr bigint,
ref_pk1 char, ref_pk2 bigint
);
INSERT INTO t_scan VALUES
('n',123, 1 ,NULL,NULL),
('n',456, 2 ,NULL,NULL),
('r',123, 1 ,'w' ,123),
('w',123, 5 ,'n' ,456),
('r',456, 2 ,'n' ,123),
('r',123, 1 ,'n' ,111),
('n',111, 4 ,NULL,NULL);
仅运行rec
您将获得:
pk1 | pk2 | pairs
-----+-----+-----------------
r | 123 | {{0,0},{1,123}}
r | 456 | {{0,0},{2,456}}
w | 123 | {{0,0},{5,123}}
但是,不幸的是,要了解“大数据性能问题”,您需要在真实数据库中查看它……我正在准备一个使用 OpenStreetMap 大数据运行的公共 Github。
解决方案
推荐阅读
- react-native - react-navigation [v6] 抽屉和堆栈嵌套问题
- jquery - 数据表 - 获取自动总和和所有值
- django - 运行多个摄像头的 Django StreamHttpRequest
- swift - SCNGeometry 没有给出正确的顶点数
- javascript - 当电子应用程序在后台工作时打开模式
- c# - 存储更改历史
- r - 如何在数据框中向下填充先前的值,直到 R 中列的最后一个数值?
- python - 以整数格式处理 MMDDYYYY 格式 python
- java - 如果没有 SharedPreferences,onCreate 中的 Android Java 弹出菜单
- java - java - 如何将格式(“dd-M-yyyy kk:mm:ss z”)的字符串转换为java中服务器的LocalDateTime?