🥇SQL HowTo：直接在查询中写一个while循环，或者“初等三路”| 专业主机

定期出现通过一组键搜索相关数据的任务， 直到我们得到所需的记录总数.

最“逼真”的例子就是展示 20个最古老的问题，列出 在员工名单上 （例如，在同一部门内）。对于带有工作领域简短摘要的各种管理“仪表板”，经常需要类似的主题。

在本文中，我们将考虑在 PostgreSQL 上实现解决此类问题的“简单”版本，即“更智能”且非常复杂的算法 SQL 中的“循环”，带有找到的数据的退出条件，这对于一般开发和其他类似情况都很有用。

让我们使用一个测试数据集上一篇文章。使得输出记录在排序值匹配时不会时不时地“跳动”， 通过添加主键来扩展主题索引。同时，这将立即赋予它唯一性，并保证我们排序顺序的唯一性：

CREATE INDEX ON task(owner_id, task_date, id);
-- а старый - удалим
DROP INDEX task_owner_id_task_date_idx;

正如所听到的，所写的

首先，让我们画出最简单的请求版本，传递执行者的 ID 数组作为输入:

SELECT
  *
FROM
  task
WHERE
  owner_id = ANY('{1,2,4,8,16,32,64,128,256,512}'::integer[])
ORDER BY
  task_date, id
LIMIT 20;

[看看 explain.tensor.ru]

有点难过 - 我们只订购了 20 条记录，索引扫描返回了我们 960字符串，然后也必须进行排序......让我们尝试少读一些。

取消嵌套+数组

第一个考虑因素将对我们有帮助 - 如果我们需要的话 共 20 条已排序 记录，读一下就够了 每个按相同顺序排序的数量不超过 20 个 钥匙。好的， 适合指数 （owner_id、task_date、id）我们有。

让我们使用相同的提取和“转为列”机制 完整的表条目，如上一篇文章。并使用函数将卷积应用于数组 ARRAY():

WITH T AS (
  SELECT
    unnest(ARRAY(
      SELECT
        t
      FROM
        task t
      WHERE
        owner_id = unnest
      ORDER BY
        task_date, id
      LIMIT 20 -- ограничиваем тут...
    )) r
  FROM
    unnest('{1,2,4,8,16,32,64,128,256,512}'::integer[])
)
SELECT
  (r).*
FROM
  T
ORDER BY
  (r).task_date, (r).id
LIMIT 20; -- ... и тут - тоже

[看看 explain.tensor.ru]

哦，已经好多了！ 速度提高 40%，数据量减少 4.5 倍 必须阅读。

通过 CTE 实现表记录我会注意到 在某些情况下 尝试在子查询中搜索记录字段后立即使用记录字段而不“包装”在 CTE 中，可能会导致 “乘法”初始化计划 与这些相同字段的数量成正比：

SELECT
  ((
    SELECT
      t
    FROM
      task t
    WHERE
      owner_id = 1
    ORDER BY
      task_date, id
    LIMIT 1
  ).*);

Result  (cost=4.77..4.78 rows=1 width=16) (actual time=0.063..0.063 rows=1 loops=1)
  Buffers: shared hit=16
  InitPlan 1 (returns $0)
    ->  Limit  (cost=0.42..1.19 rows=1 width=48) (actual time=0.031..0.032 rows=1 loops=1)
          Buffers: shared hit=4
          ->  Index Scan using task_owner_id_task_date_id_idx on task t  (cost=0.42..387.57 rows=500 width=48) (actual time=0.030..0.030 rows=1 loops=1)
                Index Cond: (owner_id = 1)
                Buffers: shared hit=4
  InitPlan 2 (returns $1)
    ->  Limit  (cost=0.42..1.19 rows=1 width=48) (actual time=0.008..0.009 rows=1 loops=1)
          Buffers: shared hit=4
          ->  Index Scan using task_owner_id_task_date_id_idx on task t_1  (cost=0.42..387.57 rows=500 width=48) (actual time=0.008..0.008 rows=1 loops=1)
                Index Cond: (owner_id = 1)
                Buffers: shared hit=4
  InitPlan 3 (returns $2)
    ->  Limit  (cost=0.42..1.19 rows=1 width=48) (actual time=0.008..0.008 rows=1 loops=1)
          Buffers: shared hit=4
          ->  Index Scan using task_owner_id_task_date_id_idx on task t_2  (cost=0.42..387.57 rows=500 width=48) (actual time=0.008..0.008 rows=1 loops=1)
                Index Cond: (owner_id = 1)
                Buffers: shared hit=4"
  InitPlan 4 (returns $3)
    ->  Limit  (cost=0.42..1.19 rows=1 width=48) (actual time=0.009..0.009 rows=1 loops=1)
          Buffers: shared hit=4
          ->  Index Scan using task_owner_id_task_date_id_idx on task t_3  (cost=0.42..387.57 rows=500 width=48) (actual time=0.009..0.009 rows=1 loops=1)
                Index Cond: (owner_id = 1)
                Buffers: shared hit=4

同一条记录被“搜索”了 4 次……直到 PostgreSQL 11，这种行为经常发生，解决方案是“包装”在 CTE 中，这是这些版本中优化器的无条件边界。

递归累加器

在之前的版本中，我们总共阅读了 200字符串 为了必要的 20。已经不是 960，但甚至更少 - 有可能吗？

让我们尝试运用我们需要的知识 总xnumx 记录。也就是说，我们只会迭代数据减法，直到达到我们需要的数量。

第 1 步：开始列表

显然，我们的 20 个条目的“目标”列表应该以我们的owner_id 键之一的“第一个”条目开始。因此，我们首先找到这样的 每个键都是“very first” 并将其放入列表中，按照我们想要的顺序对其进行排序 - (task_date, id)。

步骤2：查找“下一条”记录

现在，如果我们从列表中取出第一个条目并开始 指数进一步“走低” 保存owner_id-key后，所有找到的记录都是结果选择中的下一个记录。当然，仅 直到我们交叉应用键 列表中的第二个条目。

如果结果证明我们“越过了”第二个条目，那么 最后读取的条目应添加到列表中，而不是第一个 （具有相同的owner_id），之后列表再次排序。

也就是说，我们总是得到列表中每个键的条目不超过一个（如果条目结束，并且我们没有“交叉”，那么第一个条目将从列表中消失，并且不会添加任何内容）），和他们 总是排序的 按应用程序密钥（task_date、id）的升序排列。

步骤 3：过滤和扩展记录

在我们递归选择的行的部分中，有一些记录 rv 重复 - 首先我们找到“跨越列表第二个条目的边界”，然后将其替换为列表中的第一个。因此第一次出现的情况应该被过滤掉。

糟糕的最终查询

WITH RECURSIVE T AS (
  -- #1 : заносим в список "первые" записи по каждому из ключей набора
  WITH wrap AS ( -- "материализуем" record'ы, чтобы обращение к полям не вызывало умножения InitPlan/SubPlan
    WITH T AS (
      SELECT
        (
          SELECT
            r
          FROM
            task r
          WHERE
            owner_id = unnest
          ORDER BY
            task_date, id
          LIMIT 1
        ) r
      FROM
        unnest('{1,2,4,8,16,32,64,128,256,512}'::integer[])
    )
    SELECT
      array_agg(r ORDER BY (r).task_date, (r).id) list -- сортируем список в нужном порядке
    FROM
      T
  )
  SELECT
    list
  , list[1] rv
  , FALSE not_cross
  , 0 size
  FROM
    wrap
UNION ALL
  -- #2 : вычитываем записи 1-го по порядку ключа, пока не перешагнем через запись 2-го
  SELECT
    CASE
      -- если ничего не найдено для ключа 1-й записи
      WHEN X._r IS NOT DISTINCT FROM NULL THEN
        T.list[2:] -- убираем ее из списка
      -- если мы НЕ пересекли прикладной ключ 2-й записи
      WHEN X.not_cross THEN
        T.list -- просто протягиваем тот же список без модификаций
      -- если в списке уже нет 2-й записи
      WHEN T.list[2] IS NULL THEN
        -- просто возвращаем пустой список
        '{}'
      -- пересортировываем словарь, убирая 1-ю запись и добавляя последнюю из найденных
      ELSE (
        SELECT
          coalesce(T.list[2] || array_agg(r ORDER BY (r).task_date, (r).id), '{}')
        FROM
          unnest(T.list[3:] || X._r) r
      )
    END
  , X._r
  , X.not_cross
  , T.size + X.not_cross::integer
  FROM
    T
  , LATERAL(
      WITH wrap AS ( -- "материализуем" record
        SELECT
          CASE
            -- если все-таки "перешагнули" через 2-ю запись
            WHEN NOT T.not_cross
              -- то нужная запись - первая из спписка
              THEN T.list[1]
            ELSE ( -- если не пересекли, то ключ остался как в предыдущей записи - отталкиваемся от нее
              SELECT
                _r
              FROM
                task _r
              WHERE
                owner_id = (rv).owner_id AND
                (task_date, id) > ((rv).task_date, (rv).id)
              ORDER BY
                task_date, id
              LIMIT 1
            )
          END _r
      )
      SELECT
        _r
      , CASE
          -- если 2-й записи уже нет в списке, но мы хоть что-то нашли
          WHEN list[2] IS NULL AND _r IS DISTINCT FROM NULL THEN
            TRUE
          ELSE -- ничего не нашли или "перешагнули"
            coalesce(((_r).task_date, (_r).id) < ((list[2]).task_date, (list[2]).id), FALSE)
        END not_cross
      FROM
        wrap
    ) X
  WHERE
    T.size < 20 AND -- ограничиваем тут количество
    T.list IS DISTINCT FROM '{}' -- или пока список не кончился
)
-- #3 : "разворачиваем" записи - порядок гарантирован по построению
SELECT
  (rv).*
FROM
  T
WHERE
  not_cross; -- берем только "непересекающие" записи

[看看 explain.tensor.ru]

所以我们 用 50% 的数据读取换取 20% 的执行时间。也就是说，如果你有理由相信读取可能会很长（例如，数据通常不在缓存中，而你必须去磁盘上获取它），那么通过这种方式你可以依赖更少的读取。

无论如何，执行时间比“天真的”第一个选项要好。但是使用这 3 个选项中的哪一个取决于您。

来源： habr.com

SQL HowTo：直接在查询中写一个while-loop，或者“初等三路”

正如所听到的，所写的

取消嵌套+数组

递归累加器

第 1 步：开始列表

步骤2：查找“下一条”记录

步骤 3：过滤和扩展记录

添加评论取消回复

SQL HowTo：直接在查询中写一个while-loop，或者“初等三路”

正如所听到的，所写的

取消嵌套+数组

递归累加器

第 1 步：开始列表

步骤2：查找“下一条”记录

步骤 3：过滤和扩展记录

添加评论 取消回复

添加评论取消回复