小编典典

需要SQL优化（也许是DISTINCT ON的原因吗？）

sql

我当前的查询如下所示：

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
- 第一段（第1-6行）：
- 从点数组创建表以用于…
- 第二段（第8-14行）：
- 对于points表中的每个点：从表中获取一个随机（！）groundtruth点measurement，其距离<distance
- 将那些元组保存在gtps表中
- 第三段（第16-19行）：
- 对于表中的每个groundtruth值gtps：获取所有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-tuple与anchor_id该groundtruth的每个possilbe连接。
两个不同的输入值可以具有相同的对应groundtruth值。
两个不同的groundtruth-inputPoint-tuple可以具有相同的 anchor_id
两个相同的groundtruth-inputPoint-tuple必须具有不同的anchor_ids

基准（两个输入值）：

1-6行：16ms
第8-14行：48ms
16-19行：600ms

详细说明：

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个输入值）从大约670ms减少到大约530ms。因此，目前这还不是主要的影响。

如果更容易/更快，也可以运行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

使用此查询，它的速度非常快（ 15ms ），但有很多不足：

我只需要为每个随机行 ps.p
这两个数组彼此属于。意思是：里面物品的顺序很重要！
需要对这两个数组进行过滤（随机）：
对于anchor_id出现多次的数组中的每个数组：保留一个随机数组，然后删除所有其他数组。这也意味着id从id-array中删除每个删除的对应项anchor_id

如果anchor_id并且id可以将其存储在元组数组中，那也很好。例如：（{[4,1],[6,3],[4,2],[8,5],[4,4]}约束：每个元组都是唯一的，每个id（在示例中==第2个值）都是唯一的，anchor_ids不是唯一的）。本示例显示查询，其中没有必须仍然应用的过滤器。应用过滤器后，它看起来像是{[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 ，那么它还必须保留数组的索引1、4和7 id（当然删除所有其他索引）

阅读 205

2021-05-23

共1个答案

小编典典

如果anchor_id和id可以存储在元组数组中，那也很好。

多维数组的聚集函数

我想您为此创建了一个 二维数组 。这比起来更容易处理ARRAY of record。Standardarray_agg()无法聚合多维数组。但是您可以为此轻松地编写自己的聚合函数：

CREATE AGGREGATE array_agg_mult (anyarray)  (
    SFUNC     = array_cat
   ,STYPE     = anyarray
   ,INITCOND  = '{}'
);

对于出现多次的数组中的每个anchor_id：保留一个随机数，然后删除所有其他。这还意味着从每个已删除的anchor_id的ID数组中删除相应的ID

询问

SELECT DISTINCT ON (p)
       p, groundtruth, array_agg_mult(ARRAY[ARRAY[anchor_id, id]]) AS ids
FROM (
   SELECT DISTINCT ON (ps.p, m.groundtruth, m.anchor_id)
          ps.p, m.groundtruth, m.anchor_id, m.id
   FROM  (SELECT unnest(point_array) AS p) AS ps
   JOIN   measurement m ON ST_DWithin(ps.p, m.groundtruth, distance)
   ORDER  BY ps.p, m.groundtruth, m.anchor_id, random()
   ) x
GROUP  BY p, groundtruth
ORDER  BY p, random();

子查询每个x都不同，如果有多个同级，则选择一个随机行。这样，连接保持完整。anchor_id``(p, groundtruth)``anchor_id - id
外部查询聚合您想要的二维数组，按排序anchor_id。如果要anchor_id随机订购，请再次使用随机：
```
array_agg_mult(ARRAY[ARRAY[anchor_id, id]] ORDER BY random())
```
最后，每个人DISTINCT ON只能随机选择1groundtruth个p。

2021-05-23