SQL HowTo: ikteb while-loop direttament fil-mistoqsija, jew "Elementary three-way"

Perjodikament, tqum il-kompitu ta 'tiftix għal data relatata bl-użu ta' sett ta 'ċwievet. sakemm nikseb in-numru totali meħtieġ ta 'rekords.

L-aktar eżempju ta '"ħajja reali" huwa li juri 20 eqdem problema, elenkati fuq il-lista tal-impjegati (pereżempju, fi ħdan diviżjoni waħda). Għal diversi "dashboards" ta 'ġestjoni b'sommarji qosra ta' oqsma tax-xogħol, suġġett simili huwa meħtieġ spiss.

F'dan l-artikolu se nħarsu lejn l-implimentazzjoni f'PostgreSQL ta 'soluzzjoni "naive" għal problema bħal din, algoritmu "aktar intelliġenti" u kumpless ħafna "loop" fl-SQL b'kundizzjoni ta 'ħruġ mid-dejta misjuba, li jistgħu jkunu utli kemm għall-iżvilupp ġenerali kif ukoll għall-użu f'każijiet oħra simili.

Ejja nieħdu sett tad-dejta tat-test minn artiklu preċedenti. Biex tevita li r-rekords murija "jaqbżu" minn żmien għal żmien meta l-valuri magħżula jikkoinċidu, tespandi l-indiċi tas-suġġett billi żżid ċavetta primarja. Fl-istess ħin, dan immedjatament jagħtiha l-uniċità u jiggarantilna li l-ordni tal-issortjar ma tkunx ambigwa:

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

Kif jinstema, hekk miktub

L-ewwel, ejja nfasslu l-aktar verżjoni sempliċi tat-talba, billi ngħaddu l-IDs tal-artisti array bħala parametru tad-dħul:

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;

[ħares lejn explic.tensor.ru]

Xi ftit imdejjaq - ordnajna biss 20 rekord, iżda Index Scan rritornaha lilna 960 linja, li mbagħad kellhom jiġu magħżula wkoll... Ejja nippruvaw naqraw inqas.

unnest + ARRAY

L-ewwel konsiderazzjoni li tgħinna hija jekk ikollna bżonn 20 biss magħżula rekords, imbagħad aqra biss mhux aktar minn 20 magħżula fl-istess ordni għal kull wieħed ċavetta. Tajjeb, indiċi adattat (owner_id, task_date, id) għandna.

Ejja nużaw l-istess mekkaniżmu għall-estrazzjoni u "tifrix f'kolonni" rekord integrali tal-mejda, bħal fi l-aħħar artikolu. Nistgħu wkoll napplikaw tiwi f'firxa bl-użu tal-funzjoni 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; -- ... и тут - тоже

[ħares lejn explic.tensor.ru]

Oh, ħafna aħjar diġà! 40% aktar mgħaġġla u 4.5 darbiet inqas dejta Kelli naqrah.

Materjalizzazzjoni ta' rekords ta' tabella permezz ta' CTEĦa niġbed l-attenzjoni tiegħek għall-fatt li f'xi każijiet Tentattiv biex taħdem immedjatament mal-oqsma ta' rekord wara li tfittix għaliha f'subquery, mingħajr ma "geżwer" f'CTE, jista' jwassal għal "immultiplika" InitPlan proporzjonali għan-numru ta' dawn l-istess oqsma:

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

L-istess rekord kien "ħares 'il fuq" darbiet 4... Sa PostgreSQL 11, din l-imġieba sseħħ regolarment, u s-soluzzjoni hija li "kebbeb" f'CTE, li huwa limitu assolut għall-ottimizzatur f'dawn il-verżjonijiet.

Akkumulatur rikorsiv

Fil-verżjoni preċedenti, b'kollox naqraw 200 linja għall-fini tal-ħtieġa 20. Mhux 960, iżda saħansitra inqas - huwa possibbli?

Ejja nippruvaw nużaw l-għarfien li għandna bżonn b’kollox 20 rekords. Jiġifieri se ntennu l-qari tad-dejta biss sakemm nilħqu l-ammont li neħtieġu.

Pass 1: Lista tal-Bidu

Ovvjament, il-lista "mira" tagħna ta '20 rekord għandha tibda bl-"ewwel" rekords għal waħda miċ-ċwievet tagħna sid_id. Għalhekk, l-ewwel se nsibu tali "l-ewwel ħafna" għal kull ċwievet u żidha mal-lista, issortjarha fl-ordni li rridu - (task_date, id).

Pass 2: Sib l-iskrizzjonijiet "li jmiss".

Issa jekk nieħdu l-ewwel dħul mil-lista tagħna u nibdew "pass" aktar tul l-indiċi il-preservazzjoni taċ-ċavetta owner_id, allura r-rekords kollha misjuba huma eżattament dawk li jmiss fl-għażla li tirriżulta. Naturalment, biss sakemm naqsmu l-butt key it-tieni daħla fil-lista.

Jekk jirriżulta li aħna "qassim" it-tieni rekord, allura l-aħħar entrata li tinqara għandha tiżdied mal-lista minflok l-ewwel waħda (bl-istess sid_id), u wara nerġgħu nisortraw il-lista mill-ġdid.

Jiġifieri, dejjem insiru li l-lista m'għandhiex aktar minn entrata waħda għal kull ċwievet (jekk l-entrati jispiċċaw u ma “naqsbux”, allura l-ewwel entrata mil-lista sempliċement tisparixxi u xejn ma jkun miżjud ), u huma dejjem magħżula f'ordni axxendenti taċ-ċavetta tal-applikazzjoni (task_date, id).

Pass 3: iffiltra u "tespandi" r-rekords

F'xi wħud mir-ringieli tal-għażla rikorsiv tagħna, xi rekords rv huma duplikati - l-ewwel insibu bħal "taqsam il-fruntiera tat-tieni entrata tal-lista", u mbagħad tibdilha bħala l-ewwel mil-lista. Allura l-ewwel okkorrenza trid tiġi ffiltrata.

Il-mistoqsija finali dreaded

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

[ħares lejn explic.tensor.ru]

Għalhekk, aħna innegozjat 50% tad-data qari għal 20% tal-ħin ta 'eżekuzzjoni. Jiġifieri, jekk għandek raġunijiet biex temmen li l-qari jista 'jieħu żmien twil (per eżempju, id-dejta ħafna drabi ma tkunx fil-cache, u trid tmur fuq disk għaliha), allura b'dan il-mod tista' tiddependi inqas fuq il-qari .

Fi kwalunkwe każ, il-ħin ta 'eżekuzzjoni rriżulta li kien aħjar milli fl-ewwel għażla "naive". Imma liema minn dawn it-3 għażliet għandek tuża hija f'idejk.

Sors: www.habr.com