SQL HowTo: クエリ内に直接 while ループを記述する、または「基本的な XNUMX 方向」

一連のキーによって関連データを検索するタスクが定期的に発生します。 必要なレコードの合計数が得られるまで.

最も「本物らしい」例は、次のように表示することです。 最も古い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用語、その後、並べ替える必要もありました...そして、読む量を減らしてみましょう。

ネスト解除 + ARRAY

必要な場合に役立つ最初の考慮事項 合計 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 分の XNUMX に減少 読まなければならなかった。

CTE を介したテーブル レコードの具体化留意しておきます 場合によっては サブクエリで検索した後、CTE で「ラップ」せずにレコード フィールドをすぐに操作しようとすると、次のような問題が発生する可能性があります。 「乗算」InitPlan これらの同じフィールドの数に比例します。

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ではありませんが、さらにそれ未満です-可能ですか？

必要な知識を活用してみよう 合計20 記録。 つまり、必要な量に達するまでのみデータの減算を繰り返します。

ステップ 1: スタートリスト

明らかに、20 エントリの「ターゲット」リストは、owner_id キーの XNUMX つの「最初の」エントリから始まる必要があります。 したがって、最初にそのようなものを見つけます 各キーの「非常に最初」 それをリストに追加し、希望する順序 (task_date、id) で並べ替えます。

ステップ 2: 「次の」レコードを見つける

ここで、リストから最初のエントリを取り出して開始すると、 インデックスをさらに下に「ステップ」します owner_id-key を保存すると、見つかったすべてのレコードが、結果として選択される次のレコードになります。 もちろん、それだけ 適用されたキーを通過するまで リストの XNUMX 番目のエントリ。

XNUMX 番目のエントリを「越えた」ことが判明した場合、 最初のエントリではなく、最後に読み取られたエントリをリストに追加する必要があります。 (同じ owner_id で)、その後、リストが再度ソートされます。

つまり、リストには各キーのエントリが XNUMX つしかないことが常にわかります (エントリが終わり、「交差」していない場合、最初のエントリはリストから単に消えるだけで、何も追加されません) ）、 そして彼らが 常に並べ替えられる アプリケーションキー(task_date、id)の昇順で表示されます。

ステップ 3: レコードのフィルタリングと展開

再帰的選択の行の一部では、いくつかのレコードが rv 重複しています。最初に「リストの 2 番目のエントリの境界を越えている」などを見つけてから、リストの 1 番目として置き換えます。 したがって、最初に出現したものは除外する必要があります。

ひどい最終クエリ

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