Lẹẹkọọkan, iṣẹ ṣiṣe wiwa data ti o jọmọ nipa lilo awọn bọtini ṣeto dide. titi a o fi gba nọmba lapapọ ti awọn igbasilẹ ti a beere.
Apẹẹrẹ “igbesi aye gidi” julọ ni lati ṣafihan 20 Atijọ isoro, akojọ lori akojọ awọn oṣiṣẹ (fun apẹẹrẹ, laarin ọkan pipin). Fun ọpọlọpọ awọn “dasibodu” iṣakoso pẹlu awọn akojọpọ kukuru ti awọn agbegbe iṣẹ, koko-ọrọ ti o jọra ni a nilo nigbagbogbo.
Ninu nkan yii a yoo wo imuse ni PostgreSQL ti ojutu “naive” si iru iṣoro kan, “ogbon” ati algorithm eka pupọ. “lupu” ni SQL pẹlu ipo ijade lati data ti o rii, eyiti o le wulo mejeeji fun idagbasoke gbogbogbo ati fun lilo ni awọn ọran miiran ti o jọra.
Jẹ ká ya a igbeyewo data ṣeto lati . Lati ṣe idiwọ awọn igbasilẹ ti o han lati “fifo” lati igba de igba nigbati awọn iye ti a ti ṣeto ni ibamu, faagun atọka koko-ọrọ nipa fifi bọtini akọkọ kan kun. Ni akoko kanna, eyi yoo fun ni iyasọtọ lẹsẹkẹsẹ ati ṣe iṣeduro fun wa pe aṣẹ tito lẹtọ jẹ aibikita:
CREATE INDEX ON task(owner_id, task_date, id);
-- а старый - удалим
DROP INDEX task_owner_id_task_date_idx;Gẹ́gẹ́ bí a ti ń gbọ́, bẹ́ẹ̀ ni a kọ ọ́
Ni akọkọ, jẹ ki a ṣe apẹrẹ ẹya ti o rọrun julọ ti ibeere naa, ti o kọja awọn ID ti awọn oṣere :
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;
Ibanujẹ diẹ - a paṣẹ awọn igbasilẹ 20 nikan, ṣugbọn Atọka Scan da pada si wa 960 ila, eyiti lẹhinna tun ni lati ṣe lẹsẹsẹ… Jẹ ki a gbiyanju lati ka kere si.
unnest + ARRAY
Dogbapọnna tintan he na gọalọna mí wẹ eyin mí tindo nuhudo etọn nikan 20 lẹsẹsẹ awọn igbasilẹ, lẹhinna kan ka ko si siwaju sii ju 20 lẹsẹsẹ ni kanna ibere fun kọọkan bọtini. O dara, o dara atọka (owner_id, task_date, id) a ni.
Jẹ ki a lo ẹrọ kanna fun yiyo ati “itankale sinu awọn ọwọn” igbasilẹ tabili akojọpọ, bi ninu . A tun le lo kika sinu orun nipa lilo iṣẹ naa 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, pupọ dara julọ tẹlẹ! 40% yiyara ati awọn akoko 4.5 kere si data Mo ni lati ka.
Ohun elo ti awọn igbasilẹ tabili nipasẹ CTEJẹ ki n fa ifojusi rẹ si otitọ pe ni awọn igba miiran Igbiyanju lati ṣiṣẹ lẹsẹkẹsẹ pẹlu awọn aaye ti igbasilẹ lẹhin wiwa rẹ ni abẹlẹ, laisi “fi ipari” rẹ ni CTE, le ja si "isodipupo" InitPlan ni ibamu si nọmba awọn aaye kanna:
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
Igbasilẹ kanna ni a “wo soke” awọn akoko 4… Titi di PostgreSQL 11, ihuwasi yii waye nigbagbogbo, ati pe ojutu ni lati “fi ipari si” ni CTE, eyiti o jẹ opin pipe fun oluṣapejuwe ninu awọn ẹya wọnyi.
Recursive accumulator
Ninu ẹya ti tẹlẹ, lapapọ a ka 200 ila fun awọn nitori ti awọn ti a beere 20. Ko 960, sugbon ani kere - o ṣee ṣe?
Jẹ ká gbiyanju lati lo imo ti a nilo lapapọ 20 awọn igbasilẹ. Iyẹn ni, a yoo ṣe atunwo kika data nikan titi ti a yoo fi de iye ti a nilo.
Igbesẹ 1: Akojọ Ibẹrẹ
O han ni, atokọ “afojusun” wa ti awọn igbasilẹ 20 yẹ ki o bẹrẹ pẹlu awọn igbasilẹ “akọkọ” fun ọkan ninu awọn bọtini oluwa_id wa. Nitorina, akọkọ a yoo ri iru "gan akọkọ" fun kọọkan ti awọn bọtini ki o si fi kun si atokọ, titọ lẹsẹsẹ ni aṣẹ ti a fẹ - (iṣẹ-ṣiṣe_date, id).
Igbesẹ 2: Wa awọn titẹ sii "tókàn".
Bayi ti a ba gba titẹsi akọkọ lati atokọ wa ki o bẹrẹ "igbesẹ" siwaju sii pẹlu atọka titọju bọtini onihun_id, lẹhinna gbogbo awọn igbasilẹ ti o rii jẹ deede awọn atẹle ni yiyan abajade. Dajudaju, nikan titi a o fi kọja bọtini apọju keji titẹsi ninu awọn akojọ.
Ti o ba wa ni pe a "rekoja" igbasilẹ keji, lẹhinna kika ti o kẹhin yẹ ki o fi kun si atokọ dipo ti akọkọ (pẹlu onihun_id kanna), lẹhin eyi a tun-to akojọ naa lẹẹkansi.
Iyẹn ni, a nigbagbogbo gba pe atokọ ko ni titẹ sii ju ọkan lọ fun awọn bọtini kọọkan (ti awọn titẹ sii ba pari ati pe a ko “kọja”, lẹhinna titẹsi akọkọ lati atokọ naa yoo parẹ ati pe ko si nkankan ti yoo ṣafikun. ), ati awọn nigbagbogbo lẹsẹsẹ ni aṣẹ ti bọtini ohun elo (task_date, id).
Igbesẹ 3: àlẹmọ ati “faagun” awọn igbasilẹ
Ni diẹ ninu awọn ori ila ti yiyan recursive wa, diẹ ninu awọn igbasilẹ rv ti wa ni pidánpidán - akọkọ a ri iru bi "rekọja awọn aala ti awọn 2nd titẹsi ti awọn akojọ", ati ki o si aropo o bi awọn 1st lati awọn akojọ. Nitorinaa iṣẹlẹ akọkọ nilo lati ṣe filtered.
Ibeere ikẹhin ti o bẹru
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; -- берем только "непересекающие" записи
Bayi, a ta 50% ti data kika fun 20% ti akoko ipaniyan. Iyẹn ni, ti o ba ni awọn idi lati gbagbọ pe kika le gba akoko pipẹ (fun apẹẹrẹ, data nigbagbogbo ko si ninu kaṣe, ati pe o ni lati lọ si disk fun rẹ), lẹhinna ni ọna yii o le dale kere si lori kika. .
Ni eyikeyi idiyele, akoko ipaniyan ti jade lati dara ju aṣayan akọkọ “naive” lọ. Ṣugbọn ewo ninu awọn aṣayan mẹta wọnyi lati lo jẹ tirẹ.
orisun: www.habr.com