相关的,前置问题:
按值(而非列)分组后从组中选择随机条目?
我的当前查询如下:
WITH
points AS (
SELECT unnest(array_of_points) AS p
),
gtps AS (
SELECT DISTINCT ON(points.p)
points.p, m.groundtruth
FROM measurement m, points
WHERE st_distance(m.groundtruth, points.p) < distance
ORDER BY points.p, RANDOM()
)
SELECT DISTINCT ON(gtps.p, gtps.groundtruth, m.anchor_id)
m.id, m.anchor_id, gtps.groundtruth, gtps.p
FROM measurement m, gtps
ORDER BY gtps.p, gtps.groundtruth, m.anchor_id, RANDOM()
语义:
有两个输入值:
- 第4行:一个点数组
array_of_points
- 第12行:一个双精度数:
distance
- 第4行:一个点数组
第一段(第1-6行):
- 从点数组中创建一个表,用于...
第二段(第8-14行):
- 对于
points
表内的每个点:从measurement
表中获取一个随机(!)的groundtruth
点,该点距离小于distance
- 将这些元组保存在
gtps
表中
- 对于
第三段(第16-19行):
- 对于
gtps
表内的每个groundtruth
值:获取所有anchor_id
值并... - 如果一个
anchor_id
值不唯一:则选择一个随机的
- 对于
输出:
id
、anchor_id
、groundtruth
、p
(来自array_of_points
的输入值)
示例表格:
id | anchor_id | groundtruth | data
-----------------------------------
1 | 1 | POINT(1 4) | ...
2 | 3 | POINT(1 4) | ...
3 | 8 | POINT(1 4) | ...
4 | 6 | POINT(1 4) | ...
-----------------------------------
5 | 2 | POINT(3 2) | ...
6 | 4 | POINT(3 2) | ...
-----------------------------------
7 | 1 | POINT(4 3) | ...
8 | 1 | POINT(4 3) | ...
9 | 6 | POINT(4 3) | ...
10 | 7 | POINT(4 3) | ...
11 | 3 | POINT(4 3) | ...
-----------------------------------
12 | 1 | POINT(6 2) | ...
13 | 5 | POINT(6 2) | ...
例子结果:
id | anchor_id | groundtruth | p
-----------------------------------------
1 | 1 | POINT(1 4) | POINT(1 0)
2 | 3 | POINT(1 4) | POINT(1 0)
4 | 6 | POINT(1 4) | POINT(1 0)
3 | 8 | POINT(1 4) | POINT(1 0)
5 | 2 | POINT(3 2) | POINT(2 2)
6 | 4 | POINT(3 2) | POINT(2 2)
1 | 1 | POINT(1 4) | POINT(4 8)
2 | 3 | POINT(1 4) | POINT(4 8)
4 | 6 | POINT(1 4) | POINT(4 8)
3 | 8 | POINT(1 4) | POINT(4 8)
12 | 1 | POINT(6 2) | POINT(7 3)
13 | 5 | POINT(6 2) | POINT(7 3)
1 | 1 | POINT(4 3) | POINT(9 1)
11 | 3 | POINT(4 3) | POINT(9 1)
9 | 6 | POINT(4 3) | POINT(9 1)
10 | 7 | POINT(4 3) | POINT(9 1)
正如您所见:
- 每个输入值都可以有多个相等的
groundtruth
值。 - 如果一个输入值有多个
groundtruth
值,则这些值必须全部相等。 - 每个groundtruth-inputPoint元组都与该groundtruth的每个可能的
anchor_id
相连接。 - 两个不同的输入值可以具有相同的对应
groundtruth
值。 - 两个不同的groundtruth-inputPoint元组可以具有相同的
anchor_id
。 - 两个相同的groundtruth-inputPoint元组必须具有不同的
anchor_id
。
基准测试(针对两个输入值):
- 第1-6行:16ms
- 第8-14行:48ms
- 第16-19行:600ms
EXPLAIN VERBOSE:
Unique (cost=11119.32..11348.33 rows=18 width=72)
Output: m.id, m.anchor_id, gtps.groundtruth, gtps.p, (random())
CTE points
-> Result (cost=0.00..0.01 rows=1 width=0)
Output: unnest('{0101000000EE7C3F355EF24F4019390B7BDA011940:01010000003480B74082FA44402CD49AE61D173C40}'::geometry[])
CTE gtps
-> Unique (cost=7659.95..7698.12 rows=1 width=160)
Output: points.p, m.groundtruth, (random())
-> Sort (cost=7659.95..7679.04 rows=7634 width=160)
Output: points.p, m.groundtruth, (random())
Sort Key: points.p, (random())
-> Nested Loop (cost=0.00..6565.63 rows=7634 width=160)
Output: points.p, m.groundtruth, random()
Join Filter: (st_distance(m.groundtruth, points.p) < m.distance)
-> CTE Scan on points (cost=0.00..0.02 rows=1 width=32)
Output: points.p
-> Seq Scan on public.measurement m (cost=0.00..535.01 rows=22901 width=132)
Output: m.id, m.anchor_id, m.tag_node_id, m.experiment_id, m.run_id, m.anchor_node_id, m.groundtruth, m.distance, m.distance_error, m.distance_truth, m."timestamp"
-> Sort (cost=3421.18..3478.43 rows=22901 width=72)
Output: m.id, m.anchor_id, gtps.groundtruth, gtps.p, (random())
Sort Key: gtps.p, gtps.groundtruth, m.anchor_id, (random())
-> Nested Loop (cost=0.00..821.29 rows=22901 width=72)
Output: m.id, m.anchor_id, gtps.groundtruth, gtps.p, random()
-> CTE Scan on gtps (cost=0.00..0.02 rows=1 width=64)
Output: gtps.p, gtps.groundtruth
-> Seq Scan on public.measurement m (cost=0.00..535.01 rows=22901 width=8)
Output: m.id, m.anchor_id, m.tag_node_id, m.experiment_id, m.run_id, m.anchor_node_id, m.groundtruth, m.distance, m.distance_error, m.distance_truth, m."timestamp"
解释分析:
Unique (cost=11119.32..11348.33 rows=18 width=72) (actual time=548.991..657.992 rows=36 loops=1)
CTE points
-> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.004..0.011 rows=2 loops=1)
CTE gtps
-> Unique (cost=7659.95..7698.12 rows=1 width=160) (actual time=133.416..146.745 rows=2 loops=1)
-> Sort (cost=7659.95..7679.04 rows=7634 width=160) (actual time=133.415..142.255 rows=15683 loops=1)
Sort Key: points.p, (random())
Sort Method: external merge Disk: 1248kB
-> Nested Loop (cost=0.00..6565.63 rows=7634 width=160) (actual time=0.045..46.670 rows=15683 loops=1)
Join Filter: (st_distance(m.groundtruth, points.p) < m.distance)
-> CTE Scan on points (cost=0.00..0.02 rows=1 width=32) (actual time=0.007..0.020 rows=2 loops=1)
-> Seq Scan on measurement m (cost=0.00..535.01 rows=22901 width=132) (actual time=0.013..3.902 rows=22901 loops=2)
-> Sort (cost=3421.18..3478.43 rows=22901 width=72) (actual time=548.989..631.323 rows=45802 loops=1)
Sort Key: gtps.p, gtps.groundtruth, m.anchor_id, (random())"
Sort Method: external merge Disk: 4008kB
-> Nested Loop (cost=0.00..821.29 rows=22901 width=72) (actual time=133.449..166.294 rows=45802 loops=1)
-> CTE Scan on gtps (cost=0.00..0.02 rows=1 width=64) (actual time=133.420..146.753 rows=2 loops=1)
-> Seq Scan on measurement m (cost=0.00..535.01 rows=22901 width=8) (actual time=0.014..4.409 rows=22901 loops=2)
Total runtime: 834.626 ms
运行时应该有大约100到1000个输入值,目前需要花费35到350秒,这太长了。
我已经尝试去除RANDOM()
函数。这将运行时间(对于2个输入值)从约670毫秒减少至约530毫秒。因此,目前它不是主要影响因素。
如果更容易/更快捷的话,也可以运行2或3个单独的查询并在软件中执行一些部分(它正在运行于Ruby on Rails服务器上)。例如随机选择?!
正在进行的工作:
SELECT
m.groundtruth, ps.p, ARRAY_AGG(m.anchor_id), ARRAY_AGG(m.id)
FROM
measurement m
JOIN
(SELECT unnest(point_array) AS p) AS ps
ON ST_DWithin(ps.p, m.groundtruth, distance)
GROUP BY groundtruth, ps.p
使用这个查询非常快(15毫秒),但是有很多缺失:
- 我只需要每个
ps.p
的一个随机行。 - 这两个数组相互属于。也就是说:其中的项目顺序很重要!
- 这两个数组需要进行过滤(随机):
对于数组中出现多次的每个anchor_id
:保留一个随机的,并删除所有其他的。这还意味着对于每个已删除的anchor_id
,需要从id
数组中删除相应的id
。
如果能够将 anchor_id
和 id
存储在元组数组中,那就更好了。例如:{[4,1],[6,3],[4,2],[8,5],[4,4]}
(约束条件:每个元组都是唯一的,每个 id(== 示例中的第二个值)都是唯一的,anchor_id 不唯一)。该示例显示了未应用筛选器的查询。应用筛选器后,它将如下所示:{[6,3],[4,4],[8,5]}
。
工作正在进行中 II:
SELECT DISTINCT ON (ps.p)
m.groundtruth, ps.p, ARRAY_AGG(m.anchor_id), ARRAY_AGG(m.id)
FROM
measurement m
JOIN
(SELECT unnest(point_array) AS p) AS ps
ON ST_DWithin(ps.p, m.groundtruth, distance)
GROUP BY ps.p, m.groundtruth
ORDER BY ps.p, RANDOM()
现在它提供了不错的结果,并且仍然非常快:16ms
只剩下一件事要做:
ARRAY_AGG(m.anchor_id)
已经被随机化,但是:- 它包含大量重复的条目,所以:
- 我想在它上面使用类似 DISTINCT 的东西,但是:
- 它必须与
ARRAY_AGG(m.id)
同步。这意味着:
如果 DISTINCT 命令保留anchor_id
数组的索引1、4和7,则它也必须保留id
数组的索引1、4和7(当然还要删除所有其他的索引)。
explain analyze
命令以查看实际耗时情况。 - user330315explain verbose
下方。解释详细 - Benjamin MCROSS JOIN
也是如此 - 但是,如果您可以预先计算坐标的边界和最大距离(包括points
),则可以进行相关联。外部查询可能会以交叉连接的方式误导您 - 为什么不基于先前收集的点呢?我不确定使用RANDOM()
对结果进行排序是否真正实现了您想要的效果,但我不确定这是否不可取... - Clockwork-Muse