SQL HowTo: убакыт циклин түздөн-түз суроого жазуу же "Элементардык үч кадам"

Мезгил-мезгили менен, баскычтардын топтомун колдонуу менен байланышкан маалыматтарды издөө милдети пайда болот. биз жазуулардын зарыл болгон жалпы санын алганга чейин.

Эң "чыныгы жашоо" мисалы - көрсөтүү 20 эң эски көйгөйлөр, тизмеленген кызматкерлердин тизмесинде (мисалы, бир бөлүмдүн ичинде). Иш чөйрөлөрүнүн кыскача корутундулары менен ар кандай башкаруу "башкаруу такталары" үчүн окшош тема көп талап кылынат.

Бул макалада биз PostgreSQLде мындай көйгөйдү чечүүнүн "акылдуу" жана өтө татаал алгоритмин ишке ашырууну карайбыз. Табылган маалыматтардан чыгуу шарты менен SQLде "цикл", бул жалпы иштеп чыгуу үчүн жана башка ушул сыяктуу учурларда колдонуу үчүн да пайдалуу болушу мүмкүн.

Келгиле, тесттик маалыматтар топтомун алалы мурунку макала. Көрсөтүлгөн жазуулардын мезгил-мезгили менен сорттолгон маанилер дал келгенде "секирүүсүн" алдын алуу үчүн, негизги ачкычты кошуу менен предметтин индексин кеңейтүү. Ошол эле учурда, бул дароо ага уникалдуулукту берет жана сорттоо тартиби бир түшүнүктүү экенине кепилдик берет:

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

Кандай угулса, ошондой жазылат

Биринчиден, аткаруучулардын идентификаторлорун өткөрүп, суроо-талаптын эң жөнөкөй версиясын чийип көрөлү киргизүү параметри катары массив:

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;

[express.tensor.ru сайтынан көрүү]

Бир аз өкүнүчтүү - биз болгону 20 жазууга буйрук бердик, бирок Index Scan аны бизге кайтарып берди 960 мөөнөт, аны да иргеш керек болчу... Азыраак окуганга аракет кылалы.

unnest + ARRAY

Бизге жардам бере турган биринчи нерсе, эгер керек болсо 20 гана сорттолгон жазуулар, анан жөн гана оку ар бири үчүн бирдей тартипте сорттолгон 20дан ашык эмес ачкыч. Жакшы, ылайыктуу индекс (ээсинин_идентификатору, тапшырма_даты, id) бизде бар.

Ошол эле механизмди казып алуу жана "мамычаларга жайылтуу" үчүн колдонолу интегралдык таблица жазуусу, сыяктуу акыркы макала. Функцияны колдонуп, массивге бүктөөнү да колдоно алабыз 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; -- ... и тут - тоже

[express.tensor.ru сайтынан көрүү]

О, ансыз деле жакшыраак! 40% тезирээк жана 4.5 эсе аз маалымат Мен аны окууга туура келди.

CTE аркылуу таблица жазууларын материалдаштырууУшуга көңүлүңүздү бурайын кээ бир учурларда Жазуунун талаалары менен дароо иштөө аракети, аны подсуруда издегенден кийин, аны CTEге "ороосуз" алып келиши мүмкүн. InitPlan "көбөйт" ушул эле талаалардын санына пропорционалдуу:

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

Ошол эле жазуу 4 жолу "каралды"... PostgreSQL 11ге чейин бул жүрүм-турум үзгүлтүксүз болуп турат жана чечим аны CTEге "ороо" болуп саналат, бул бул версиялардагы оптимизатор үчүн абсолюттук чек.

Рекурсивдүү аккумулятор

Мурунку версияда жалпысынан окудук 200 мөөнөт үчүн зарыл болгон 20. 960 эмес, андан да аз - бул мүмкүнбү?

Бизге керектүү билимди колдонууга аракет кылалы бардыгы 20 жазуулар. Башкача айтканда, биз керектүү суммага жеткенге чейин гана маалыматтарды окууну кайталайбыз.

1-кадам: Башталгыч тизме

Албетте, биздин 20 жазуудан турган "максаттуу" тизмебиз ээси_id ачкычтарыбыздын бири үчүн "биринчи" жазуулардан башталышы керек. Ошондуктан, биринчи биз ушундай табабыз баскычтардын ар бири үчүн "абдан биринчи" жана аны биз каалаган тартипте сорттоо менен тизмеге кошуңуз - (task_date, id).

2-кадам: "Кийинки" жазууларды табыңыз

Эми биздин тизмеден биринчи жазууну алып, баштай турган болсок индекс боюнча "кадам" owner_id ачкычын сактап, анда табылган бардык жазуулар натыйжада тандоонун кийинкилери болуп саналат. Албетте, бир гана биз жамбаш ачкычын кесип чейин тизмедеги экинчи жазуу.

Эгер биз экинчи рекордду «кетип» чыксак, анда биринчи окугандын ордуна тизмеге акыркы жазуу кошулушу керек (ошол эле owner_id менен), андан кийин биз тизмени кайра иреттейбиз.

Башкача айтканда, биз ар дайым тизмеде ар бир баскыч үчүн бирден ашык жазуу жок экенин көрөбүз (эгерде жазуулар түгөнүп, биз “кайчылаш” болбосок, анда тизмедеги биринчи жазуу жөн эле жок болуп кетет жана эч нерсе кошулбайт. ), жана алар ар дайым иреттелген колдонмо ачкычынын өсүү тартибинде (task_date, id).

3-кадам: чыпкалоо жана жазууларды "кеңейтүү"

Биздин рекурсивдүү тандообуздун кээ бир катарларында кээ бир жазуулар rv кайталанат - адегенде биз "тизменин 2-жазуунун чек арасын кесип өтүү" сыяктууларды табабыз, андан кийин аны тизмеден 1-чи катары алмаштырабыз. Ошентип, биринчи көрүнүш чыпкалоо керек.

Коркунучтуу акыркы суроо

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

[express.tensor.ru сайтынан көрүү]

Ошентип, биз аткарылган убакыттын 50% үчүн маалыматтардын 20% окуду. Башкача айтканда, эгер сизде окуу көп убакыт талап кылынышы мүмкүн деп ишенүүгө негиз бар болсо (мисалы, маалыматтар көбүнчө кэште болбойт жана ал үчүн дискке өтүшүңүз керек), анда бул жол менен сиз окууга азыраак көз каранды болосуз. .

Кандай болгон күндө да, аткаруу убактысы "наивдуу" биринчи вариантка караганда жакшыраак болуп чыкты. Бирок бул 3 варианттын кайсынысын колдонуу сизге байланыштуу.

Source: www.habr.com