🥇SQL HowTo: kunyora chinguva loop zvakananga mumubvunzo, kana "Elementary nhanho nhatu"

Nguva nenguva, basa rekutsvaga data rakabatana uchishandisa seti yemakiyi anomuka. kusvikira tawana nhamba inodiwa yezvinyorwa.

Iyo yakanyanya "hupenyu chaihwo" muenzaniso ndeyekuratidza 20 matambudziko ekare, vakanyorwa pazita revashandi (somuenzaniso, mukati mechikamu chimwe). Kune akasiyana manejimendi "mabhodhi" ane pfupiso pfupi dzenzvimbo dzebasa, musoro wakafanana unodiwa kazhinji.

Muchinyorwa chino tichatarisa kuisirwa muPostgreSQL ye "naive" mhinduro kudambudziko rakadaro, "yakangwara" uye yakaoma kwazvo algorithm. "loop" muSQL ine mamiriro ekubuda kubva kune yakawanikwa data, iyo inogona kubatsira zvose pakukura kwese uye kushandiswa mune zvimwe zviitiko zvakafanana.

Ngatitorei data rekuyedza kubva nyaya yapfuura. Kudzivirira marekodhi akaratidzwa kubva "kusvetuka" nguva nenguva kana maitiro akarongwa achienderana, wedzera indekisi yezvidzidzo nekuwedzera kiyi yekutanga. Panguva imwecheteyo, izvi zvinongozvipa kusarudzika uye zvinotivimbisa kuti kurongeka kwehurongwa hakuna kujeka:

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

Sezvazvakanyorwa, ndizvo zvakanyorwa;

Kutanga, ngatitorei iyo yakapusa vhezheni yechikumbiro, tichipfuudza maID evatambi array seyekuisa 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;

[tarisa ku explain.tensor.ru]

Zvinosuwisa zvishoma - takangoodha marekodhi makumi maviri chete, uye Index Scan yakadzosera kwatiri 960 mitsetse, iyo zvakare yaifanira kurongedzwa ... Ngatiedzei kuverenga zvishoma.

unnest + ARRAY

Kutanga kuchatibatsira kana tichida 20 chete dzakarongwa marekodhi, wobva wangoverenga haapfuure makumi maviri akarongwa munhevedzano imwe neimwe key. Kugona, indekisi yakakodzera (owner_id, task_date, id) tine.

Ngatishandisei nzira imwechete yekuburitsa uye "kuparadzira mumakoramu" yakakosha tafura rekodhi, sezvamu chinyorwa chekupedzisira. Isu tinogona zvakare kuisa kupeta kuita hurongwa tichishandisa basa 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; -- ... и тут - тоже

[tarisa ku explain.tensor.ru]

Oo, zviri nani kare! 40% nekukurumidza uye 4.5 nguva shoma data Ndaifanira kuiverenga.

Materialization yetafura rekodhi kuburikidza neCTERega ndikukwevera pfungwa dzako kune chokwadi chekuti mune zvimwe zviitiko Kuedza kushanda nekukurumidza neminda yerekodhi mushure mekuitsvaga mune subquery, pasina "kuiputira" muCTE, inogona kutungamira "wanza" InitPlan zvichienderana nehuwandu hweminda imwe chete iyi:

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

Rekodhi imwechete "yakatarisa kumusoro" 4 nguva ... Kusvikira PostgreSQL 11, maitiro aya anoitika nguva dzose, uye mhinduro ndeye "kuiputira" muCTE, inova muganhu wakakwana we optimizer mune idzi shanduro.

Recursive accumulator

Mushanduro yapfuura, muhuwandu tinoverenga 200 mitsetse nokuda kwezvinodiwa 20. Kwete 960, asi kunyange zvishoma - zvinogoneka here?

Ngatiedzei kushandisa zivo yatinoda ese makumi matanhatu zvinyorwa. Ndokunge, isu tichadzokorora kuverenga data chete kudzamara tasvika pamari yatinoda.

Danho 1: Kutanga List

Zviripachena, yedu "chinangwa" chinyorwa chemakumi maviri marekodhi chinofanira kutanga ne "yekutanga" marekodhi eimwe yedu muridzi_id kiyi. Naizvozvo, kutanga tichawana vakadaro “chekutanga” kune imwe neimwe yemakiyi uye woiwedzera pane rondedzero, tichiironga nenzira yatinoda - (task_date, id).

Danho 2: Tsvaga izvo "zvinotevera" zvinyorwa

Zvino kana tikatora yekutanga kupinda kubva pane yedu runyorwa uye kutanga "nhanho" mberi pamwe nendekisi kuchengetedza muridzi_id kiyi, saka ese akawanikwa marekodhi ndiwo chaiwo anotevera mukusarudzwa kunoguma. Chokwadi, chete kusvika tayambuka kiyi yebutt chechipiri chekupinda muchinyorwa.

Kana zvikazoitika kuti isu "takayambuka" rekodhi yechipiri, saka chinyorwa chekupedzisira chinoverengwa chinofanira kuwedzerwa pane rondedzero pane yekutanga (nemuridzi mumwechete_id), mushure mezvo tinorongazve rondedzero zvakare.

Kureva kuti, isu tinogara tichiwana kuti iyo rondedzero haina inopfuura imwechete yekupinda kune imwe neimwe yekiyi (kana mapindiro apera uye isu tisina "kuyambuka", ipapo yekutanga yekupinda kubva pane iyo rondedzero inongonyangarika uye hapana chichawedzerwa. ), uye ivo nguva dzose yakarongedzwa mukukwira kurongeka kwekiyi yekushandisa (task_date, id).

Danho rechitatu: sefa uye "wedzera" marekodhi

Mune mimwe mitsara yesarudzo yedu yekudzokorora, mamwe marekodhi rv zvakadhindwa - chekutanga tinowana se "kuyambuka muganho we2nd yekupinda rondedzero", tozoitsiva seyekutanga kubva pakurongwa. Saka chiitiko chekutanga chinoda kusefa.

Mubvunzo wekupedzisira unotyisa

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

[tarisa ku explain.tensor.ru]

Saka, isu yakatengeswa 50% yedata inoverengwa ye20% yenguva yekuuraya. Ndokunge, kana uine zvikonzero zvekutenda kuti kuverenga kunogona kutora nguva yakareba (semuenzaniso, iyo data kazhinji haisi mu cache, uye iwe unofanirwa kuenda kune dhisiki yayo), saka nenzira iyi unogona kuvimba zvishoma pakuverenga. .

Chero zvazvingaitika, nguva yekuuraya yakave iri nani pane yekutanga sarudzo ye "naive". Asi ndeipi pane idzi 3 sarudzo dzekushandisa zviri kwauri.

Source: www.habr.com

SQL HowTo: nyora chinguva-loop zvakananga mumubvunzo, kana "Elementary nhatu-nzira"

Sezvazvakanyorwa, ndizvo zvakanyorwa;

unnest + ARRAY

Recursive accumulator

Danho 1: Kutanga List

Danho 2: Tsvaga izvo "zvinotevera" zvinyorwa

Danho rechitatu: sefa uye "wedzera" marekodhi

Voeg kukanzura mhinduro