SQL HowTo: birbaşa sorğuda bir müddət dövrə yazmaq və ya "Elementar üç addım"

Dövri olaraq, bir sıra açarlardan istifadə edərək əlaqəli məlumatların axtarışı vəzifəsi yaranır. tələb olunan ümumi qeydlərin sayını əldə edənə qədər.

Ən “real həyat” nümunəsi göstərməkdir 20 ən qədim problem, siyahıya alınmışdır işçilərin siyahısında (məsələn, bir bölmə daxilində). İş sahələrinin qısa xülasəsi olan müxtəlif idarəetmə "tabelləri" üçün oxşar mövzu olduqca tez-tez tələb olunur.

Bu yazıda PostgreSQL-də belə bir problemin “sadəlövh” həllinin, “daha ​​ağıllı” və çox mürəkkəb alqoritmin tətbiqinə baxacağıq. Tapılan məlumatlardan çıxış şərti ilə SQL-də “döngü”, bu həm ümumi inkişaf üçün, həm də digər oxşar hallarda istifadə üçün faydalı ola bilər.

-dən bir test məlumat dəstini götürək əvvəlki məqalə. Göstərilən qeydlərin vaxtaşırı çeşidlənmiş dəyərlər üst-üstə düşdüyü zaman "atlamasının" qarşısını almaq üçün, əsas açar əlavə edərək mövzu indeksini genişləndirin. Eyni zamanda, bu, dərhal unikallıq verəcək və çeşidləmə qaydasının birmənalı olmasına zəmanət verəcəkdir:

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

Necə eşidilirsə, elə yazılır

Əvvəlcə ifaçıların şəxsiyyət vəsiqələrini keçərək sorğunun ən sadə versiyasını eskiz edək giriş parametri kimi massiv:

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 saytına baxın]

Bir az kədərli - biz cəmi 20 qeyd sifariş etdik, lakin Index Scan onu bizə qaytardı 960 sətir, o da sıralanmalı idi... Gəlin daha az oxumağa çalışaq.

unnest + ARRAY

Bizə kömək edəcək ilk şey ehtiyacımız olub-olmamasıdır yalnız 20 sıralanır qeydlər, sonra sadəcə oxuyun hər biri üçün eyni ardıcıllıqla çeşidlənmiş 20-dən çox olmamalıdır açar. Yaxşı, uyğun indeks (sahibi_id, tapşırıq_tarixi, id) bizdə var.

Gəlin çıxarmaq və "sütunlara yaymaq" üçün eyni mexanizmdən istifadə edək inteqral cədvəl qeydikimi Son məqalə. Funksiyadan istifadə edərək massivdə qatlama tətbiq edə bilərik 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 saytına baxın]

Oh, artıq daha yaxşı! 40% daha sürətli və 4.5 dəfə az məlumat oxumalı idim.

CTE vasitəsilə cədvəl qeydlərinin materiallaşdırılmasıİcazə verin, diqqətinizi buna cəlb edim bəzi hallarda Alt sorğuda onu axtardıqdan sonra onu CTE-yə “sarmadan” dərhal onun sahələri ilə işləmək cəhdi InitPlan-ı "çoxalt" eyni sahələrin sayına mütənasibdir:

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

Eyni qeyd 4 dəfə “baxıldı”... PostgreSQL 11-ə qədər bu davranış müntəzəm olaraq baş verir və həll yolu onu CTE-yə “sarmaqdır” ki, bu da bu versiyalarda optimallaşdırıcı üçün mütləq hədddir.

Rekursiv akkumulyator

Əvvəlki versiyada, ümumilikdə oxuduq 200 sətir tələb olunan 20 xatirinə. 960 yox, hətta daha az - mümkündürmü?

Bizə lazım olan biliklərdən istifadə etməyə çalışaq cəmi 20 qeydlər. Yəni, biz yalnız lazım olan məbləğə çatana qədər məlumatların oxunmasını təkrarlayacağıq.

Addım 1: Başlanğıc siyahısı

Aydındır ki, 20 qeyddən ibarət “hədəf” siyahımız sahiblik id açarlarımızdan biri üçün “ilk” qeydlərlə başlamalıdır. Buna görə də əvvəlcə belə tapacağıq düymələrin hər biri üçün "çox birinci" və istədiyimiz ardıcıllıqla çeşidləyərək siyahıya əlavə edin - (task_date, id).

Addım 2: "Növbəti" girişləri tapın

İndi siyahımızdan ilk girişi götürsək və başlasaq indeks boyunca daha da "addım" owner_id açarını qorumaqla, bütün tapılan qeydlər nəticədə gələn seçimdə tam olaraq növbəti olanlardır. Əlbəttə, yalnız biz butt açarı keçənə qədər siyahıda ikinci giriş.

İkinci rekordu “keçdiyimiz” ortaya çıxarsa, deməli Siyahıya birincinin əvəzinə oxunan sonuncu giriş əlavə edilməlidir (eyni sahibi_id ilə), bundan sonra siyahını yenidən çeşidləyirik.

Yəni, biz həmişə əldə edirik ki, siyahıda açarların hər biri üçün birdən çox giriş yoxdur (əgər girişlər bitsə və biz “keçməsək”, siyahıdan ilk giriş sadəcə yox olacaq və heç nə əlavə edilməyəcək. ), və onlar həmişə sıralanır proqram açarının artan sırası ilə (task_date, id).

Addım 3: qeydləri süzün və "genişləndirin"

Rekursiv seçimimizin bəzi sətirlərində bəzi qeydlər var rv təkrarlanır - əvvəlcə "siyahının 2-ci girişinin sərhədini keçmək" kimi tapırıq və sonra onu siyahıdan 1-ci kimi əvəz edirik. Beləliklə, ilk hadisə süzülməlidir.

Qorxunc son sorğu

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 saytına baxın]

Beləliklə, biz icra müddətinin 50%-i üçün oxunan məlumatların 20%-ni ticarət etdi. Yəni, oxumağın uzun müddət çəkə biləcəyinə inanmaq üçün səbəbləriniz varsa (məsələn, məlumat tez-tez yaddaşda olmur və bunun üçün diskə getməlisiniz), bu şəkildə oxumaqdan daha az asılı ola bilərsiniz. .

Hər halda, icra müddəti "sadəlövh" birinci variantdan daha yaxşı oldu. Amma bu 3 variantdan hansını istifadə etmək sizə bağlıdır.

Mənbə: www.habr.com