SQL HowTo: Kirjoita while-silmukka suoraan kyselyyn tai "Elementary kolmisuuntainen"

Ajoittain ilmaantuu tehtävä etsiä liittyviä tietoja käyttämällä avainsarjaa. kunnes saamme vaaditun kokonaismäärän tietueita.

"Todennäköisin" esimerkki on näyttää 20 vanhinta ongelmaa, listattu työntekijöiden listalla (esimerkiksi yhden divisioonan sisällä). Erilaisissa johtamisnäytöissä, joissa on lyhyt yhteenveto työalueista, tarvitaan melko usein samanlainen aihe.

Artikkelissa tarkastellaan sellaisen ongelman ratkaisemisen "naiivin" version, "älykkäämmän" ja erittäin monimutkaisen algoritmin, toteuttamista PostgreSQL:ssä. "silmukka" SQL:ssä, jossa on poistumisehto löydetyistä tiedoista, joka voi olla hyödyllinen sekä yleisessä kehityksessä että käytettäväksi muissa vastaavissa tapauksissa.

Otetaan testitietojoukko edellinen artikkeli. Jotta tulostietueet eivät "hyppää" ajoittain, kun lajitellut arvot täsmäävät, laajentaa aihehakemistoa lisäämällä ensisijainen avain. Samalla tämä antaa sille välittömästi ainutlaatuisuuden ja takaa meille lajittelujärjestyksen ainutlaatuisuuden:

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

Kuten kuullaan, niin kirjoitetaan

Ensin hahmotellaan pyynnön yksinkertaisin versio, joka välittää esiintyjien tunnukset matriisi syötteenä:

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;

[katso selittää.tensor.ru]

Hieman surullista - tilasimme vain 20 levyä, ja Index Scan palautti meidät 960 riviä, joka sitten piti myös lajitella... Ja yritetään lukea vähemmän.

unnest + ARRAY

Ensimmäinen huomio, joka auttaa meitä, on, jos tarvitsemme yhteensä 20 lajiteltua levyjä, lukeminen riittää enintään 20 lajiteltuna samaan järjestykseen kullekin avain. Hyvä, sopiva indeksi (owner_id, task_date, id) meillä on.

Käytetään samaa mekanismia poimimiseen ja "muuttumiseen sarakkeiksi" kiinteä taulukkotietue, kuten kohdassa viimeinen artikkeli. Ja käytä myös konvoluutiota taulukkoon funktion avulla 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; -- ... и тут - тоже

[katso selittää.tensor.ru]

Oi, se on jo paljon parempi! 40 % nopeampi ja 4.5 kertaa vähemmän dataa piti lukea.

Taulukkotietueiden materialisointi CTE:n kauttaHuomaan sen joissakin tapauksissa yritys käsitellä tietuekenttiä välittömästi sen jälkeen, kun se on haettu alikyselyssä ilman "käärimistä" CTE:hen, voi johtaa "kertoja" InitPlan verrannollinen näiden samojen kenttien lukumäärään:

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

Samaa tietuetta "haettiin" 4 kertaa... PostgreSQL 11:een asti tätä toimintaa esiintyy säännöllisesti, ja ratkaisu on "kääriä" CTE:hen, mikä on ehdoton raja optimoijalle näissä versioissa.

rekursiivinen akku

Edellisessä versiossa luimme yhteensä 200 riviä tarvittavan 20 vuoksi. Ei jo 960, mutta vielä vähemmän - onko mahdollista?

Yritetään hyödyntää tarvitsemamme tietoa yhteensä 20 levyjä. Toisin sanoen toistamme datavähennystä vain, kunnes vaadittava määrä on saavutettu.

Vaihe 1: Aloitusluettelo

On selvää, että "kohde"-luettelomme, jossa on 20 merkintää, pitäisi alkaa "ensimmäisillä" merkinnöillä jollekin omistajatunnus-avaimellemme. Siksi löydämme ensin sellaisen "erittäin ensimmäinen" jokaiselle avaimelle ja lisää se luetteloon lajittelemalla se haluamaasi järjestykseen - (tehtävän_päivämäärä, id).

Vaihe 2: etsi "seuraavat" tietueet

Jos nyt otamme ensimmäisen merkinnän luettelostamme ja aloitamme "astu" alaspäin indeksissä Tallenna omistajatunnus-avain, niin kaikki löydetyt tietueet ovat vain seuraavat tietueet tuloksena olevassa valinnassa. Tietenkin vain kunnes ylitämme käytetyn avaimen toinen merkintä luettelossa.

Jos kävi ilmi, että "ylimme" toisen merkinnän, niin viimeinen luettu merkintä tulee lisätä luetteloon ensimmäisen sijasta (samalla omistajan_tunnuksella), minkä jälkeen lajittelemme luettelon uudelleen.

Toisin sanoen saamme aina, että luettelossa ei ole enempää kuin yksi merkintä kullekin avaimelle (jos merkinnät ovat ohi, emmekä ole ylittäneet, ensimmäinen merkintä yksinkertaisesti katoaa luettelosta eikä siihen lisätä mitään ), ja he aina lajiteltu sovellusavaimen nousevassa järjestyksessä (tehtävän_päivämäärä, id).

Vaihe 3: suodata ja "laajenna" tietueet

Rekursiivisen valikoimamme rivien osassa joitain tietueita rv ovat päällekkäisiä - ensin löydämme esimerkiksi "luettelon toisen kohdan rajan ylittäminen", ja sitten korvaamme luettelon ensimmäisenä. Ja niin ensimmäinen esiintyminen tulisi suodattaa pois.

Pelätty viimeinen kysely

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

[katso selittää.tensor.ru]

Näin ollen me vaihdettiin 50 % datan lukemista 20 %:iin suoritusajasta. Eli jos sinulla on syytä uskoa, että lukeminen voi olla pitkä (esimerkiksi dataa ei usein ole välimuistissa, ja sinun on mentävä levylle sitä varten), voit tällä tavalla luottaa lukemiseen vähemmän.

Joka tapauksessa suoritusaika osoittautui paremmaksi kuin "naiivissa" ensimmäisessä vaihtoehdossa. Mutta kumpaa näistä kolmesta vaihtoehdosta käyttää, on sinun päätettävissäsi.

Lähde: will.com