SQL HowTo: pagsusulat ng while loop nang direkta sa query, o "Elementary three-step"

Pana-panahon, ang gawain ng paghahanap para sa mga nauugnay na data gamit ang isang hanay ng mga susi ay lumitaw. hanggang makuha namin ang kinakailangang kabuuang bilang ng mga talaan.

Ang pinaka "tunay na buhay" na halimbawa ay ang pagpapakita 20 pinakalumang problema, nakalista sa listahan ng mga empleyado (halimbawa, sa loob ng isang dibisyon). Para sa iba't ibang "dashboard" ng pamamahala na may maikling buod ng mga lugar ng trabaho, ang isang katulad na paksa ay kinakailangan nang madalas.

Sa artikulong ito titingnan natin ang pagpapatupad sa PostgreSQL ng isang "walang muwang" na solusyon sa naturang problema, isang "mas matalino" at napakakomplikadong algorithm "loop" sa SQL na may kondisyon sa paglabas mula sa nahanap na data, na maaaring maging kapaki-pakinabang para sa pangkalahatang pag-unlad at para sa paggamit sa iba pang katulad na mga kaso.

Kumuha tayo ng set ng data ng pagsubok mula sa nakaraang artikulo. Upang maiwasan ang mga ipinapakitang talaan mula sa "paglukso" paminsan-minsan kapag ang mga pinagsunod-sunod na halaga ay nag-tutugma, palawakin ang index ng paksa sa pamamagitan ng pagdaragdag ng pangunahing key. Kasabay nito, agad itong magbibigay ng kakaiba at magagarantiya sa amin na ang pagkakasunud-sunod ng pag-uuri ay hindi malabo:

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

Kung paanong narinig, gayon din ang nakasulat

Una, i-sketch natin ang pinakasimpleng bersyon ng kahilingan, na ipinapasa ang mga ID ng mga gumaganap array bilang input parameter:

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;

[tingnan sa explain.tensor.ru]

Medyo malungkot - nag-order lang kami ng 20 record, ngunit ibinalik ito sa amin ng Index Scan 960 linya, na noon ay kailangan ding pagbukud-bukurin... Subukan nating magbasa nang kaunti.

unnest + ARRAY

Ang unang pagsasaalang-alang na makakatulong sa atin ay kung kailangan natin 20 lang ang nakaayos records, tapos basahin lang hindi hihigit sa 20 na pinagsunod-sunod sa parehong pagkakasunud-sunod para sa bawat isa susi. mabuti, angkop na index (owner_id, task_date, id) mayroon kami.

Gamitin natin ang parehong mekanismo para sa pagkuha at "pagkalat sa mga hanay" mahalagang talaan ng talahanayan, tulad ng sa huling artikulo. Maaari din nating ilapat ang pagtitiklop sa isang array gamit ang function 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; -- ... и тут - тоже

[tingnan sa explain.tensor.ru]

Oh, mas mabuti na! 40% mas mabilis at 4.5 beses na mas kaunting data Kinailangan kong basahin ito.

Pagpapatupad ng mga talaan ng talahanayan sa pamamagitan ng CTEHayaan mong ituon ko ang iyong pansin sa katotohanang iyon sa ibang Pagkakataon Ang isang pagtatangka na agad na magtrabaho kasama ang mga patlang ng isang talaan pagkatapos na hanapin ito sa isang subquery, nang hindi "binalot" ito sa isang CTE, ay maaaring humantong sa "multiply" InitPlan proporsyonal sa bilang ng mga parehong field na ito:

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

Ang parehong record ay "hinanap" ng 4 na beses... Hanggang sa PostgreSQL 11, ang gawi na ito ay nangyayari nang regular, at ang solusyon ay "i-wrap" ito sa isang CTE, na isang ganap na limitasyon para sa optimizer sa mga bersyong ito.

Recursive accumulator

Sa nakaraang bersyon, sa kabuuan ay nabasa natin 200 linya para sa kapakanan ng kinakailangang 20. Hindi 960, ngunit kahit na mas mababa - posible ba?

Subukan nating gamitin ang kaalaman na kailangan natin kabuuang 20 mga tala. Ibig sabihin, uulitin natin ang pagbabasa ng data hanggang sa maabot natin ang halagang kailangan natin.

Hakbang 1: Panimulang Listahan

Malinaw, ang aming "target" na listahan ng 20 talaan ay dapat magsimula sa "unang" mga tala para sa isa sa aming mga owner_id key. Samakatuwid, mahahanap muna natin ang ganoon "napaka una" para sa bawat isa sa mga susi at idagdag ito sa listahan, pag-uuri-uriin ito sa pagkakasunud-sunod na gusto namin - (task_date, id).

Hakbang 2: Hanapin ang "susunod" na mga entry

Ngayon kung kukunin namin ang unang entry mula sa aming listahan at magsimula "hakbang" pa kasama ang index pinapanatili ang owner_id key, pagkatapos ang lahat ng nahanap na tala ay eksaktong susunod sa resultang pagpili. Syempre, lang hanggang sa tumawid kami sa butt key pangalawang entry sa listahan.

Kung ito ay lumabas na "tinawid" namin ang pangalawang rekord, kung gayon ang huling entry na nabasa ay dapat idagdag sa listahan sa halip na ang una (na may kaparehong owner_id), pagkatapos ay muli naming inuri-uriin muli ang listahan.

Iyon ay, palagi nating nakukuha na ang listahan ay may hindi hihigit sa isang entry para sa bawat isa sa mga susi (kung ang mga entry ay maubusan at hindi tayo "tumawid", kung gayon ang unang entry mula sa listahan ay mawawala lang at walang maidaragdag. ), at sila laging nakaayos sa pataas na pagkakasunud-sunod ng application key (task_date, id).

Hakbang 3: i-filter at "palawakin" ang mga tala

Sa ilan sa mga row ng aming recursive selection, ilang record rv ay nadoble - unang nakita namin tulad ng "pagtawid sa hangganan ng 2nd entry ng listahan", at pagkatapos ay palitan ito bilang ang 1st mula sa listahan. Kaya kailangang i-filter ang unang pangyayari.

Ang kinatatakutang huling query

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; -- берем только "непересекающие" записи

[tingnan sa explain.tensor.ru]

Kaya, kami ipinagpalit ang 50% ng mga nabasang data para sa 20% ng oras ng pagpapatupad. Iyon ay, kung mayroon kang mga dahilan upang maniwala na ang pagbabasa ay maaaring tumagal ng mahabang panahon (halimbawa, ang data ay madalas na wala sa cache, at kailangan mong pumunta sa disk para dito), kung gayon sa paraang ito maaari kang umasa nang mas kaunti sa pagbabasa .

Sa anumang kaso, ang oras ng pagpapatupad ay naging mas mahusay kaysa sa "naive" na unang pagpipilian. Ngunit alin sa 3 opsyong ito ang iyong gagamitin.

Pinagmulan: www.habr.com