登录
当前位置： 首页 > 面试题库 >

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

蓝慈
2023-03-14
问题内容：

我当前的查询如下所示：

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

    • 第一段（第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（当然删除所有其他索引）

问题答案：

如果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。



 类似资料：
  • SQL命令优化需要记住的9点事项

    本文向大家介绍SQL命令优化需要记住的9点事项，包括了SQL命令优化需要记住的9点事项的使用技巧和注意事项，需要的朋友参考一下 与数据库交互的基本语言是sql，数据库每次解析和执行sql语句多需要执行很多步骤。以sql server为例，当数据库收到一条查询语句时，语法分析器会扫描sql语句并将其分成逻辑单元（如关键词、表达式、运算符和标识符）并生成查询树，最后查询优化器将分析所有可以访问数据库的

  • 创建项目需要root帐户的原因

    我很困惑，为什么需要帐户来创建项目？如果我使用创建项目，而不使用aka，它将返回以下错误消息。 终止安装. yarnpkg add--精确反应-dom反应-脚本--cwd /Users/user/Documents/Projects/ReactJS/mytest已失败。 如果我创建一个项目与，它是工作。

  • SQL差异的原因

    问题内容： 尽管存在SQL的ANSI标准，但为什么SQL发行版是如此非标准？SQL数据库的工作方式确实存在许多有意义的差异，还是我一直在使用的两个数据库：MS- SQL和PostgreSQL？为什么会出现这些差异？ 问题答案： 这是“隐身锁定”的一种形式。乔尔在这里详细介绍： http://www.joelonsoftware.com/articles/fog0000000056.html htt

  • 写在前面：你需要这本书的原因

    有没有哪一个瞬间，让你想要放弃学习编程？ 在我决心开始学编程的时候，我为自己制定了一个每天编程1小时的计划，那时候工作很忙，我只能等到晚上9点，同事都下班之后，独自留在办公室编程。在翻遍了我能找到的几十本国内外的 Python 编程教程之后，我还是似懂非懂。那些教程里面到处都是抽象的概念、复杂的逻辑，对于专业开发者这些再平常不过，而对于我这样一个学设计出身的编程小白，没有被视觉化的东西是无法被理解

  • 是否允许此浮点优化？

    我试图检查在哪里失去了准确表示大整数的能力。所以我写了这个小片段： 这段代码似乎适用于所有编译器，除了clang。Clang生成一个简单的无限循环。戈德博尔特。 这是允许的吗？如果是，这是QoI问题吗？

  • 非规范化数据（也许是枢轴？）

    问题内容： 我有一个简单的表，其中包含学生编号和相应的教师编号，并且需要对它进行规范化，以输入到旧版系统中。 例如，下面是数据现在的样子： 我希望它看起来像这样，将每个Teacher分成一个单独的列，从左到右填充各列。一个业务规则是，每个学生最多只能有六位老师： 原始表中有10,000多行，因此我需要以编程方式执行此操作。谢谢！ 问题答案： 您可以使用数据透视。您还需要“排名”您的老师1-6。请参

相关阅读

有些语言允许负模数是有原因的吗？Oracle中的SQL优化在SQL中是否需要分号？您最常用的sql优化是什么？在Docker容器中不需要运行sshd的原因浅析

相关文章

SQL的优点ES性能优化原理大揭秘MySQL 临时表的原理以及优化手段碰到超大SQL该如何优化？MySQL 常见数据库优化sql语句

相关问答

“constexpr if”vs“if”与优化-为什么需要“constexpr”？python - django前端分类页面需要查询100多条SQL，如何优化？DP数组初始化的原因Junit5： WebMvcTest返回404。也许是因为我没有嘲笑你的方法？第三方使用SetExpressCheckout是否需要PayPal许可？

相关工具

NCMusicEngine 优化Polr 中文优化版esoTalk 中文优化版WordPress中文优化版朋也博客

相关文档

Oracle 优化教程React Bits 你需要了解的知识点怎样优化 Pentium 系列处理器的代码如何成为优秀的程序员第一性原理
快捷导航： 新手教程 算法原理 架构设计 Java进阶 数据库进阶 大厂专栏 面试经验 编程笔记 编程问答 所有专题 文档资料 工具软件 电子书籍 小牛导航
在线工具： 房贷计算器 个税计算器 Linux命令查询 Json格式化 正则表达式 颜色转换 AES加解密 SHA1加密 MD5加密 毒鸡汤 字数统计 随机密码生成 进制转换 Base64编解码 励志句子
Copyright © 2019-2024 小牛知识库@xnip.cn. All Rights Reserved.