SQL HowTo: tusi saʻo se taimi-faʻailoga i le fesili, poʻo le "Tolu-ala Tulaga"

Mai lea taimi i lea taimi, o le galuega o le suʻeina o faʻamatalaga e fesoʻotaʻi ma faʻaaogaina se seti o ki e tulaʻi mai. se'ia tatou maua le aofa'i atoa o fa'amaumauga.

O le faʻataʻitaʻiga sili ona "ola moni" o le faʻaalia 20 faafitauli tuai, lisiina i luga o le lisi o tagata faigaluega (mo se faʻataʻitaʻiga, i totonu o le tasi vaega). Mo le tele o pulega "dashboards" faʻatasi ai ma otootoga puʻupuʻu o vaega o galuega, e manaʻomia soo se mataupu tutusa.

I lenei tusiga o le a tatou vaʻavaʻai i le faʻatinoga i PostgreSQL o se tali "naive" i se faʻafitauli faʻapea, o se "atamai" ma sili ona faigata algorithm. "loop" i le SQL ma se tulaga e alu ese ai mai faʻamaumauga maua, lea e mafai ona aoga uma mo le atinaʻeina lautele ma mo le faʻaaogaina i isi tulaga tutusa.

Se'i o tatou su'e se seti fa'amaumauga mai mataupu muamua. Ina ia puipuia faʻamaumauga faʻaalia mai le "osooso" mai lea taimi i lea taimi pe a fetaui le faʻavasegaina o tau, fa'alautele le fa'asino mataupu e ala i le fa'aopoopoina o se ki autu. I le taimi lava e tasi, o le a vave ona tuʻuina atu ia te ia le tulaga ese ma faʻamautinoa mai ia i matou o le faʻasologa o le faasologa e le faʻamaonia:

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

E pei ona faalogoina, ua faapea ona tusia

Muamua, sei o tatou tusia le ata sili ona faigofie o le talosaga, pasi ID o le au fai pese fa'asologa e fai ma parata'i totonu:

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;

[vaai i le explain.tensor.ru]

Fai sina faanoanoa - na o le 20 faamaumauga na matou okaina, ae na toe faafoi mai e le Index Scan ia i matou 960 laina, lea sa tatau foi ona faavasega... Tatou taumafai e faitau itiiti.

unnest + ARRAY

O le iloiloga muamua o le a fesoasoani ia i tatou pe a tatou manaʻomia na'o le 20 fa'avasega faamaumauga, ona na ona faitau lea e le sili atu i le 20 fa'avasega i le fa'asologa tutusa mo ta'itasi ki. Lelei, faasino igoa talafeagai (owner_id, task_date, id) matou te maua.

Sei o tatou faʻaogaina le auala lava e tasi mo le suʻeina ma "faʻasalalau i koluma" fa'amaumauga laulau fa'atasi, e pei ona i ai mataupu mulimuli. E mafai foi ona tatou faʻaogaina le gaugau i totonu o se laina faʻaaoga le galuega 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; -- ... и тут - тоже

[vaai i le explain.tensor.ru]

Oi, ua sili atu ona lelei! 40% vave ma 4.5 taimi itiiti faʻamaumauga Sa tatau ona ou faitauina.

Fa'atinoina o fa'amaumauga o laulau e ala ile CTESei ou tosina atu lou mafaufau i le mea moni e faapea i nisi tulaga O se taumafaiga e galue vave ma fanua o se faamaumauga pe a uma ona suʻeina i se subquery, e aunoa ma le "afi" i se CTE, e mafai ona taʻitaʻia ai. "faateleina" InitPlan fa'atusatusa i le numera o nei lava fanua:

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

O le faʻamaumauga lava lea e tasi na "suʻeina" 4 taimi ... Seʻia oʻo i le PostgreSQL 11, o lenei amio e tupu i taimi uma, ma o le fofo o le "afi" i totonu o se CTE, o se tapulaʻa atoatoa mo le optimizer i nei lomiga.

Recursive accumulator

I le lomiga muamua, i le aofaʻi matou faitau 200 laina mo le lelei o le manaʻomia 20. E le o le 960, ae e itiiti ifo - e mafai?

Sei o tatou taumafai e faaaoga le poto tatou te manaomia aofaiga xnumx faamaumauga. O lona uiga, o le a matou toe faʻaogaina faʻamatalaga faitau seʻia oʻo i le aofaʻi matou te manaʻomia.

Laasaga 1: Lisi Amata

E manino lava, o la matou lisi "taulai" o faamaumauga e 20 e tatau ona amata i faamaumauga "muamua" mo se tasi o tatou owner_id ki. O le mea lea, muamua o le a tatou maua ai “muamua lava” mo ki taitasi ma faʻaopopo i le lisi, faʻavasega i le faasologa matou te mananaʻo ai - (task_date, id).

Laasaga 2: Su'e fa'amatalaga "sosoo".

Ia pe a tatou ave le faʻamatalaga muamua mai la tatou lisi ma amata “laa” i luma i le faasino igoa fa'asaoina le owner_id ki, ona maua uma lea o fa'amaumauga e soso'o ai i le filifiliga e maua. Ioe, na'o seia oo ina tatou laasia le ki o fa'ailoga lona lua i le lisi.

Afai e foliga mai ua tatou "laasia" le faamaumauga lona lua, ona o le tusi faitau mulimuli e tatau ona faaopoopo i le lisi nai lo le mea muamua (faʻatasi ai ma le owner_id), a maeʻa ona matou toe faʻavasega le lisi.

O lona uiga, matou te maua i taimi uma o le lisi e le sili atu ma le tasi le faʻamatalaga mo ki taʻitasi (afai e leai ni faʻamaumauga ma matou le "kolosi", o le mea muamua mai le lisi o le a mou atu ma leai se mea e faʻaopoopoina. ), ma latou fa'avasega i taimi uma i le fa'asologa alu a'e o le ki talosaga (task_date, id).

Laasaga 3: faamama ma "faalautele" faamaumauga

I nisi o laina o la matou filifiliga recursive, o nisi faamaumauga rv o lo'o fa'aluaina - muamua tatou te maua e pei o le "kolosiina o le tuaoi o le 2nd ulufale mai o le lisi", ona sui lea o le 1st mai le lisi. O lea la, o le mea muamua e tatau ona faʻamamaina.

Le fesili mulimuli mata'utia

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

[vaai i le explain.tensor.ru]

O lea, matou fefa'ataua'i 50% o fa'amaumauga faitau mo le 20% o le taimi fa'atino. O lona uiga, afai e iai sau mafuaaga e te talitonu ai o le faitau e ono umi se taimi (mo se faʻataʻitaʻiga, o faʻamaumauga e masani ona le o iai i totonu o le cache, ma e tatau ona e alu i le tisiki mo ia), o le auala lea e mafai ai ona e faʻalagolago i le faitau. .

I soo se tulaga, o le taimi o le faʻatinoga na sili atu ona lelei nai lo le "naive" muamua filifiliga. Ae o fea o nei 3 filifiliga e faʻaaoga e pule lava oe.

puna: www.habr.com