Van tyd tot tyd ontstaan die taak om na verwante data te soek deur 'n stel sleutels te gebruik. totdat ons die vereiste totale aantal rekords kry.
Die mees "regte lewe" voorbeeld is om te vertoon 20 oudste probleme, gelys op die lys van werknemers (byvoorbeeld binne een afdeling). Vir verskeie bestuurs-“dashboards” met kort opsommings van werkareas, word 'n soortgelyke onderwerp gereeld vereis.
In hierdie artikel gaan ons kyk na die implementering in PostgreSQL van 'n "naïewe" oplossing vir so 'n probleem, 'n "slimmer" en baie komplekse algoritme "lus" in SQL met 'n uitgang voorwaarde van die gevind data, wat nuttig kan wees vir beide algemene ontwikkeling en vir gebruik in ander soortgelyke gevalle.
Kom ons neem 'n toetsdatastel uit . Om te verhoed dat die vertoonde rekords van tyd tot tyd "spring" wanneer die gesorteerde waardes saamval, brei die onderwerpindeks uit deur 'n primêre sleutel by te voeg. Terselfdertyd sal dit dadelik uniekheid gee en ons waarborg dat die sorteervolgorde ondubbelsinnig is:
CREATE INDEX ON task(owner_id, task_date, id);
-- а старый - удалим
DROP INDEX task_owner_id_task_date_idx;Soos dit gehoor word, so is dit geskrywe
Kom ons skets eers die eenvoudigste weergawe van die versoek deur die ID's van die kunstenaars deur te gee :
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;
Bietjie hartseer - ons het net 20 rekords bestel, maar Index Scan het dit aan ons terugbesorg 960 reëls, wat dan ook gesorteer moes word... Kom ons probeer minder lees.
onrus + ARRAY
Die eerste oorweging wat ons sal help, is of ons nodig het slegs 20 gesorteer rekords, lees dan net nie meer as 20 gesorteer in dieselfde volgorde vir elk nie sleutel. Goed, geskikte indeks (eienaar_id, taakdatum, id) het ons.
Kom ons gebruik dieselfde meganisme om te onttrek en "in kolomme te versprei" integrale tabelrekord, soos in . Ons kan ook vou in 'n skikking toepas deur die funksie te gebruik 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; -- ... и тут - тоже
Ag, al baie beter! 40% vinniger en 4.5 keer minder data Ek moes dit lees.
Materialisering van tabelrekords via CTELaat ek u aandag daarop vestig dat in sommige gevalle 'n Poging om onmiddellik met die velde van 'n rekord te werk nadat u dit in 'n subnavraag gesoek het, sonder om dit in 'n CTE te "verpak", kan lei tot "vermenigvuldig" InitPlan eweredig aan die aantal van hierdie selfde velde:
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
Dieselfde rekord is 4 keer "opgesoek"... Tot en met PostgreSQL 11 kom hierdie gedrag gereeld voor, en die oplossing is om dit in 'n CTE te "verpak", wat 'n absolute limiet is vir die optimeerder in hierdie weergawes.
Rekursiewe akkumulator
In die vorige weergawe het ons in totaal gelees 200 reëls ter wille van die vereiste 20. Nie 960 nie, maar nog minder - is dit moontlik?
Kom ons probeer om die kennis wat ons nodig het te gebruik totaal xnumx rekords. Dit wil sê, ons sal slegs data lees totdat ons die hoeveelheid bereik wat ons benodig.
Stap 1: Beginlys
Dit is duidelik dat ons "teiken"-lys van 20 rekords moet begin met die "eerste" rekords vir een van ons eienaar_id-sleutels. Daarom sal ons eers sulke vind "heel eerste" vir elk van die sleutels en voeg dit by die lys, sorteer dit in die volgorde wat ons wil hê - (taak_datum, id).
Stap 2: Soek die "volgende" inskrywings
Nou as ons die eerste inskrywing van ons lys neem en begin “stap” verder langs die indeks deur die eienaar_id-sleutel te bewaar, dan is al die gevind rekords presies die volgende in die gevolglike keuse. Natuurlik net totdat ons die kolfsleutel oorsteek tweede inskrywing in die lys.
As dit blyk dat ons die tweede rekord “gekruis” het, dan die laaste inskrywing wat gelees is, moet by die lys gevoeg word in plaas van die eerste een (met dieselfde eienaar_id), waarna ons die lys weer sorteer.
Dit wil sê, ons kry altyd dat die lys nie meer as een inskrywing vir elk van die sleutels het nie (as die inskrywings opraak en ons nie “kruis nie”, dan sal die eerste inskrywing van die lys eenvoudig verdwyn en niks sal bygevoeg word nie ), en hulle altyd gesorteer in stygende volgorde van die toepassingsleutel (taak_datum, id).
Stap 3: filter en "brei" rekords uit
In sommige van die rye van ons rekursiewe seleksie, sommige rekords rv word gedupliseer - eers vind ons soos "oorsteek die grens van die 2de inskrywing van die lys", en vervang dit dan as die 1ste van die lys. Die eerste voorkoms moet dus gefiltreer word.
Die gevreesde finale navraag
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; -- берем только "непересекающие" записи
Dus, ons verhandel 50% van data lees vir 20% van uitvoering tyd. Dit wil sê, as jy redes het om te glo dat lees 'n lang tyd kan neem (byvoorbeeld, die data is dikwels nie in die kas nie, en jy moet skyf daarvoor gaan), dan kan jy op hierdie manier minder staatmaak op lees .
In elk geval het die uitvoeringstyd beter geblyk te wees as in die "naïewe" eerste opsie. Maar watter van hierdie 3 opsies om te gebruik, is aan jou.
Bron: will.com