Pravidelne sa objavuje úloha vyhľadávania súvisiacich údajov pomocou súboru kľúčov. kým nezískame požadovaný celkový počet záznamov.
Najviac „skutočným“ príkladom je zobrazenie 20 najstarších problémov, uvedené na zozname zamestnancov (napríklad v rámci jednej divízie). Pre rôzne manažérske „dashboardy“ so stručnými súhrnmi pracovných oblastí sa podobná téma vyžaduje pomerne často.
V tomto článku sa pozrieme na implementáciu „naivného“ riešenia takéhoto problému v PostgreSQL, „inteligentnejšieho“ a veľmi zložitého algoritmu “loop” v SQL s podmienkou ukončenia z nájdených údajov, čo môže byť užitočné ako pre všeobecný vývoj, tak aj pre použitie v iných podobných prípadoch.
Zoberme si testovací súbor údajov z
CREATE INDEX ON task(owner_id, task_date, id);
-- а старый - удалим
DROP INDEX task_owner_id_task_date_idx;
Ako sa počúva, tak sa aj píše
Najprv načrtnime najjednoduchšiu verziu žiadosti a odovzdajme ID účinkujúcich
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;
Trochu smutné - objednali sme len 20 platní, ale Index Scan nám to vrátil 960 riadkov, ktoré potom tiež bolo treba triediť... Skúsme menej čítať.
unnest + ARRAY
Prvá úvaha, ktorá nám pomôže, je, či potrebujeme len 20 triedených záznamy, potom už len čítať nie viac ako 20 zoradených v rovnakom poradí pre každého kľúč. dobre, vhodný index (owner_id, task_date, id) máme.
Použime rovnaký mechanizmus na extrakciu a „rozloženie do stĺpcov“ integrálny tabuľkový záznamako v 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, už oveľa lepšie! O 40 % rýchlejšie a 4.5-krát menej dát Musel som si to prečítať.
Materializácia tabuľkových záznamov cez CTEDovoľte mi upriamiť vašu pozornosť na skutočnosť v niektorých prípadoch Pokus o okamžitú prácu s poľami záznamu po jeho vyhľadaní v poddotaze bez jeho „zabalenia“ do CTE môže viesť k "znásobiť" InitPlan úmerné počtu týchto rovnakých polí:
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
Ten istý záznam bol „vyhľadaný“ 4-krát... Do PostgreSQL 11 sa toto správanie vyskytuje pravidelne a riešením je „zabaliť“ ho do CTE, čo je v týchto verziách absolútny limit pre optimalizátor.
Rekurzívny akumulátor
V predchádzajúcej verzii sme celkovo čítali 200 riadkov kvôli požadovaným 20. Nie 960, ale ešte menej - je to možné?
Skúsme využiť vedomosti, ktoré potrebujeme celkom 20 záznamy. To znamená, že budeme opakovať čítanie údajov len dovtedy, kým nedosiahneme množstvo, ktoré potrebujeme.
Krok 1: Štartovacia listina
Je zrejmé, že náš „cieľový“ zoznam 20 záznamov by mal začínať „prvými“ záznamami pre jeden z našich kľúčov owner_id. Preto najprv také nájdeme „úplne prvý“ pre každý z klávesov a pridajte ho do zoznamu a zoraďte ho v požadovanom poradí - (dátum_úlohy, id).
Krok 2: Nájdite „ďalšie“ položky
Teraz, ak vezmeme prvý záznam z nášho zoznamu a začneme „krok“ ďalej pozdĺž indexu so zachovaním kľúča owner_id, potom sú všetky nájdené záznamy presne tie ďalšie vo výslednom výbere. Samozrejme len kým neskrížime kľúč na zadok druhý záznam v zozname.
Ak sa ukáže, že sme „prekročili“ druhý rekord, tak posledný prečítaný záznam by sa mal pridať do zoznamu namiesto prvého (s rovnakým vlastníkom_id), potom zoznam znova zoradíme.
To znamená, že vždy dostaneme, že zoznam nemá viac ako jeden záznam pre každý z kľúčov (ak sa záznamy vyčerpajú a my „neskrížime“, tak prvý záznam zo zoznamu jednoducho zmizne a nič sa nepridá ), a oni vždy triedené vo vzostupnom poradí podľa aplikačného kľúča (dátum_úlohy, id).
Krok 3: filtrujte a „rozbaľte“ záznamy
V niektorých riadkoch nášho rekurzívneho výberu niektoré záznamy rv
sú duplicitné - najprv nájdeme napríklad „prekročenie hranice 2. záznamu v zozname“ a potom ho nahradíme ako 1. zo zoznamu. Prvý výskyt je teda potrebné filtrovať.
Obávaná záverečná otázka
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; -- берем только "непересекающие" записи
Teda my zobchodovaných 50 % prečítaní údajov počas 20 % času realizácie. To znamená, že ak máte dôvody domnievať sa, že čítanie môže trvať dlho (napríklad údaje často nie sú vo vyrovnávacej pamäti a musíte pre ne ísť na disk), môžete sa týmto spôsobom menej spoliehať na čítanie. .
V každom prípade sa čas vykonania ukázal byť lepší ako pri „naivnej“ prvej možnosti. Ale ktorú z týchto 3 možností využijete, je len na vás.
Zdroj: hab.com