Időnként felmerül a kapcsolódó adatok kulcskészlettel történő keresésének feladata. amíg meg nem kapjuk a szükséges összes rekordot.
A leginkább „valódi életű” példa a megjelenítés 20 legrégebbi probléma, listázott az alkalmazottak listáján (például egy divízión belül). A különböző menedzsment „műszerfalakhoz” a munkaterületek rövid összefoglalásával gyakran van szükség hasonló témára.
A cikkben megvizsgáljuk egy ilyen probléma megoldásának „naiv” változatának, egy „okosabb” és nagyon összetett algoritmusnak a PostgreSQL-en való megvalósítását. „hurok” SQL-ben a talált adatokból való kilépési feltétellel, amely általános fejlesztéshez és más hasonló esetekben is hasznos lehet.
Vegyünk egy teszt adathalmazt innen . Annak elkerülése érdekében, hogy a megjelenített rekordok időről időre „ugráljanak”, amikor a rendezett értékek egybeesnek, bővítse ki a tárgymutatót egy elsődleges kulcs hozzáadásával. Ugyanakkor ez azonnal egyedivé teszi, és garantálja számunkra, hogy a rendezési sorrend egyértelmű legyen:
CREATE INDEX ON task(owner_id, task_date, id);
-- а старый - удалим
DROP INDEX task_owner_id_task_date_idx;Ahogy hallják, úgy meg van írva
Először vázoljuk fel a kérés legegyszerűbb változatát, átadva az előadók azonosítóit :
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;
Kicsit szomorú – csak 20 lemezt rendeltünk, de az Index Scan visszaküldte nekünk 960 sor, amit aztán szintén válogatni kellett... Próbáljunk meg kevesebbet olvasni.
unnest + ARRAY
Az első megfontolás, amely segít nekünk, az, ha szükségünk van rá csak 20 rendezve feljegyzéseket, majd csak olvassa el legfeljebb 20, mindegyikhez ugyanabban a sorrendben rendezve kulcs. Jó, megfelelő index (tulajdonos_azonosítója, feladat_dátuma, azonosítója) van.
Használjuk ugyanazt a mechanizmust a kinyeréshez és az oszlopokba való szétterítéshez. teljes táblázatbevitel, mint a . A függvény segítségével tömbbe hajtogatást is alkalmazhatunk 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; -- ... и тут - тоже
Ó, már sokkal jobb! 40%-kal gyorsabb és 4.5-szer kevesebb adat el kellett olvasnom.
Táblázatrekordok materializálása CTE-n keresztülHadd hívjam fel a figyelmet arra a tényre bizonyos esetekben Ha megpróbál azonnal dolgozni egy rekord mezőivel, miután megkereste azt egy segédlekérdezésben, anélkül, hogy egy CTE-be „csomagolná” azt, "sokszorozza" az InitPlan-t arányos ugyanezen mezők számával:
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
Ugyanazt a rekordot négyszer „keresték”… A PostgreSQL 4-ig ez a viselkedés rendszeresen előfordul, és a megoldás a CTE-be „csomagolás”, ami ezekben a verziókban az optimalizáló számára feltétlen határt jelent.
Rekurzív akkumulátor
Az előző verzióban összesen olvastunk 200 sor a szükséges 20 kedvéért. Nem 960, de még kevesebb - lehetséges?
Próbáljuk meg felhasználni azt a tudást, amire szükségünk van összesen 20 rekordokat. Vagyis csak addig iteráljuk az adatolvasást, amíg el nem érjük a szükséges mennyiséget.
1. lépés: Kezdő lista
Nyilvánvalóan a 20 rekordot tartalmazó „cél” listánk az egyik tulajdonos_id kulcsunk „első” rekordjával kezdődik. Ezért először találunk ilyeneket „legelső” minden egyes billentyűnél és adjuk hozzá a listához, a kívánt sorrendbe rendezve - (feladat_dátuma, id).
2. lépés: Keresse meg a „következő” bejegyzéseket
Ha kivesszük a listánk első bejegyzését, és kezdjük „lépjen” tovább az index mentén megőrizve a tulajdonos_azonosító kulcsot, akkor az összes talált rekord pontosan a következő lesz az eredményül kapott kijelölésben. Természetesen csak amíg át nem lépjük a fenékkulcsot második bejegyzés a listán.
Ha kiderül, hogy „átléptük” a második rekordot, akkor az utoljára olvasott bejegyzést kell hozzáadni a listához az első helyett (ugyanazzal a tulajdonos_azonosítóval), ami után újra átrendezzük a listát.
Vagyis mindig azt kapjuk, hogy a listán minden kulcshoz nem tartozik több bejegyzés (ha elfogynak a bejegyzések és nem „keresztezzük át”, akkor az első bejegyzés egyszerűen eltűnik a listáról, és nem lesz hozzáadva semmi ), és ők mindig rendezve az alkalmazás kulcsának (feladat_dátuma, azonosítója) növekvő sorrendjében.
3. lépés: szűrje ki és „bontsa ki” a rekordokat
Rekurzív válogatásunk néhány sorában néhány rekord rv megkettőződnek - először találunk például „átlépés a lista 2. bejegyzésének határán”, majd helyettesítjük a lista 1. bejegyzésével. Tehát az első előfordulást szűrni kell.
A rettegett végső kérdés
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; -- берем только "непересекающие" записи
Így mi az adatok 50%-át a végrehajtási idő 20%-ára értékesítette. Vagyis ha okkal feltételezi, hogy az olvasás sokáig tarthat (például az adatok gyakran nincsenek a gyorsítótárban, és ehhez lemezre kell mennie), akkor így kevésbé számíthat az olvasásra. .
Mindenesetre a végrehajtási idő jobbnak bizonyult, mint a „naiv” első lehetőségnél. De a 3 lehetőség közül melyiket használja, az Önön múlik.
Forrás: will.com