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-дан аспайды кілт. Жақсы, қолайлы көрсеткіш (иенің_идентификаторы, тапсырма_күні, идентификаторы) бізде бар.

Шығару және «бағандарға тарату» үшін бірдей механизмді қолданайық интегралдық кесте жазбасы, сияқты соңғы мақала. Функцияны пайдаланып массивке бүктеуді де қолдана аламыз 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 емес, одан да аз - мүмкін бе?

Өзімізге қажетті білімді қолдануға тырысайық жалпы xnumx жазбалар. Яғни, біз қажетті мөлшерге жеткенше ғана деректерді оқуды қайталаймыз.

1-қадам: Бастау тізімі

Әлбетте, біздің 20 жазбадан тұратын «мақсатты» тізім иеленуші идентификаторының бірінің «бірінші» жазбаларынан басталуы керек. Сондықтан, алдымен біз мұндайды табамыз Әрбір кілт үшін «өте бірінші». және оны біз қалаған ретпен сұрыптап, тізімге қосыңыз - (тапсырма_күні, идентификатор).

2-қадам: «Келесі» жазбаларды табыңыз

Енді біз тізімнен бірінші жазбаны алып, бастасақ индекс бойымен «қадам». owner_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 нұсқаның қайсысын пайдалану сізге байланысты.

Ақпарат көзі: www.habr.com