SQL HowTo: ຂຽນ while-loop ໂດຍກົງໃນການສອບຖາມ, ຫຼື "ປະຖົມສາມທາງ"

ແຕ່ລະໄລຍະ, ວຽກງານຂອງການຄົ້ນຫາຂໍ້ມູນທີ່ກ່ຽວຂ້ອງໂດຍໃຊ້ຊຸດຂອງກະແຈເກີດຂື້ນ. ຈົນກ່ວາພວກເຮົາໄດ້ຮັບຈໍານວນທັງຫມົດທີ່ກໍານົດໄວ້ຂອງບັນທຶກ.

ຕົວຢ່າງ "ຊີວິດຈິງ" ທີ່ສຸດແມ່ນການສະແດງ 20 ບັນຫາເກົ່າແກ່ທີ່ສຸດ, ລາຍຊື່ ໃນ​ບັນ​ຊີ​ລາຍ​ຊື່​ຂອງ​ພະ​ນັກ​ງານ​ (ຕົວຢ່າງ, ພາຍໃນຫນຶ່ງພະແນກ). ສໍາລັບການຄຸ້ມຄອງຕ່າງໆ "dashboards" ທີ່ມີບົດສະຫຼຸບສັ້ນໆຂອງພື້ນທີ່ເຮັດວຽກ, ຫົວຂໍ້ທີ່ຄ້າຍຄືກັນແມ່ນຕ້ອງການເລື້ອຍໆ.

ໃນບົດຄວາມນີ້ພວກເຮົາຈະເບິ່ງການປະຕິບັດໃນ PostgreSQL ຂອງການແກ້ໄຂ "naive" ສໍາລັບບັນຫາດັ່ງກ່າວ, ເປັນ "smarter" ແລະ algorithm ສະລັບສັບຊ້ອນຫຼາຍ. "loop" ໃນ SQL ທີ່ມີເງື່ອນໄຂອອກຈາກຂໍ້ມູນທີ່ພົບເຫັນ, ຊຶ່ງສາມາດເປັນປະໂຫຍດທັງການພັດທະນາທົ່ວໄປແລະສໍາລັບການນໍາໃຊ້ໃນກໍລະນີທີ່ຄ້າຍຄືກັນອື່ນໆ.

ໃຫ້ເຮົາເອົາຊຸດຂໍ້ມູນການທົດສອບຈາກ ບົດຄວາມທີ່ຜ່ານມາ. ເພື່ອປ້ອງກັນບໍ່ໃຫ້ບັນທຶກທີ່ສະແດງຈາກ "ໂດດ" ເປັນບາງຄັ້ງຄາວເມື່ອຄ່າຈັດຮຽງກົງກັນ, ຂະຫຍາຍດັດຊະນີຫົວຂໍ້ໂດຍການເພີ່ມລະຫັດຫຼັກ. ໃນເວລາດຽວກັນ, ນີ້ຈະໃຫ້ມັນເປັນເອກະລັກທັນທີແລະຮັບປະກັນພວກເຮົາວ່າຄໍາສັ່ງການຈັດລຽງແມ່ນບໍ່ຊັດເຈນ:

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

ຕາມ​ທີ່​ໄດ້​ຍິນ, ດັ່ງ​ນັ້ນ​ຈຶ່ງ​ຂຽນ

ທໍາອິດ, ໃຫ້ເຮົາແຕ້ມສະບັບທີ່ງ່າຍດາຍທີ່ສຸດຂອງຄໍາຮ້ອງຂໍ, ຜ່ານ IDs ຂອງນັກສະແດງ array ເປັນພາລາມິເຕີການປ້ອນຂໍ້ມູນ:

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) ພວກເຮົາມີ.

ໃຫ້ໃຊ້ກົນໄກດຽວກັນສໍາລັບການສະກັດແລະ "ແຜ່ເຂົ້າໄປໃນຖັນ" ບັນທຶກຕາຕະລາງປະສົມປະສານ, ໃນ​ຖາ​ນະ​ເປັນ​ ບົດຄວາມສຸດທ້າຍ. ພວກເຮົາຍັງສາມາດໃຊ້ folding ເຂົ້າໄປໃນ array ໂດຍໃຊ້ຟັງຊັນ 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ໃຫ້ຂ້ອຍດຶງດູດຄວາມສົນໃຈຂອງເຈົ້າກັບຄວາມຈິງທີ່ວ່າ ໃນ​ບາງ​ກໍ​ລະ​ນີ ຄວາມພະຍາຍາມທີ່ຈະເຮັດວຽກທັນທີທັນໃດກັບພາກສະຫນາມຂອງບັນທຶກຫຼັງຈາກການຊອກຫາມັນຢູ່ໃນ subquery, ໂດຍບໍ່ມີການ "ຫໍ່" ມັນຢູ່ໃນ 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, ເຊິ່ງເປັນຂອບເຂດຈໍາກັດຢ່າງແທ້ຈິງສໍາລັບ optimizer ໃນສະບັບເຫຼົ່ານີ້.

ຄັງສະສົມ Recursive

ໃນສະບັບທີ່ຜ່ານມາ, ພວກເຮົາອ່ານທັງຫມົດ 200 ເສັ້ນ ສໍາລັບ sake ຂອງຄວາມຕ້ອງການ 20. ບໍ່ແມ່ນ 960, ແຕ່ເຖິງແມ່ນວ່າຫນ້ອຍ - ມັນເປັນໄປໄດ້ບໍ?

ລອງໃຊ້ຄວາມຮູ້ທີ່ເຮົາຕ້ອງການ ລວມ 20 ບັນທຶກ. ນັ້ນແມ່ນ, ພວກເຮົາຈະເຮັດຊ້ໍາການອ່ານຂໍ້ມູນເທົ່ານັ້ນຈົນກ່ວາພວກເຮົາບັນລຸຈໍານວນທີ່ພວກເຮົາຕ້ອງການ.

ຂັ້ນຕອນທີ 1: ເລີ່ມຕົ້ນລາຍການ

ແນ່ນອນ, ບັນຊີລາຍຊື່ "ເປົ້າຫມາຍ" ຂອງພວກເຮົາຂອງ 20 ບັນທຶກຄວນເລີ່ມຕົ້ນດ້ວຍບັນທຶກ "ທໍາອິດ" ສໍາລັບຫນຶ່ງໃນລະຫັດ owner_id ຂອງພວກເຮົາ. ເພາະສະນັ້ນ, ທໍາອິດພວກເຮົາຈະຊອກຫາສິ່ງດັ່ງກ່າວ "ທໍາອິດ" ສໍາລັບແຕ່ລະກະແຈ ແລະເພີ່ມມັນເຂົ້າໃນບັນຊີລາຍຊື່, ຈັດຮຽງຕາມລໍາດັບທີ່ພວກເຮົາຕ້ອງການ - (task_date, id).

ຂັ້ນຕອນທີ 2: ຊອກຫາລາຍການ "ຕໍ່ໄປ".

ໃນປັດຈຸບັນຖ້າພວກເຮົາເອົາການເຂົ້າທໍາອິດຈາກບັນຊີລາຍຊື່ຂອງພວກເຮົາແລະເລີ່ມຕົ້ນ "ກ້າວ" ຕໍ່ໄປຕາມດັດຊະນີ ຮັກສາລະຫັດ owner_id, ຫຼັງຈາກນັ້ນບັນທຶກທັງຫມົດທີ່ພົບເຫັນແມ່ນແນ່ນອນອັນຕໍ່ໄປໃນການຄັດເລືອກຜົນໄດ້ຮັບ. ແນ່ນອນ, ພຽງແຕ່ ຈົນກ່ວາພວກເຮົາຂ້າມຄີກົ້ນ ເຂົ້າທີສອງໃນບັນຊີລາຍຊື່.

ຖ້າມັນປາກົດວ່າພວກເຮົາ "ຂ້າມ" ບັນທຶກທີສອງ, ຫຼັງຈາກນັ້ນ ຂໍ້ຄວາມສຸດທ້າຍທີ່ອ່ານຄວນຈະຖືກເພີ່ມໃສ່ບັນຊີລາຍຊື່ແທນທີ່ຈະເປັນອັນທໍາອິດ (ກັບ owner_id ດຽວກັນ), ຫຼັງຈາກນັ້ນພວກເຮົາຈັດຮຽງບັນຊີລາຍຊື່ອີກເທື່ອຫນຶ່ງ.

ນັ້ນແມ່ນ, ພວກເຮົາສະເຫມີໄດ້ຮັບວ່າບັນຊີລາຍຊື່ບໍ່ມີຫຼາຍກ່ວາຫນຶ່ງລາຍການສໍາລັບແຕ່ລະກະແຈ (ຖ້າລາຍການຫມົດໄປແລະພວກເຮົາບໍ່ "ຂ້າມ", ຫຼັງຈາກນັ້ນລາຍການທໍາອິດຈາກບັນຊີລາຍຊື່ຈະຫາຍໄປແລະບໍ່ມີຫຍັງຈະເພີ່ມ. ), ແລະພວກເຂົາ ຈັດຮຽງສະ ເໝີ ໃນລໍາດັບຕັ້ງຊັນຂຶ້ນຂອງລະຫັດຄໍາຮ້ອງສະຫມັກ (task_date, id).

ຂັ້ນຕອນທີ 3: ການກັ່ນຕອງແລະ "ຂະຫຍາຍ" ບັນທຶກ

ໃນບາງແຖວຂອງການຄັດເລືອກ recursive ຂອງພວກເຮົາ, ບາງບັນທຶກ 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; -- берем только "непересекающие" записи

[ເບິ່ງທີ່ອະທິບາຍ.tensor.ru]

ດັ່ງນັ້ນ, ພວກເຮົາ ຊື້ຂາຍ 50% ຂອງຂໍ້ມູນອ່ານສໍາລັບ 20% ຂອງເວລາປະຕິບັດ. ນັ້ນແມ່ນ, ຖ້າທ່ານມີເຫດຜົນທີ່ຈະເຊື່ອວ່າການອ່ານອາດຈະໃຊ້ເວລາດົນ (ຕົວຢ່າງ, ຂໍ້ມູນມັກຈະບໍ່ຢູ່ໃນແຄດ, ແລະທ່ານຕ້ອງໄປຫາແຜ່ນສໍາລັບມັນ), ຫຼັງຈາກນັ້ນດ້ວຍວິທີນີ້, ທ່ານສາມາດຂຶ້ນກັບການອ່ານຫນ້ອຍລົງ. .

ໃນກໍລະນີໃດກໍ່ຕາມ, ເວລາປະຕິບັດໄດ້ດີກວ່າໃນທາງເລືອກທໍາອິດ "ໂງ່". ແຕ່ວ່າໃນ 3 ທາງເລືອກນີ້ຈະໃຊ້ອັນໃດແມ່ນຂຶ້ນກັບທ່ານ.

ແຫຼ່ງຂໍ້ມູນ: www.habr.com