Periode aperas la tasko serĉi rilatajn datumojn per aro de ŝlosiloj. ĝis ni ricevas la bezonatan totalan nombron da rekordoj.
La plej "reala vivo" ekzemplo estas montri 20 plej malnovaj problemoj, listigita sur la listo de dungitoj (ekzemple, ene de unu divido). Por diversaj administraj "instrumentpaneloj" kun mallongaj resumoj de laborareoj, simila temo estas postulata sufiĉe ofte.
En ĉi tiu artikolo ni rigardos la efektivigon en PostgreSQL de "naiva" solvo al tia problemo, "pli inteligenta" kaj tre kompleksa algoritmo. "buklo" en SQL kun elirkondiĉo de la trovitaj datumoj, kiu povas esti utila kaj por ĝenerala evoluo kaj por uzo en aliaj similaj kazoj.
Ni prenu testan datuman aron de . Por malhelpi, ke la montrataj registroj "saltu" de tempo al tempo kiam la ordigitaj valoroj koincidas, vastigi la subjektan indekson aldonante ĉefan ŝlosilon. Samtempe, ĉi tio tuj donos al ĝi unikecon kaj garantios al ni, ke la ordigo estas malambigua:
CREATE INDEX ON task(owner_id, task_date, id);
-- а старый - удалим
DROP INDEX task_owner_id_task_date_idx;Kiel estas auxdita, tiel estas skribite
Unue, ni skizu la plej simplan version de la peto, pasigante la identigilojn de la prezentistoj :
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;
Iom malĝoje - ni mendis nur 20 diskojn, kaj Index Scan resendis ĝin al ni 960 linioj, kiu tiam ankaŭ devis esti ordigita... Ni provu legi malpli.
unnest + ARRAY
La unua konsidero kiu helpos nin estas se ni bezonas nur 20 ordigitaj rekordoj, poste nur legi ne pli ol 20 ordigitaj en la sama ordo por ĉiu ŝlosilo. Bone, taŭga indekso (owner_id, task_date, id) ni havas.
Ni uzu la saman mekanismon por ĉerpi kaj "disvastigi en kolumnojn" integra tablo-rekordo, kiel en . Ni ankaŭ povas apliki faldadon en tabelon uzante la funkcion 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; -- ... и тут - тоже
Ho, jam multe pli bone! 40% pli rapide kaj 4.5 fojojn malpli da datumoj Mi devis legi ĝin.
Materiigo de tabelaj rekordoj per CTELasu min atentigi vin pri tio en iuj kazoj Provo tuj labori kun la kampoj de rekordo post serĉado de ĝi en subdemando, sen "envolvi" ĝin en CTE, povas konduki al "multobligi" InitPlan proporcia al la nombro da ĉi tiuj samaj kampoj:
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
La sama rekordo estis "serĉita" 4 fojojn... Ĝis PostgreSQL 11, ĉi tiu konduto okazas regule, kaj la solvo estas "envolvi" ĝin en CTE, kio estas absoluta limo por la optimumigilo en ĉi tiuj versioj.
Rekursiva akumulilo
En la antaŭa versio, entute ni legas 200 linioj pro la bezonataj 20. Ne 960, sed eĉ malpli — ĉu eblas?
Ni provu uzi la sciojn, kiujn ni bezonas entute 20 rekordoj. Tio estas, ni ripetos datuman legadon nur ĝis ni atingos la kvanton, kiun ni bezonas.
Paŝo 1: Komenca Listo
Evidente, nia "cela" listo de 20 registroj devus komenci per la "unuaj" registroj por unu el niaj posedanto_id-ŝlosiloj. Tial, unue ni trovos tiajn "tre unua" por ĉiu el la ŝlosiloj kaj aldonu ĝin al la listo, ordigante ĝin en la ordo, kiun ni volas - (tasko_dato, id).
Paŝo 2: Trovu la "sekvaj" enskriboj
Nun se ni prenos la unuan eniron el nia listo kaj komencu "paŝi" plu laŭ la indekso konservante la owner_id ŝlosilon, tiam ĉiuj trovitaj registroj estas ĝuste la sekvaj en la rezulta elekto. Kompreneble, nur ĝis ni transiras la pugŝlosilon dua eniro en la listo.
Se montriĝas, ke ni "transiris" la duan rekordon, do la lasta enskribo legita estu aldonita al la listo anstataŭ la unua (kun la sama posedanto_id), post kio ni denove ordigas la liston.
Tio estas, ni ĉiam ricevas, ke la listo havas ne pli ol unu eniron por ĉiu el la ŝlosiloj (se la enskriboj finiĝas kaj ni ne "kruciĝas", tiam la unua eniro el la listo simple malaperos kaj nenio estos aldonita. ), kaj ili ĉiam ordigitaj en kreskanta ordo de la aplikaĵoŝlosilo (task_date, id).
Paŝo 3: filtri kaj "vastigi" rekordojn
En iuj el la vicoj de nia rekursiva elekto, iuj registroj rv estas duobligitaj - unue ni trovas kiel "transiri la limon de la 2-a eniro de la listo", kaj poste anstataŭigu ĝin kiel la 1-an el la listo. Do la unua okazo devas esti filtrita.
La timita fina demando
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; -- берем только "непересекающие" записи
Tiel, ni komercis 50% de datumoj legas por 20% de ekzekuttempo. Tio estas, se vi havas kialojn por kredi, ke legado povas daŭri longan tempon (ekzemple, la datumoj ofte ne estas en la kaŝmemoro, kaj vi devas iri al disko por ĝi), tiam tiamaniere vi povas malpli dependi de legado. .
Ĉiukaze, la ekzekuttempo montriĝis pli bona ol en la "naiva" unua opcio. Sed kiun el ĉi tiuj 3 ebloj uzi dependas de vi.
fonto: www.habr.com