SQL HowTo: kirjutame while-tsükli otse päringusse, ehk "Elementaarne kolmekäiguline"

Aeg-ajalt tekib vajadus leida seotud andmeid võtmekomplekti põhjal, kuni kogume vajaliku salvestuste koguse.

Kõige „elulähedasem” näide on välja tuua 20 vanimat ülesannet, mis on loetletud töötajate nimekirjas (näiteks, ühe osakonna raames). Erinevates juhtimis „dashboard’ides” lühikeste kokkuvõtetega tegevusvaldkondade kohta on sarnase teema vajadus pigem sagedane.

Artiklis vaatleme PostgreSQL-l põhineva „naivse” variandi rakendamist selle ülesande lahendamiseks, „nutikama” ja täiesti keerulise algoritmi „tsükliga” SQL-is, mille väljunditingimus tuleneb leitud andmetest, mis võib olla kasulik nii üldiseks arenguks kui ka rakendamiseks teistes sarnastes olukordades.

Võtame testkomplekti andmed eelnevas artiklis. Et väljundandmed ei „hüppaks” kord-korralt, kui sorteeringuväärtused kattuvad, laiendame teemade indeksit lisades esmase võtme. See annab kohe ka sellele ainulaadsuse ja garanteerib meile sorteerimisjärjese järjepidevuse:

CREATE INDEX ON task(owner_id, task_date, id);
-- ja vana - eemaldame
DROP INDEX task_owner_id_task_date_idx;

Kuidas see kõlab, nii see kirjutatakse

Esmalt koostame kõige lihtsama päringu variandi, edastades täitjate ID-d massiivina sisendparameetrina:

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;

[vaata explain.tensor.ru]

Veidi südantlõhestav - tellisime vaid 20 salvestust, aga Index Scan tagastas meile 960 rida, mida tuli veel ka sorteerida ... Aga proovime lugeda vähem.

unnest + ARRAY

Esimene mõte, mis aitab, on see, et kui meil on vaja kuni 20 sorteeritud salvestust, siis piisab lugemisest mitte rohkem kui 20 sorteeritud sama järjekorra järgi iga võtme kohta. Õnneks, sobiv indeks (owner_id, task_date, id) on meil olemas.

Kasutame sama mehhanismi andmete väljatoomiseks ja „veeretamiseks veergudesse” täieliku tabelirekordi, nagu ka eelmisel artiklil. Samuti rakendame massiivi kokkuvõtmist funktsiooni ARRAY():

WITH T AS (
  SELECT
    unnest(ARRAY(
      SELECT
        t
      FROM
        task t
      WHERE
        owner_id = unnest
      ORDER BY
        task_date, id
      LIMIT 20 -- piirame siin...
    )) 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; -- ... ja siin - samuti

[vaata explain.tensor.ru]

Oh, juba palju parem! 40% kiiremini ja 4.5 korda vähem andmeid pidi lugema.

Tabelite salvestuste materialiseerimine CTE kauduMainin, et mõnel juhul katse kohe töötada salvestuse väljadega pärast selle otsimist alampäringus, ilma CTE-sse "mässimata", võib viia "korrutamise" InitPlani proportsionaalselt nende väljade arvule:

SELECT
  ((
    SELECT
      t
    FROM
      task t
    WHERE
      owner_id = 1
    ORDER BY
      task_date, id
    LIMIT 1
  ).*);

Tulemus  (kulud=4.77..4.78 read=1 laius=16) (reaalne aeg=0.063..0.063 read=1 tsüklid=1)
  Puhvrid: jagatud hit=16
  InitPlan 1 (tagastab $0)
    ->  Limit  (kulud=0.42..1.19 read=1 laius=48) (reaalne aeg=0.031..0.032 read=1 tsüklid=1)
          Puhvrid: jagatud hit=4
          ->  Indeksi skaneerimine kasutades task_owner_id_task_date_id_idx ülesande t  (kulud=0.42..387.57 read=500 laius=48) (reaalne aeg=0.030..0.030 read=1 tsüklid=1)
                Indeksi tingimus: (owner_id = 1)
                Puhvrid: jagatud hit=4
  InitPlan 2 (tagastab $1)
    ->  Limit  (kulud=0.42..1.19 read=1 laius=48) (reaalne aeg=0.008..0.009 read=1 tsüklid=1)
          Puhvrid: jagatud hit=4
          ->  Indeksi skaneerimine kasutades task_owner_id_task_date_id_idx ülesande t_1  (kulud=0.42..387.57 read=500 laius=48) (reaalne aeg=0.008..0.008 read=1 tsüklid=1)
                Indeksi tingimus: (owner_id = 1)
                Puhvrid: jagatud hit=4
  InitPlan 3 (tagastab $2)
    ->  Limit  (kulud=0.42..1.19 read=1 laius=48) (reaalne aeg=0.008..0.008 read=1 tsüklid=1)
          Puhvrid: jagatud hit=4
          ->  Indeksi skaneerimine kasutades task_owner_id_task_date_id_idx ülesande t_2  (kulud=0.42..387.57 read=500 laius=48) (reaalne aeg=0.008..0.008 read=1 tsüklid=1)
                Indeksi tingimus: (owner_id = 1)
                Puhvrid: jagatud hit=4"
  InitPlan 4 (tagastab $3)
    ->  Limit  (kulud=0.42..1.19 read=1 laius=48) (reaalne aeg=0.009..0.009 read=1 tsüklid=1)
          Puhvrid: jagatud hit=4
          ->  Indeksi skaneerimine kasutades task_owner_id_task_date_id_idx ülesande t_3  (kulud=0.42..387.57 read=500 laius=48) (reaalne aeg=0.009..0.009 read=1 tsüklid=1)
                Indeksi tingimus: (owner_id = 1)
                Puhvrid: jagatud hit=4

Sama kirje "otsiti" 4 korda… Fino PostgreSQL 11 see käitumine esineb regulaarselt, ja lahenduseks on "mässimine" CTE-sse, mis on tingimatu piir laseriteks nende versioonide puhul.

Rekursiivne akumulator

Eelmine variant luges kokku 200 rida vajalikeks 20. Juba mitte 960, aga siiski vähem — kas on võimalik?

Proovime kasutada teadmist, et meil on vaja kokku 20 salvestust. See tähendab, et me kavatseme andmete lugemist iteratsiooniga ainult vajaliku arvu saavutamiseks.

Samm 1: algne nimekiri

On ilmne, et meie "siht" 20 salvestuse nimekiri peab algama "esimestest" salvestustest ühe meie owner_id võtme kohaselt. Seega otsime esmalt sellised "vara varased" iga võtme kohta ja paneme nende nimekirja, järjestades selle vastavalt soovitule — (task_date, id).

Samm 2: leiame "järgmised" salvestused

Nüüd, kui võtame meie nimekirjast esimese salvestuse ja hakkame "edasi liikuma" indeksi järgi säilitades owner_id võtme, siis kõik leitud salvestused on just järgmised tulemusvalimisse. Loomulikult ainult kuni me ületame rakendusvõtme teise kirje loendis.

Kui juhtub, et me «ületame» teise kirje, siis viimane loetud kirje peab olema lisatud loendisse esimesena (sama owner_id-ga), pärast mida loend tuleb uuesti sorteerida.

See tähendab, et meil on kogu aeg loendis mitte rohkem kui üks kirje iga võtme kohta (kui kirjed on otsa saanud, aga me ei ole «ületanud», siis kaob loendist esimene kirje lihtsalt ja midagi ei lisandu), ning need on alati sorteeritud rakendusvõtme (task_date, id) kasvava järjekorra järgi.

Samm 3: filteerime ja «laiendame» kirjeid

Mõned kirjed meie rekursiivses valikus rv korduvad — esmalt leiame need, mis «ületavad 2. kirje piiri», ja siis asetame need loendi 1-ks. Nii et esimene ilmumine tuleb filtreerida.

Hirmutav lõplik päring

KORDINATIVSE KUIDAS T KASUTAMISEKS (
  -- #1 : loome nimekirja "esimeste" kirjeid igas võtmes rühmas
  KORDINATIV WRAP AS ( -- "materialiseerime" kirjed, et väljadele viitamine ei kutsuks esile InitPlan/SUBPLAN kordust
    KORDINATIV T AS (
      VALI
        (
          VALI
            r
          KUST
            ülesanne r
          KUS
            omanik_id = unnest
          KORDA
            ülesande_aeg, id
          PIIR 1
        ) r
      KUST
        unnest('{1,2,4,8,16,32,64,128,256,512}'::integer[])
    )
    VALI
      array_agg(r KORDA (r).ülesande_aeg, (r).id) nimekiri -- sorteerime nimekirja soovitud järjekorras
    KUST
      T
  )
  VALI
    nimekiri
  , nimekiri[1] rv
  , FALSE not_cross
  , 0 suurus
  KUST
    wrap
UNION ALL
  -- #2 : loeme läbi 1. järjestuse võtme kirjeid, kuni ületame 2. kirjete piiri
  VALI
    JUHTUM
      -- kui 1. kirje ei leitud
      KUI X._r ON NOT DISTINCT FROM NULL SIIS
        T.nimekiri[2:] -- eemaldame selle nimekirjast
      -- kui me EI ületanud 2. kirje rakenduslikku võtme
      KUI X.not_cross SIIS
        T.nimekiri -- lihtsalt esitame sama nimekirja ilma muudatusteta
      -- kui nimekirjas pole enam 2. kirjet
      KUI T.nimekiri[2] ON NULL SIIS
        -- lihtsalt tagastame tühja nimekirja
        '{}'
      -- sorteerime sõnastiku uuesti, eemaldades 1. kirje ja lisades viimase leitud
      MUUL JUHTUVAL (
        VALI
          coalesce(T.nimekiri[2] || array_agg(r KORDA (r).ülesande_aeg, (r).id), '{}')
        KUST
          unnest(T.nimekiri[3:] || X._r) r
      )
    LÕPUD
  , X._r
  , X.not_cross
  , T.suurus + X.not_cross::integer
  KUST
    T
  , LATERAL(
      KORDINATIV WRAP AS ( -- "materialiseerime" kirje
        VALI
          JUHTUM
            -- kui tõeliselt "ületanud" 2. kirje
            KUI MITTE T.not_cross
              -- siis vajalik kirje on nimekirja esimene
              SIIS T.nimekiri[1]
            MUUL JUHTUVAL ( -- kui ei ületanud, siis võti püsib nagu eelnevas kirjes - toetume sellele
              VALI
                _r
              KUST
                ülesanne _r
              KUS
                omanik_id = (rv).omanik_id JA
                (ülesande_aeg, id) > ((rv).ülesande_aeg, (rv).id)
              KORDA
                ülesande_aeg, id
              PIIR 1
            )
          LÕPUD _r
      )
      VALI
        _r
      , JUHTUM
          -- kui 2. kirjet pole enam nimekirjas, kuid leidisime midagi
          KUI nimekiri[2] ON NULL JA _r ON DISTINCT FROM NULL SIIS
            TÕE
          MUUL JUHTUVAL -- ei leidnud midagi või "ületasime"
            coalesce(((_r).ülesande_aeg, (_r).id) < ((nimekiri[2]).ülesande_aeg, (nimekiri[2]).id), FALSE)
        LÕPUD not_cross
      KUST
        wrap
    ) X
  KUS
    T.suurus < 20 JA -- piirame siin arvu
    T.nimekiri IS DISTINCT FROM '{}' -- või kuni nimekiri ei ole otsa saanud
)
-- #3 : "laiendame" kirjeid - järjestus on garantii ülesehituse kaudu
VALI
  (rv).* 
KUST
  T
KUS
  not_cross; -- võtame ainult "mitteületavad" kirjed

[vaata explain.tensor.ru]

Seega, me vahetasime 50% andmete lugemist 20% täitmisajagaSee, if you have reasons to believe that reading might be lengthy (for example, data is often not cached and needs to be retrieved from the disk), then this method can reduce dependency on reading.

In any case, the execution time turned out to be better than in the 'naive' first version. But which of these 3 options to use is up to you to decide.

Allikas: habr.com