SQL HowTo: sorğuda bir while döngüsü yazmaq, ya da "Əsas Üçlük"

Bəzən açar sözlər dəstinə uyğun olaraq əlaqəli məlumatların axtarılması tələbi ortaya çıxır təqvimimizdə lazım olan ümumi yazı sayına çatana qədər.

Ən 'real' nümunə — çıxarmaqdır 20 ən köhnə vəzifələr, siyahıya alınan işçilər arasında (məsələn, eyni şöbə daxilində). Müxtəlif idarəetmə 'qrafikləri' üçün iş sahələrinin qısa xülasələri üzrə oxşar tələbat həmişə olur.

Məqalədə PostgreSQL-də bu cür məsələni həll etməyin 'sadə' versiyası, 'daha ağıllı' və tamamilə çətin bir alqoritmi mərhələli 'döngü' SQL-də tapılan məlumatdan çıxış şərtiilə, həm ümumi bilik üçün, həm də bənzər hallarda istifadəsi yolları üçün faydalı ola bilər.

Test məlumat dəstini keçən məqalədəngötürək. Çıxarılan yazıların hər dəfə uyğun sıralanmış dəyərlərə uyğun olaraq 'tullanmadığı' üçün mövzunun indeksini ilkin açar əlavə etməklə genişləndirəcəyik. Bu, ona dərhal unikallıq da verəcək və sıralamanın ardıcıllığını dəqiqləşdirəcək:

CREATE INDEX ON task(owner_id, task_date, id);
-- köhnəni siləcəyik
DROP INDEX task_owner_id_task_date_idx;

Eşidilən kimi yazılır

İlk növbədə, icraçıların ID-lərini qəbul edən ən sadə sorğu variantını yazacağıq massiv olaraq giriş parametrimiz kimi:

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;

[explain.tensor.ru-dan baxın]

Bir az kədərli — biz yalnız 20 yazı sifariş etmişdik, lakin Index Scan bizə 960 sətir, bunları daha sonra sıralamağa məcbur olduq... Gəlin, az oxumağa çalışaq.

unnest + ARRAY

İlk düşüncə, bizə kömək edəcək — əgər bizə yalnız 20 sıralanmış yazı lazımdırsa, o zaman hər bir açar üçün yalnız 20 sıralanmış oxumaq kifayətdir. Xoşbəxtlikdən, uyğun indeks (owner_id, task_date, id) bizdə var. Eyni şəkildə, 'sıralanmış cədvəl' çıxarılması və 'sütunlara döndərilməsi' mexanizmindən istifadə edəcəyik

cədvəl yazısının bütövlüklə keçmiş məqalədə. Həmçinin, 'ARRAY()' funksiyası ilə massivi sıxışdıracağıq .WITH T AS ( SELECT unnest(ARRAY( SELECT t FROM task t WHERE owner_id = unnest ORDER BY task_date, id LIMIT 20 -- burada məhdudlaşdırırıq... )) 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; -- ... və burada da - eyni Oh, artıq daha yaxşıdır!:

40% daha sürətlidir və 4.5 dəfə az məlumat

[explain.tensor.ru-dan baxın]

oxumağa məcbur olduq. CTE vasitəsi ilə cədvəl yazılarını materializasiya etmək Qeyd etmək istərdim ki,

bəzənyarıdan xülasəsini birbaşa tapıldıqdan sonra işləmlərilə 'göndərilməsi' cəhdlərinin buna səbəb ola bilər 'InitPlan'ın sayının 'çoxaldılması': SELECT (( SELECT t FROM task t WHERE owner_id = 1 ORDER BY task_date, id LIMIT 1 ).*);

SELECT
  ((
    SELECT
      t
    FROM
      task t
    WHERE
      owner_id = 1
    ORDER BY
      task_date, id
    LIMIT 1
  ).*);

Nəticə  (xərcləri=4.77..4.78, satırlar=1, genişlik=16) (real vaxt=0.063..0.063, satırlar=1, dövrlər=1)
  Yaddaş: bölüşdürülmüş hit=16
  InitPlan 1 (geri qaytarır $0)
    ->  Limit  (xərcləri=0.42..1.19, satırlar=1, genişlik=48) (real vaxt=0.031..0.032, satırlar=1, dövrlər=1)
          Yaddaş: bölüşdürülmüş hit=4
          ->  İndeks Tarama, task_owner_id_task_date_id_idx istifadə edərək tapşırıq t  (xərcləri=0.42..387.57, satırlar=500, genişlik=48) (real vaxt=0.030..0.030, satırlar=1, dövrlər=1)
                İndeks Şərti: (owner_id = 1)
                Yaddaş: bölüşdürülmüş hit=4
  InitPlan 2 (geri qaytarır $1)
    ->  Limit  (xərcləri=0.42..1.19, satırlar=1, genişlik=48) (real vaxt=0.008..0.009, satırlar=1, dövrlər=1)
          Yaddaş: bölüşdürülmüş hit=4
          ->  İndeks Tarama, task_owner_id_task_date_id_idx istifadə edərək tapşırıq t_1  (xərcləri=0.42..387.57, satırlar=500, genişlik=48) (real vaxt=0.008..0.008, satırlar=1, dövrlər=1)
                İndeks Şərti: (owner_id = 1)
                Yaddaş: bölüşdürülmüş hit=4
  InitPlan 3 (geri qaytarır $2)
    ->  Limit  (xərcləri=0.42..1.19, satırlar=1, genişlik=48) (real vaxt=0.008..0.008, satırlar=1, dövrlər=1)
          Yaddaş: bölüşdürülmüş hit=4
          ->  İndeks Tarama, task_owner_id_task_date_id_idx istifadə edərək tapşırıq t_2  (xərcləri=0.42..387.57, satırlar=500, genişlik=48) (real vaxt=0.008..0.008, satırlar=1, dövrlər=1)
                İndeks Şərti: (owner_id = 1)
                Yaddaş: bölüşdürülmüş hit=4"
  InitPlan 4 (geri qaytarır $3)
    ->  Limit  (xərcləri=0.42..1.19, satırlar=1, genişlik=48) (real vaxt=0.009..0.009, satırlar=1, dövrlər=1)
          Yaddaş: bölüşdürülmüş hit=4
          ->  İndeks Tarama, task_owner_id_task_date_id_idx istifadə edərək tapşırıq t_3  (xərcləri=0.42..387.57, satırlar=500, genişlik=48) (real vaxt=0.009..0.009, satırlar=1, dövrlər=1)
                İndeks Şərti: (owner_id = 1)
                Yaddaş: bölüşdürülmüş hit=4

Eyni qeydin «axtarıldığı» 4 dəfə… PostgreSQL 11-ə qədər bu cür davranış müntəzəm baş verir və həll yolu CTE-yə «sarımaqdır», bu da bu versiyalarda optimizator üçün şübhəsiz sərhəddir.

Təkrarlanan akkumulyator

Öncəki variantda ümumilikdə oxuduq 200 sətir lazım olan 20-nin müqabilində. Artıq 960 deyil, amma daha az — mümkündürmü?

Gəlin bilikdən istifadə etməyə çalışaq ki, bizə lazım olan cəmi 20 qeyddir. Yəni biz məlumatların çıxarılmasını yalnız lazım olan saya çatana qədər iterasiya edəcəyik.

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

Aydındır ki, bizim «hədəf» siyahımız 20 qeyddən ibarət olmalıdır və «ilk» qeydlərdən biri ilə başlayır, buna görə də əvvəlcə belə qeydləri tapacağıq. Hər bir açar üçün «ən birinci» olanları tapıb, istədiyimiz sırada (task_date, id) üzrə sıralayacağıq.

Addım 2: «növbəti» qeydləri tapmaq

İndi əgər siyahımızın ilk qeydi götürsək və indeksdə daha irəliləsək, saxlayaraq owner_id açarını, tapılan bütün qeydlər tam olaraq nəticə seçiminin növbəti olanlarıdır. Əlbəttə ki, yalnız biz siyahıdakı ikinci qeydin tətbiq açarını keçənə qədər. Əgər iştirak etmişiksə ki, ikinci qeydi «keçmişik», onda sonuncu oxunan qeydi birinci ilə əvəz edərək siyahıya əlavə etmək lazımdır

(eyni owner_id ilə), daha sonra siyahını yenidən sıralayırıq. Yəni bizdə hər zaman elə olur ki, siyahıda hər bir açar üzrə bir qeyddən artıq yoxdur (əgər qeydlər bitmişsə və biz «keçməsək», o zaman siyahının ilk qeydi sadəcə yox olacaq və heç nə əlavə olunmayacaq); həmçinin onlar həmişə tətbiq açarının artan sırada (task_date, id) üzrə sıralanır.

Addım 3: qeydləri filtreləyib «açmaq» Bəzi sətirlərimizin rekursiv seçməsində bəzi qeydlər rv

Шаг 3: фильтруем и «разворачиваем» записи

В части строк нашей рекурсивной выборки некоторые записи rv Dublika var — əvvəlcə "siyahının 2-ci qeydinin sərhədini keçirənlər" tapılır, sonra isə ilk olaraq siyahıya daxil edilir. Beləliklə, ilk ortaya çıxanı süzgəcdən keçirmək lazımdır.

Dəhşətli sonuncu sorğu

WITH RECURSIVE T AS (
  -- #1: hər bir açar dəstinə uyğun "ilk" qeydləri siyahıya daxil edirik
  WITH wrap AS ( -- qeydləri "matериалizasiya edirik", beləliklə, sahələrə müraciət edilməsi InitPlan/SubPlan'ı artırmır
    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 -- siyahını lazım olan qaydada sıralayırıq
    FROM
      T
  )
  SELECT
    list
  , list[1] rv
  , FALSE not_cross
  , 0 size
  FROM
    wrap
UNION ALL
  -- #2: 1-ci açar üzrə qeydləri oxuyuruq, 2-ciyə keçməyincəyə qədər
  SELECT
    CASE
      -- əgər 1-ci qeyd üçün heç bir şey tapılmazsa
      WHEN X._r IS NOT DISTINCT FROM NULL THEN
        T.list[2:] -- onu siyahıdan çıxarırıq
      -- əgər 2-ci qeyd üzrə tətbiq açarını KEÇMƏYİB
      WHEN X.not_cross THEN
        T.list -- sadəcə, eyni siyahını modifications etmədən uzadırıq
      -- əgər siyahıda artıq 2-ci qeyd yoxdursa
      WHEN T.list[2] IS NULL THEN
        -- sadəcə boş siyahı qaytarırıq
        '{}'
      -- lüğəti yenidən sıralayırıq, 1-ci qeydi çıxararaq, tapılan sonuncunu əlavə edirik
      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 ( -- qeydi "matериалizasiya edirik"
        SELECT
          CASE
            -- əgər hər halda "2-ci dəf KEÇMƏDİK"
            WHEN NOT T.not_cross
              -- o vaxt lazım olan qeyd - siyahının ilkidir
              THEN T.list[1]
            ELSE ( -- əgər keçmədik, açar 1-ci qeyd olduğu kimi qaldı - ona istinad edirik
              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
          -- əgər 2-ci qeyd artıq siyahıda yoxdursa, amma biz bir şey tapmışıqsa
          WHEN list[2] IS NULL AND _r IS DISTINCT FROM NULL THEN
            TRUE
          ELSE -- heç nə tapmadıq ya da "KEÇDİK"
            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 -- burda sayı məhdudlaşdırırıq
    T.list IS DISTINCT FROM '{}' -- ya da siyahı bitənədək
)
-- #3: qeydləri "qaldırırıq" - sıra tikilişə görə təsdiqlənib
SELECT
  (rv).* 
FROM
  T
WHERE
  not_cross; -- yalnız "keçməyən" qeydləri alırıq

[explain.tensor.ru-dan baxın]

Beləliklə, biz 50% məlumat oxumasını 20% icra müddəti ilə dəyişdik. Yəni əgər sizdə oxumanın uzun olacağına dair səbəblər varsa (məsələn, məlumatlar çox vaxt keşdə deyil, diskə müraciət etməlisinizsə), bu şəkildə oxumağa daha az bağlı olmanız mümkündür.

Hər haldə, icra müddəti "naiv" ilk variantdan daha yaxşı oldu. Ancaq bu 3 variantdan hansını istifadə edəcəyinizi siz seçirsiniz.

Mənbə: habr.com