SQL HowTo: while begizta bat idaztea zuzenean kontsultan, edo "Oinarrizko hiru urrats"

Aldian-aldian, erlazionatutako datuak gako-multzo bat erabiliz bilatzeko zeregina sortzen da. beharrezko erregistro kopurua lortu arte.

"Bizitza errealeko" adibiderik handiena erakustea da 20 arazo zaharrenak, zerrendatua langileen zerrendan (adibidez, zatiketa baten barruan). Lan-arloen laburpen laburrak dituzten hainbat kudeaketa "arbel"etarako, antzeko gaia behar da askotan.

Artikulu honetan, PostgreSQL-n halako arazo baten konponbide "inozoa" ezartzea aztertuko dugu, algoritmo "adimenttsuago" eta oso konplexua. "begizta" SQL-n aurkitutako datuetatik irteteko baldintza batekin, garapen orokorrerako zein antzeko beste kasu batzuetan erabiltzeko baliagarria izan daitekeena.

Har dezagun probako datu multzo bat aurreko artikulua. Bistaratzen diren erregistroek noizean behin "jauzi" egin ez dezaten ordenatutako balioak bat datozenean, zabaldu gaiaren indizea gako nagusi bat gehituz. Aldi berean, horrek berehala emango dio berezitasuna eta sailkapen-ordena anbiguoa dela bermatuko digu:

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

Entzuten den bezala, hala idatzita dago

Lehenik eta behin, zirriborratu dezagun eskaeraren bertsiorik errazena, interpreteen IDak pasatuz array sarrerako parametro gisa:

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;

[ikusi explain.tensor.ru helbidean]

Pena samarra: 20 disko baino ez ditugu eskatu, baina Index Scan-ek itzuli digu 960 lerro, gero ere ordenatu beharra zegoen... Saia gaitezen gutxiago irakurtzen.

unnest + ARRAY

Lagunduko digun lehenengo kontua behar badugu 20 bakarrik sailkatuta erregistroak, gero irakurri besterik ez gehienez 20 ordena berean ordenatuta bakoitzeko giltza. Ongi, indize egokia (owner_id, task_date, id) dugu.

Erabili dezagun mekanismo bera ateratzeko eta "zutabeetan zabaltzeko" taularen erregistro integrala, bezala azken artikulua. Funtzioa erabiliz array batean tolestura ere aplika dezakegu 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; -- ... и тут - тоже

[ikusi explain.tensor.ru helbidean]

Ai, askoz hobeto jada! %40 azkarrago eta 4.5 aldiz datu gutxiago irakurri behar izan nuen.

Taulen erregistroen materializazioa CTE bidezUtzidazu arreta deitzen dizudan hori kasu batzuetan Azpikontsulta batean bilatu ondoren erregistro baten eremuak berehala lan egiteko saiakerak, CTE batean "bilduta" gabe, sor dezake. "biderkatu" InitPlan eremu bereko kopuruarekiko proportzionala:

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

Erregistro bera 4 aldiz "begiratu" zen... PostgreSQL 11ra arte, portaera hau aldizka gertatzen da, eta irtenbidea CTE batean "biltzea" da, bertsio hauetako optimizatzailearentzat erabateko muga baita.

Metagailu errekurtsiboa

Aurreko bertsioan, guztira irakurri dugu 200 lerro beharrezko 20. Ez 960, baina are gutxiago - posible al da?

Saia gaitezen behar ditugun ezagutzak erabiltzen guztira 20 erregistroak. Hau da, datuen irakurketa errepikatuko dugu behar dugun kopurura iritsi arte.

1. urratsa: Hasierako zerrenda

Jakina, gure 20 erregistroko "helburu" zerrenda gure jabe_id gakoetako baten "lehen" erregistroekin hasi beharko litzateke. Hori dela eta, lehenik horrelakoak aurkituko ditugu "oso lehena" tekla bakoitzeko eta gehitu zerrendara, nahi dugun ordenan ordenatuz - (zeregin_data, id).

2. urratsa: Bilatu "hurrengo" sarrerak

Orain gure zerrendako lehen sarrera hartu eta hasten badugu "urrats" gehiago aurkibidean zehar jabea_id gakoa gordez, orduan aurkitutako erregistro guztiak emaitzaren hautapenean hurrengoak dira. Jakina, bakarrik ipurdia giltza gurutzatu arte zerrendako bigarren sarrera.

Bigarren diskoa “gurutzatu” genuela ateratzen bada, orduan irakurritako azken sarrera gehitu behar zaio zerrendara lehenengoari ordez (jabe_id berarekin), ondoren zerrenda berriro ordenatuko dugu.

Hau da, beti lortzen dugu zerrendak sarrera bat baino gehiago ez duela gako bakoitzeko (sarrerak agortzen badira eta ez badugu “gurutzatzen”, zerrendako lehen sarrera besterik gabe desagertuko da eta ez da ezer gehituko ), eta haiek beti ordenatuta aplikazioaren gakoaren goranzko ordenan (zeregin_data, id).

3. urratsa: iragazi eta "zabaldu" erregistroak

Gure hautapen errekurtsiboko errenkada batzuetan, erregistro batzuk rv bikoiztu egiten dira - lehenik "zerrendako 2. sarreraren muga zeharkatzea" adibidez aurkituko dugu, eta gero zerrendako 1. gisa ordezkatu. Beraz, lehen agerraldia iragazi behar da.

Azken kontsulta beldurgarria

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

[ikusi explain.tensor.ru helbidean]

Horrela, guk datuen irakurketen % 50 exekuzio denboraren % 20an negoziatu zuen. Hau da, irakurketak denbora luzea izan dezakeela sinesteko arrazoiak badituzu (adibidez, askotan datuak ez daude cachean, eta diskora joan behar duzu horretarako), orduan, modu honetan, irakurketaren menpe gutxiago egon zaitezke. .

Edonola ere, exekuzio-denbora hobea izan zen lehen aukera "inozoan" baino. Baina 3 aukera hauetatik zein erabili zure esku dago.

Iturria: www.habr.com