Periodikisht, lind detyra e kërkimit të të dhënave të lidhura duke përdorur një grup çelësash. derisa të marrim numrin e përgjithshëm të kërkuar të regjistrimeve.
Shembulli më i "jetës reale" është për t'u shfaqur 20 problemet më të vjetra, të listuara në listën e punonjësve (për shembull, brenda një divizioni). Për "panel" të ndryshëm të menaxhimit me përmbledhje të shkurtra të fushave të punës, një temë e ngjashme kërkohet mjaft shpesh.
Në këtë artikull do të shikojmë zbatimin në PostgreSQL të një zgjidhjeje "naive" për një problem të tillë, një algoritëm "më të zgjuar" dhe shumë kompleks. "loop" në SQL me një kusht dalje nga të dhënat e gjetura, i cili mund të jetë i dobishëm si për zhvillimin e përgjithshëm ashtu edhe për përdorim në raste të tjera të ngjashme.
Le të marrim një grup të dhënash testimi nga . Për të parandaluar që të dhënat e shfaqura të "kërcejnë" herë pas here kur vlerat e renditura përkojnë, zgjeroni indeksin e lëndës duke shtuar një çelës primar. Në të njëjtën kohë, kjo do t'i japë menjëherë unike dhe do të na garantojë që rendi i renditjes është i paqartë:
CREATE INDEX ON task(owner_id, task_date, id);
-- а старый - удалим
DROP INDEX task_owner_id_task_date_idx;Siç dëgjohet, ashtu shkruhet
Së pari, le të skicojmë versionin më të thjeshtë të kërkesës, duke kaluar ID-të e interpretuesve :
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;
Pak e trishtuar - porositëm vetëm 20 regjistrime, por Index Scan na e ktheu atë 960 rreshta, e cila më pas gjithashtu duhej të renditej... Le të përpiqemi të lexojmë më pak.
çmontoj + ARRAY
Shqyrtimi i parë që do të na ndihmojë është nëse kemi nevojë vetëm 20 të renditura regjistroni, pastaj thjesht lexoni jo më shumë se 20 të renditura në të njëjtën mënyrë për secilin Celës. Mirë, indeks të përshtatshëm (id_owner, data_detyrë, id) kemi.
Le të përdorim të njëjtin mekanizëm për nxjerrjen dhe "përhapjen në kolona" rekord integral i tabelës, si në . Mund të aplikojmë gjithashtu palosjen në një grup duke përdorur funksionin 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; -- ... и тут - тоже
Oh, shumë më mirë tashmë! 40% më shpejt dhe 4.5 herë më pak të dhëna Më duhej ta lexoja.
Materializimi i të dhënave të tabelës nëpërmjet CTEMë lejoni të tërheq vëmendjen tuaj për faktin se në disa raste Një përpjekje për të punuar menjëherë me fushat e një rekordi pas kërkimit të tij në një nënpyetje, pa e "mbështjellë" atë në një CTE, mund të çojë në "shumëzuar" InitPlan proporcionale me numrin e këtyre fushave të njëjta:
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
I njëjti rekord u “kërkua” 4 herë... Deri në PostgreSQL 11, kjo sjellje ndodh rregullisht dhe zgjidhja është ta “mbështillni” me një CTE, që është një kufi absolut për optimizuesin në këto versione.
Akumulator rekurziv
Në versionin e mëparshëm, në total kemi lexuar 200 rreshta për hir të 20. Jo 960, por edhe më pak - a është e mundur?
Le të përpiqemi të përdorim njohuritë që na nevojiten gjithsej xnumx rekorde. Kjo do të thotë, ne do të përsërisim leximin e të dhënave vetëm derisa të arrijmë sasinë që na nevojitet.
Hapi 1: Lista e fillimit
Natyrisht, lista jonë e "objektivit" me 20 rekorde duhet të fillojë me regjistrimet "e para" për një nga çelësat tanë të identifikimit të pronarit. Prandaj, së pari do të gjejmë të tillë "Shumë i pari" për secilin nga çelësat dhe e shtojmë në listë, duke e renditur sipas radhës që duam - (data_detyrës, id).
Hapi 2: Gjeni hyrjet "tjetër".
Tani nëse marrim hyrjen e parë nga lista jonë dhe fillojmë "hap" më tej përgjatë indeksit duke ruajtur çelësin pronar_id, atëherë të gjitha rekordet e gjetura janë pikërisht ato të radhës në përzgjedhjen që rezulton. Sigurisht, vetëm derisa të kalojmë çelësin e prapanicës hyrja e dytë në listë.
Nëse rezulton se kemi "kapërcyer" rekordin e dytë, atëherë hyrja e fundit e lexuar duhet të shtohet në listë në vend të të parës (me të njëjtin pronar_id), pas së cilës e riorganizojmë listën përsëri.
Kjo do të thotë, ne marrim gjithmonë se lista nuk ka më shumë se një hyrje për secilin nga çelësat (nëse hyrjet mbarojnë dhe ne nuk "kapërcejmë", atëherë hyrja e parë nga lista thjesht do të zhduket dhe asgjë nuk do të shtohet ), dhe ata gjithmonë të renditura në rendin rritës të çelësit të aplikacionit (data_detyrës, id).
Hapi 3: filtroni dhe "zgjeroni" të dhënat
Në disa nga rreshtat e përzgjedhjes sonë rekursive, disa regjistrime rv janë të dyfishuara - fillimisht gjejmë të tilla si "kalimi i kufirit të hyrjes së dytë të listës", dhe më pas e zëvendësojmë atë si të 2-tin nga lista. Pra, dukuria e parë duhet të filtrohet.
Pyetja e frikshme përfundimtare
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; -- берем только "непересекающие" записи
Kështu, ne tregtuan 50% të leximeve të të dhënave për 20% të kohës së ekzekutimit. Kjo do të thotë, nëse keni arsye për të besuar se leximi mund të zgjasë shumë (për shembull, të dhënat shpesh nuk janë në cache, dhe ju duhet të shkoni në disk për të), atëherë në këtë mënyrë mund të vareni më pak nga leximi .
Në çdo rast, koha e ekzekutimit doli të ishte më e mirë se në opsionin e parë "naiv". Por cila nga këto 3 opsione për t'u përdorur varet nga ju.
Burimi: www.habr.com