程序员经验分享-hwhale

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

2024-02-25

相关的前一个问题：
按值（而不是列）分组后从组中选择随机条目？ https://stackoverflow.com/questions/15091363/select-a-random-entry-from-a-group-after-grouping-by-a-value-not-column

我当前的查询如下所示：

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()

语义：

  1. 有两个输入值：

    • 第 4 行：点数组array_of_points
    • 第 12 行：双精度数：distance

  2. 第一段（第 1-6 行）：

    • 从点数组创建一个表以用于...

  3. 第二段（第 8-14 行）：

    • 对于内的每个点points表：获取一个随机数（！）groundtruth点从measurement表，其距离 distance
    • 将这些元组保存在gtps table

  4. 第三段（第 16-19 行）：

    • 对于每个groundtruth里面的值gtps表：获取全部anchor_id价值观和...
    • If an anchor_id值不唯一：然后随机选择一个

  5. Output: 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 values.
  • 如果一个输入值有多个groundtruth值，这些必须全部相等。
  • 每个真实输入点元组都与每个可能的anchor_id为了这个事实。
  • 两个不同的输入值可以有相同的对应groundtruth value.
  • 两个不同的 groundtruth-inputPoint-tuples 可以具有相同的anchor_id
  • 两个相同的真实输入点元组必须具有不同的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 个输入值）从大约 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

通过这个查询，速度非常快（15ms），但还遗漏了很多：

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

如果anchor_id and id可以存储在元组数组中。例如：{[4,1],[6,3],[4,2],[8,5],[4,4]}（约束：每个元组都是唯一的，每个 id （==示例中的第二个值）都是唯一的，anchor_ids 不是唯一的）。此示例显示不带仍必须应用的过滤器的查询。应用过滤器后，它看起来像这样{[6,3],[4,4],[8,5]}.

正在进行的工作二： 

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 的东西，but:
  • 它必须与同步ARRAY_AGG(m.id)。这意味着：
    如果 DISTINCT 命令保留索引 1、4 和 7anchor_id数组，那么它还必须保留数组的索引 1、4 和 7id数组（当然删除所有其他数组）

如果anchor_id 和id 可以存储在元组数组中，那就太好了。

多维数组的聚合函数

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

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

阅读此相关答案中的解释：
选择数据到 Postgres 数组中 https://stackoverflow.com/questions/11762398/selecting-data-into-a-postgres-array-format/11763245#11763245

对于数组中出现多次的每个anchor_id：保留一个 随机一个并删除所有其他。这也意味着删除 id-array 中每个删除的anchor_id 对应的 id

Query

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 per (p, groundtruth)如果有多个对等点，则选择随机行。这样就可以连接了anchor_id - id保持完好无损。

  • 外部查询聚合一个二维数组，如您所愿，按顺序排列anchor_id。如果你想拥有anchor_id随机排序，再次使用随机：

    array_agg_mult(ARRAY[ARRAY[anchor_id, id]] ORDER BY random())

  • 最后，DISTINCT ON只选1个groundtruth per p，再次随机。

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)

sql

postgresql

queryoptimization

aggregatefunctions

PostGIS

需要 SQL 优化（也许 DISTINCT ON 是原因？） 的相关文章

  • SQL IN 子句比单个查询慢

    我正在使用 Hibernate 的 JPA 实现和 MySQL 5 0 67 MySQL 配置为使用 InnoDB 在执行 JPA 查询 转换为 SQL 时 我发现使用IN子句比执行单个查询慢 例子 SELECT p FROM Person

  • SQL查询获取列的精度值

    我需要一个 SQL 查询来获取某些列的精度值 我主要关心十进制类型列 并且我需要相同的精度值 我意识到在某些版本和数据库服务器供应商中可以这样做 如果您能列出其中的一些 那就太好了 对于 SQL 服务器 select precision f

  • 大型数据集的报告工具/查看器

    我有一个数据处理系统 可以根据其处理的数据生成非常大的报告 我所说的 大 是指该系统的 小 执行在转储到 CSV 文件中时会产生大约 30 MB 的报告数据 而大数据集大约为 130 150 MB 我确信有人有更大的想法 大 但这不是重点

  • SSRS报告不显示数据

    我刚刚创建了 SQL Server 2005 SSRS 报告 数据未显示在预览窗格中 数据集是根据字符串参数从存储过程正确填充的 我可以在数据窗格中执行它 在预览窗格中运行报表时 会显示正确的行数 但单元格的内容不包含任何数据 源数据集基于

  • 使用 START WITH 和 CONNECT BY PRIOR 将查询从 oracle 迁移到 postgresql

    我正在将一个进程从 oracle 迁移到 postgresql 并且在它们的转换方面遇到了另一个问题 我一直在研究如何迁移oracle查询 它有 START WITH 和 CONNECT BY PRIOR 我已经对此进行了记录 我认为最简单

  • 将记录与另一个表上的最新记录连接

    我正在尝试创建一个 SQL 视图 我如何从一个表中选择最新的记录 而其他记录保持原样 我需要从所有表中选择所有记录 这工作正常 但我需要仅按日期选择最新的提案 这是我遇到的问题 这是我到目前为止所拥有的 SELECT TOP 100 PER

  • 查找缺失值

    我有一个表 有 2 个重要的列 DocEntry WebId 样本数据就像 DocEntry WebId 1 S001 2 S002 3 S003 4 S005 现在我们可以注意到 在 WebId 列中 S004 丢失了 我们如何通过查询找

  • Psycopg2 中的元命令 - \d 不起作用

    我希望使用列出表的所有列名psycopg2Python 包 2 7 但我无法执行以下查询 cur execute d my table psycopg2 ProgrammingError syntax error at or near 对于

  • 有没有办法将应用程序上下文与非 Sybase DB 服务器中的数据库连接进行通信（类似于 Sybase 中的 set_appcontext）？

    Sybase 有一种让应用程序进行通信的方法 上下文 数据 http manuals sybase com onlinebooks group as asg1250e sag Generic BookTextView 38861 hf 0

  • Postgres 数据库中特殊的时区处理

    我的环境 I m in 法国巴黎 UTC 1 or CET It s 12am 00 00 我们在2016 年 11 月 25 日 My Postgres数据库托管于亚马逊网络服务 AWS RDS 在eu west 1 region 问题

  • 如何使用 pgAdmin 恢复 postgreSQL 转储文件？

    我有一个 dmp 文件 想要从中恢复数据库 使用 pgAdmin 我该怎么做 在 PgAdmin3 内 在您正在使用的服务器中创建一个新数据库 右键单击该数据库并选择 恢复 使用 浏览器 按钮选择 dmp 文件 选择 恢复 开始恢复数据库

  • 在 JSP 中迭代列表对象

    我正在做一个项目来尝试自学 spring 和 struts 我目前卡在 JSP 页面上 我有一个 pojo 类 其中包含带有 getter setter 的变量 eid 和 ename 我还有一个 sql 中的表 其具有相同的值和六个填充行

  • 为什么 justify_interval('360 days'::interval) 结果 '1 年'

    因为某些原因justify interval now 2013 02 14 timestamptz 产生奇怪的结果 postgres select justify interval concat 365 4 1 days interval

  • 具有位变化的 PostgreSQL 位运算符“不能与不同大小的位字符串”

    我有一个变化的位掩码字段 我想对其执行按位与操作 PG Error ERROR cannot AND bit strings of different sizes SELECT groups FROM groups WHERE read r

  • 从 Visual Studio 调试 SQL Server 2005 中的存储过程？

    我在这里和其他地方看到很多令人沮丧的问题 但没有明确的答案 我试图让存储过程进行调试 但没有成功 客户端 VS2005或VS2008 两者都不起作用 当我从存储过程上下文菜单中选择 步入存储过程 时 我在调试窗口中看到 已被用户取消 这就是

  • 将 SQL 中的数据存储在数组中

    我正在尝试将 sql 数据库中的数据存储到数组中 目前我有这个 query mysql query SELECT FROM InspEmail WHERE Company LIKE company while row mysql fetch

  • 如何编辑表以启用级联删除？

    我有一个代表用户的表 当用户被删除时我得到 DELETE 语句与 REFERENCE 约束冲突 显然 CASCADE DELETE在SQL Server中并不像我想象的那么容易 需要将选项添加到表中 问题是 我不知道如何添加CASCADE

  • SQL Server：比较两个表中的列

    我最近完成了从某些应用程序的旧版本到当前版本的迁移 在迁移数据库时遇到了一些问题 我需要一个可以帮助我比较两个表中的列的查询 我的意思不是行中的数据 我需要比较列本身来弄清楚我错过了表结构的哪些变化 看一下红门 SQL 比较 http ww

  • 对时间序列数据重新采样

    我有一个以毫秒为单位的时间序列列表 我想对时间序列进行重新采样并对组应用平均值 我如何在 Postgres 中实现它 重新采样 是指聚合一秒或一分钟内的所有时间戳 一秒或一分钟内的所有行形成一组 表结构 date x y z Use dat

  • 使用 GROUP 和 SUM 的 LINQ 查询

    请帮助我了解如何使用带有 GROUP 和 SUM 的 LINQ 进行查询 Query the database IEnumerable

随机推荐

热门标签