SQL HowTo: skrifa while-lykkju beint í fyrirspurnina, eða „Elementary three-way“

Reglulega kemur upp það verkefni að leita að tengdum gögnum með því að nota sett af lyklum. þar til við fáum tilskilinn heildarfjölda skráa.

Mest „raunverulega“ dæmið er að sýna 20 elstu vandamál, skráð á starfsmannalista (td innan einnar deildar). Fyrir ýmis „mælaborð“ stjórnenda með stuttum samantektum á vinnusvæðum er nokkuð oft krafist svipaðs efnis.

Í þessari grein munum við skoða útfærslu í PostgreSQL á „barnlausri“ lausn á slíku vandamáli, „snjallara“ og mjög flókið reiknirit „lykkja“ í SQL með útgönguskilyrði úr fundnum gögnum, sem getur nýst bæði fyrir almenna þróun og til notkunar í öðrum sambærilegum tilvikum.

Tökum prófunargagnasett úr fyrri grein. Til að koma í veg fyrir að birtar færslur „hoppi“ af og til þegar flokkuð gildi falla saman, stækkaðu efnisskrána með því að bæta við aðallykli. Á sama tíma mun þetta strax gefa því sérstöðu og tryggja okkur að flokkunarröðin sé ótvíræð:

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

Eins og heyrt er, svo er ritað

Fyrst skulum við teikna upp einfaldasta útgáfuna af beiðninni og senda auðkenni flytjenda fylki sem inntaksbreytu:

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;

[horfðu á explain.tensor.ru]

Svolítið sorglegt - við pöntuðum aðeins 20 plötur en Index Scan skilaði okkur 960 línur, sem þá þurfti líka að flokka... Reynum að lesa minna.

unnest + FJÖLKI

Fyrsta íhugunin sem mun hjálpa okkur er hvort við þurfum aðeins 20 flokkaðar skrár, þá er bara að lesa ekki fleiri en 20 raðað í sömu röð fyrir hvern lykill. Góður, viðeigandi vísitölu (owner_id, task_date, id) sem við höfum.

Við skulum nota sama kerfi til að draga út og „dreifa í dálka“ samþætt töfluskrá, eins og í síðasta greinin. Við getum líka beitt brjóta saman í fylki með því að nota aðgerðina 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; -- ... и тут - тоже

[horfðu á explain.tensor.ru]

Ó, miklu betra nú þegar! 40% hraðari og 4.5 sinnum minni gögn Ég varð að lesa hana.

Efnisgerð töfluskráa í gegnum CTELeyfðu mér að vekja athygli þína á því að í sumum tilvikum Tilraun til að vinna strax með reiti færslu eftir að hafa leitað að henni í undirfyrirspurn, án þess að „pakka“ henni inn í CTE, getur leitt til „margfalda“ InitPlan í hlutfalli við fjölda þessara sömu reita:

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

Sama skráin var „flett upp“ 4 sinnum... Fram að PostgreSQL 11 kemur þessi hegðun reglulega fyrir og lausnin er að „pakka“ henni inn í CTE, sem er algjör takmörk fyrir fínstillingu í þessum útgáfum.

Endurkvæmur rafgeymir

Í fyrri útgáfunni lesum við samtals 200 línur vegna tilskilinna 20. Ekki 960, en jafnvel minna - er það mögulegt?

Við skulum reyna að nota þá þekkingu sem við þurfum samtals 20 skrár. Það er, við munum endurtaka gagnalestur aðeins þar til við náum því magni sem við þurfum.

Skref 1: Byrjunarlisti

Augljóslega ætti „markmið“ listinn okkar með 20 færslum að byrja á „fyrstu“ færslunum fyrir einn af own_id lyklunum okkar. Þess vegna munum við fyrst finna slíkt „allra fyrsta“ fyrir hvern lykla og bæta því við listann, flokka það í þeirri röð sem við viljum - (verkefnisdagur, auðkenni).

Skref 2: Finndu „næstu“ færslurnar

Nú ef við tökum fyrstu færsluna af listanum okkar og byrjum „stíga“ lengra eftir vísitölunni með því að varðveita owner_id lykilinn, þá eru allar fundnar færslur nákvæmlega þær næstu í valinu sem myndast. Auðvitað bara þangað til við förum yfir rassalykilinn önnur færsla á listanum.

Ef það kemur í ljós að við „farum yfir“ annað met, þá síðasta færslunni sem lesið var ætti að bæta við listann í stað þeirrar fyrstu (með sama owner_id), eftir það raðum við listann aftur.

Það er, við fáum alltaf að listinn hefur ekki fleiri en eina færslu fyrir hvern lykla (ef færslurnar klárast og við „krysum ekki yfir“ þá mun fyrsta færslan af listanum einfaldlega hverfa og engu verður bætt við ), og þeir alltaf raðað í hækkandi röð forritslykils (verkefnisdagur, auðkenni).

Skref 3: sía og „stækka“ færslur

Í sumum af röðum endurkvæma úrvalsins okkar, sumar skrár rv eru afrituð - fyrst finnum við eins og að „fara yfir landamæri 2. færslu listans“ og setja hana síðan í staðinn fyrir þá 1. af listanum. Þannig að fyrsta tilvikið þarf að sía.

Hin óttalega lokafyrirspurn

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

[horfðu á explain.tensor.ru]

Þannig, við verslað 50% af lestri gagna í 20% af framkvæmdartíma. Það er að segja, ef þú hefur ástæðu til að ætla að lestur geti tekið langan tíma (t.d. eru gögnin oft ekki í skyndiminni og þú þarft að fara á disk fyrir það), þá geturðu treyst minna á lestur .

Í öllum tilvikum reyndist framkvæmdatíminn betri en í „barnlausum“ fyrsta valkostinum. En hver af þessum 3 valkostum á að nota er undir þér komið.

Heimild: www.habr.com