🥇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 個選項中的哪一個取決於您。

來源： www.habr.com

SQL HowTo：直接在查詢中寫一個while-loop，或者“初等三路”

正如所聽到的，所寫的

取消嵌套+數組

遞歸累加器

第 1 步：開始列表

步驟2：查找“下一條”記錄

步驟 3：過濾和擴展記錄

添加評論取消回复

SQL HowTo：直接在查詢中寫一個while-loop，或者“初等三路”

正如所聽到的，所寫的

取消嵌套+數組

遞歸累加器

第 1 步：開始列表

步驟2：查找“下一條”記錄

步驟 3：過濾和擴展記錄

添加評論 取消回复

添加評論取消回复