SQL HowTo: pisanje zanke while neposredno v poizvedbo ali »Elementarni trije koraki«

Občasno se pojavi naloga iskanja povezanih podatkov z uporabo nabora ključev. dokler ne dobimo zahtevanega skupnega števila zapisov.

Najbolj »resničen« primer je prikazati 20 najstarejših problemov, na seznamu na seznamu zaposlenih (na primer znotraj ene divizije). Za različne vodstvene »nadzorne plošče« s kratkimi povzetki delovnih področij je podobna tema pogosto potrebna.

V tem članku si bomo ogledali implementacijo v PostgreSQL "naivne" rešitve takšnega problema, "pametnejšega" in zelo kompleksnega algoritma “zanko” v SQL z izhodnim pogojem iz najdenih podatkov, ki je lahko koristen tako za splošni razvoj kot za uporabo v drugih podobnih primerih.

Vzemimo testni niz podatkov iz prejšnji članek. Da preprečite, da bi prikazani zapisi občasno "skočili", ko razvrščene vrednosti sovpadajo, razširite predmetno kazalo z dodajanjem primarnega ključa. Hkrati mu bo to takoj dalo edinstvenost in zagotovilo, da je vrstni red nedvoumen:

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

Kakor se sliši, tako se piše

Najprej skiciramo najpreprostejšo različico zahteve, ki posreduje ID-je izvajalcev polje kot vhodni 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;

[ogled na expand.tensor.ru]

Malo žalostno - naročili smo samo 20 zapisov, vendar nam jih je Index Scan vrnil 960 vrstic, ki jih je bilo potem treba tudi sortirati ... Potrudimo se brati manj.

unnest + ARRAY

Prvi premislek, ki nam bo pomagal, je, če potrebujemo samo 20 razvrščenih zapise, potem le preberite ne več kot 20 razvrščenih v istem vrstnem redu za vsakega ključ. dobro, primeren indeks (owner_id, task_date, id) imamo.

Uporabimo isti mehanizem za ekstrahiranje in "širjenje v stolpce" zapis integralne tabele, kot v zadnji članek. Uporabimo lahko tudi zlaganje v matriko s funkcijo 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; -- ... и тут - тоже

[ogled na expand.tensor.ru]

Oh, že veliko bolje! 40 % hitreje in 4.5-krat manj podatkov Moral sem ga prebrati.

Materializacija tabelnih zapisov preko CTENaj vas opozorim na dejstvo, da V nekaterih primerih Poskus takojšnjega dela s polji zapisa po iskanju v podpoizvedbi, ne da bi ga "ovili" v CTE, lahko vodi do "pomnoži" InitPlan sorazmerno s številom teh istih polj:

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

Isti zapis je bil "preiskan" 4-krat ... Do PostgreSQL 11 se to vedenje pojavlja redno in rešitev je, da ga "zavijemo" v CTE, kar je absolutna omejitev za optimizator v teh različicah.

Rekurzivni akumulator

V prejšnji različici smo skupaj prebrali 200 vrstic zaradi zahtevanih 20. Ne 960, ampak še manj - ali je to mogoče?

Poskusimo uporabiti znanje, ki ga potrebujemo skupaj 20 zapisi. To pomeni, da bomo ponavljali branje podatkov le, dokler ne dosežemo količine, ki jo potrebujemo.

1. korak: Štartni seznam

Očitno bi se moral naš "ciljni" seznam 20 zapisov začeti s "prvimi" zapisi za enega od naših ključev owner_id. Zato bomo najprej našli takšne »prvi« za vsakega od ključev in ga dodamo na seznam ter ga razvrstimo v želenem vrstnem redu - (datum_opravila, id).

2. korak: Poiščite »naslednje« vnose

Če zdaj vzamemo prvi vnos s seznama in začnemo »stopiti« naprej po indeksu ohranjanje ključa owner_id, potem so vsi najdeni zapisi točno naslednji v nastali izbiri. Seveda samo dokler ne prečkamo zadnjičnega ključa drugi vnos na seznamu.

Če se izkaže, da smo "prestopili" drugi rekord, potem zadnji prebrani vnos je treba dodati na seznam namesto prvega (z istim owner_id), nakar ponovno razvrstimo seznam.

To pomeni, da vedno dobimo, da seznam nima več kot enega vnosa za vsakega od ključev (če vnosov zmanjka in ne "križamo", bo prvi vnos s seznama preprosto izginil in nič ne bo dodano ), in so vedno urejeno v naraščajočem vrstnem redu aplikacijskega ključa (task_date, id).

3. korak: filtrirajte in »razširite« zapise

V nekaterih vrsticah našega rekurzivnega izbora nekaj zapisov rv se podvojijo - najprej najdemo, kot je "prečkanje meje 2. vnosa na seznamu", nato pa ga nadomestimo kot 1. s seznama. Zato je treba prvo pojavitev filtrirati.

Strašna zadnja poizvedba

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

[ogled na expand.tensor.ru]

Tako, mi trgoval 50 % branja podatkov za 20 % časa izvajanja. To pomeni, da če imate razloge za domnevo, da lahko branje traja dolgo (podatki na primer pogosto niso v predpomnilniku in jih morate iti na disk), potem ste lahko na ta način manj odvisni od branja .

Vsekakor se je čas izvedbe izkazal za boljšega kot pri "naivni" prvi možnosti. Toda katero od teh treh možnosti boste uporabili, je odvisno od vas.

Vir: www.habr.com