Պարբերաբար առաջանում է ստեղների հավաքածուի միջոցով առնչվող տվյալների որոնման խնդիրը: մինչև մենք ստանանք անհրաժեշտ ընդհանուր թվով գրառումներ.
Առավել «իրական կյանքի» օրինակը ցուցադրելն է 20 ամենահին խնդիրները, նշված աշխատողների ցուցակում (օրինակ, մեկ բաժնի շրջանակներում): Տարբեր կառավարման «վահանակների» համար՝ աշխատանքի ոլորտների հակիրճ ամփոփագրերով, նմանատիպ թեմա բավական հաճախ է պահանջվում:
Այս հոդվածում մենք կանդրադառնանք PostgreSQL-ում նման խնդրի «միամիտ» լուծման՝ «ավելի խելացի» և շատ բարդ ալգորիթմի ներդրմանը։ «loop» SQL-ում գտնված տվյալների ելքի պայմանով, որը կարող է օգտակար լինել ինչպես ընդհանուր զարգացման, այնպես էլ նմանատիպ այլ դեպքերում օգտագործելու համար։
Եկեք վերցնենք թեստային տվյալների հավաքածու . Որպեսզի ցուցադրվող գրառումները ժամանակ առ ժամանակ «ցատկեն», երբ դասավորված արժեքները համընկնում են, ընդլայնել առարկայի ինդեքսը՝ ավելացնելով առաջնային բանալի. Միևնույն ժամանակ, դա անմիջապես կհաղորդի նրան եզակիություն և երաշխավորում է, որ տեսակավորման կարգը միանշանակ է.
CREATE INDEX ON task(owner_id, task_date, id);
-- а старый - удалим
DROP INDEX task_owner_id_task_date_idx;Ինչպես լսվում է, այնպես էլ գրված է
Նախ, եկեք ուրվագծենք հարցման ամենապարզ տարբերակը՝ փոխանցելով կատարողների ID-ները :
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;
Մի փոքր տխուր. մենք պատվիրեցինք ընդամենը 20 ձայնագրություն, և Index Scan-ը այն վերադարձրեց մեզ 960 տող, որը հետո նույնպես պետք էր տեսակավորել... Փորձենք ավելի քիչ կարդալ։
unnest + ARRAY
Առաջին նկատառումը, որը կօգնի մեզ, դա անհրաժեշտության դեպքում է միայն 20 տեսակավորված գրառումներ, ապա պարզապես կարդացեք ոչ ավելի, քան 20 տեսակավորված յուրաքանչյուրի համար նույն հերթականությամբ բանալի. Լավ, հարմար ցուցանիշ (owner_id, task_date, id) մենք ունենք:
Եկեք օգտագործենք նույն մեխանիզմը հանելու և «սյուների մեջ տարածելու» համար. ինտեգրալ աղյուսակի գրառում, ինչպես մեջ . Մենք կարող ենք նաև ծալել զանգվածի մեջ՝ օգտագործելով ֆունկցիան 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; -- ... и тут - тоже
Օ՜, արդեն շատ ավելի լավ: 40% ավելի արագ և 4.5 անգամ ավելի քիչ տվյալներ Ես ստիպված էի կարդալ այն:
Աղյուսակի գրառումների նյութականացում CTE-ի միջոցովԹույլ տվեք ձեր ուշադրությունը հրավիրել այն փաստի վրա, որ որոշ դեպքերում Գրառման դաշտերի հետ անմիջապես աշխատելու փորձը ենթահարցում փնտրելուց հետո, առանց այն «փաթաթելու» CTE-ով, կարող է հանգեցնել. «բազմապատկել» InitPlan-ը համամասնական այս նույն դաշտերի թվին.
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
Նույն ռեկորդը «փնտրվել» է 4 անգամ... Մինչև PostgreSQL 11-ը, այս վարքագիծը պարբերաբար տեղի է ունենում, և լուծումը այն CTE-ով «փաթաթելն» է, որը բացարձակ սահման է այս տարբերակներում օպտիմիզատորի համար:
Ռեկուրսիվ կուտակիչ
Նախորդ տարբերակում ընդհանուր առմամբ կարդում ենք 200 տող հանուն պահանջվող 20-ի. ոչ թե 960, այլ ավելի քիչ՝ հնարավո՞ր է։
Փորձենք օգտագործել այն գիտելիքները, որոնք մեզ անհրաժեշտ են ընդհանուր 20 գրառումներ. Այսինքն՝ մենք կրկնելու ենք տվյալների ընթերցումը միայն այնքան ժամանակ, մինչև հասնենք մեզ անհրաժեշտ քանակին:
Քայլ 1. Մեկնարկային ցուցակ
Ակնհայտ է, որ մեր 20 գրառումներից բաղկացած «թիրախային» ցանկը պետք է սկսվի «առաջին» գրառումներով մեր սեփականատեր_id ստեղներից մեկի համար: Հետեւաբար, նախ մենք կգտնենք այդպիսին «Առաջինը» ստեղներից յուրաքանչյուրի համար և ավելացրո՛ւ ցանկին՝ դասավորելով այն մեր ուզած հերթականությամբ - (task_date, id):
Քայլ 2. Գտեք «հաջորդ» գրառումները
Հիմա, եթե վերցնենք մեր ցուցակից առաջին գրառումը և սկսենք «Քայլ» առաջ գնալ ինդեքսի երկայնքով պահպանելով owner_id ստեղնը, ապա գտնված բոլոր գրառումները հենց հաջորդներն են ստացված ընտրության մեջ: Իհարկե, միայն մինչև չանցնենք հետույքի բանալին ցուցակի երկրորդ գրառումը.
Եթե պարզվի, որ մենք «հատել ենք» երկրորդ ռեկորդը, ապա վերջին կարդացված գրառումը պետք է ավելացվի ցուցակում առաջինի փոխարեն (նույն owner_id-ով), որից հետո նորից վերադասավորում ենք ցուցակը։
Այսինքն, մենք միշտ ստանում ենք, որ ցուցակը չունի մեկից ավելի մուտքագրում յուրաքանչյուր ստեղնի համար (եթե մուտքերը վերջանում են, և մենք չենք «խաչում», ապա ցուցակից առաջին գրառումը պարզապես կվերանա, և ոչինչ չի ավելացվի։ ), եւ նրանք միշտ տեսակավորված հավելվածի բանալիի աճման կարգով (task_date, id):
Քայլ 3. զտել և «ընդլայնել» գրառումները
Մեր ռեկուրսիվ ընտրության որոշ տողերում որոշ գրառումներ rv կրկնօրինակվում են. նախ մենք գտնում ենք, որ «անցում է ցուցակի 2-րդ մուտքի սահմանը», այնուհետև այն փոխարինում ենք որպես ցուցակի 1-ին: Այսպիսով, առաջին երևույթը պետք է զտվի:
Սարսափելի վերջնական հարցումը
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; -- берем только "непересекающие" записи
Այսպիսով, մենք վաճառվել է տվյալների ընթերցման 50%-ը կատարման ժամանակի 20%-ով. Այսինքն, եթե դուք հիմքեր ունեք հավատալու, որ կարդալը կարող է երկար տևել (օրինակ, տվյալները հաճախ քեշում չեն, և դուք պետք է գնաք սկավառակի վրա դրա համար), ապա այս կերպ կարող եք ավելի քիչ կախված լինել կարդալուց։ .
Ամեն դեպքում, կատարման ժամանակը ավելի լավ ստացվեց, քան «միամիտ» առաջին տարբերակում։ Բայց այս 3 տարբերակներից որն օգտագործել, կախված է ձեզանից:
Source: www.habr.com