🥇SQL HowTo: نوشتن یک حلقه while به طور مستقیم در پرس و جو یا "Elementary three-step"

به طور دوره ای، وظیفه جستجوی داده های مرتبط با استفاده از مجموعه ای از کلیدها مطرح می شود. تا زمانی که تعداد کل رکوردهای لازم را بدست آوریم.

"واقعی ترین" مثال برای نمایش است 20 مشکل قدیمی، ذکر شده در لیست کارمندان (مثلاً در یک بخش). برای "داشبوردهای" مدیریتی مختلف با خلاصه‌های مختصری از حوزه‌های کاری، موضوع مشابه اغلب مورد نیاز است.

در این مقاله ما به پیاده سازی یک راه حل ساده برای چنین مشکلی در PostgreSQL نگاه خواهیم کرد، یک الگوریتم هوشمندتر و بسیار پیچیده. "حلقه" در SQL با یک شرط خروج از داده های یافت شدهکه هم برای توسعه عمومی و هم برای استفاده در سایر موارد مشابه می تواند مفید باشد.

بیایید یک مجموعه داده آزمایشی را از مقاله قبلی. برای جلوگیری از "پرش" رکوردهای نمایش داده شده از زمان به زمان زمانی که مقادیر مرتب شده با یکدیگر مطابقت دارند، با افزودن یک کلید اصلی، فهرست موضوع را گسترش دهید. در عین حال، این بلافاصله به آن منحصربه‌فرد می‌دهد و به ما تضمین می‌کند که ترتیب مرتب‌سازی بدون ابهام است:

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

همانطور که شنیده می شود، نوشته شده است

ابتدا، بیایید ساده ترین نسخه درخواست را ترسیم کنیم، شناسه اجراکنندگان را ارسال کنیم آرایه به عنوان پارامتر ورودی:

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;

[به توضیح.tensor.ru نگاه کنید]

کمی غمگین - ما فقط 20 رکورد سفارش دادیم، اما Index Scan آن را به ما برگرداند 960 خط، که بعد هم باید مرتب می شد... سعی کنیم کمتر بخوانیم.

unnest + 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; -- ... и тут - тоже

[به توضیح.tensor.ru نگاه کنید]

اوه، خیلی بهتر از قبل! 40 درصد سریعتر و 4.5 برابر داده کمتر مجبور شدم بخونمش

تحقق رکوردهای جدول از طریق 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 رکورد "هدف" ما باید با رکوردهای "اولین" یکی از کلیدهای مالک_id ما شروع شود. بنابراین، ابتدا چنین خواهیم یافت "بسیار اول" برای هر یک از کلیدها و آن را به لیست اضافه می کنیم و آن را به ترتیبی که می خواهیم مرتب کنیم - (تاریخ_کار، شناسه).

مرحله 2: ورودی های "بعدی" را پیدا کنید

حال اگر اولین ورودی را از لیست خود برداریم و شروع کنیم در امتداد شاخص "گام" فراتر بروید با حفظ کلید owner_id، تمام رکوردهای یافت شده دقیقاً رکوردهای بعدی در انتخاب به دست آمده هستند. البته فقط تا زمانی که کلید قنداق را رد کنیم دومین ورودی در لیست

اگر معلوم شد که از رکورد دوم "گذر" کرده ایم، پس آخرین ورودی خوانده شده باید به جای اولین ورودی به لیست اضافه شود (با همان 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; -- берем только "непересекающие" записи

[به توضیح.tensor.ru نگاه کنید]

بنابراین، ما 50٪ از داده ها را برای 20٪ از زمان اجرا معامله کرد. یعنی اگر دلایلی برای این باور دارید که خواندن ممکن است زمان زیادی ببرد (مثلاً داده ها اغلب در حافظه پنهان نیستند و باید برای آن به دیسک بروید)، پس از این طریق می توانید کمتر به خواندن وابسته باشید. .

در هر صورت، زمان اجرا بهتر از گزینه اول "ساده لوح" بود. اما اینکه کدام یک از این 3 گزینه استفاده کنید به شما بستگی دارد.

منبع: www.habr.com

SQL HowTo: یک حلقه while به طور مستقیم در پرس و جو بنویسید، یا "Elementary three-way"

همانطور که شنیده می شود، نوشته شده است

unnest + ARRAY

انباشت کننده بازگشتی

مرحله 1: فهرست شروع

مرحله 2: ورودی های "بعدی" را پیدا کنید

مرحله 3: فیلتر کردن و "بسط" رکوردها

اضافه کردن نظر انصراف از پاسخ