Okresowo pojawia się potrzeba wyszukiwania powiązanych danych na podstawie zestawu kluczy, dopóki nie zgromadzimy potrzebnej sumy rekordów.
Najbardziej „życiowy” przykład — zestawić 20 najstarszych zadań, figuruje na liście pracowników (na przykład, w obrębie jednego działu). W różnych menedżerskich „kokpitach” z krótkimi podsumowaniami dotyczących obszarów pracy, podobny temat pojawia się dosyć często.
W artykule rozważymy implementację w PostgreSQL „naiwnej” wersji rozwiązania takiego zadania, „sprytniejszy” i całkowicie złożony algorytm „pętli” w SQL z warunkiem wyjścia z danych znalezionych, który może być przydatny zarówno do ogólnej wiedzy, jak i do zastosowania w innych podobnych sytuacjach.
Weźmiemy testowy zestaw danych z . Aby wyświetlane rekordy nie „skakały” od razu przy powtszędzających się wartości sortowanych, rozszerzymy indeks przedmiotowy poprzez dodanie klucza głównego. To od razu nada mu unikalność i zapewni jednoznaczność kolejności sortowania:
UTWÓRZ INDEKS NA task(owner_id, task_date, id);
-- a stary - usuniemy
USUŃ INDEKS task_owner_id_task_date_idx;Jak się słyszy, tak się pisze
Najpierw szkicujemy najprostszy wariant zapytania, przekazując ID wykonawców :
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; 
Trochę smutno — zamówiliśmy tylko 20 rekordów, a skanowanie indeksu zwróciło nam 960 wierszy, które jeszcze musieliśmy posortować… A spróbujmy czytać mniej.
unnest + TABLICA
Pierwsza myśl, która nam pomoże — jeśli potrzebujemy zaledwie 20 posortowanych rekordów, wystarczy czytać nie więcej niż 20 posortowanych w tej samej kolejności dla każdego klucza. Na szczęście, odpowiedni indeks (owner_id, task_date, id) już mamy.
Skorzystamy z tego samego mechanizmu wydobycia i 'rozkładania na kolumny' całościowego wpisu tabeli, co w . A także zastosujemy złożenie w tablicę z pomocą funkcji TABLICA():
Z T JAKO (
SELECT
unnest(TABLICA(
SELECT
t
FROM
task t
WHERE
owner_id = unnest
ORDER BY
task_date, id
LIMIT 20 -- ograniczamy tutaj...
)) 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; -- ... i tutaj - również 
O, już znacznie lepiej! O 40% szybciej i 4,5 razy mniej danych trzeba było przeczytać.
Materializacja rekordów tabeli za pomocą CTEZwracam uwagę, że w niektórych przypadkach próba pracy z polami rekordu zaraz po jego wyszukaniu w podzapytaniu, bez „opakowywania” w CTE, może prowadzić do „powielania” InitPlan proporcjonalnie do liczby tych właśnie pól:
SELECT
((
SELECT
t
FROM
task t
WHERE
owner_id = 1
ORDER BY
task_date, id
LIMIT 1
).*);Wynik (koszt=4.77..4.78 wierszy=1 szerokość=16) (rzeczywisty czas=0.063..0.063 wierszy=1 pętli=1)
Bufory: wspólny hit=16
InitPlan 1 (zwraca $0)
-> Limit (koszt=0.42..1.19 wierszy=1 szerokość=48) (rzeczywisty czas=0.031..0.032 wierszy=1 pętli=1)
Bufory: wspólny hit=4
-> Skanowanie indeksu przy użyciu task_owner_id_task_date_id_idx w zadaniu t (koszt=0.42..387.57 wierszy=500 szerokość=48) (rzeczywisty czas=0.030..0.030 wierszy=1 pętli=1)
Warunek indeksu: (owner_id = 1)
Bufory: wspólny hit=4
InitPlan 2 (zwraca $1)
-> Limit (koszt=0.42..1.19 wierszy=1 szerokość=48) (rzeczywisty czas=0.008..0.009 wierszy=1 pętli=1)
Bufory: wspólny hit=4
-> Skanowanie indeksu przy użyciu task_owner_id_task_date_id_idx w zadaniu t_1 (koszt=0.42..387.57 wierszy=500 szerokość=48) (rzeczywisty czas=0.008..0.008 wierszy=1 pętli=1)
Warunek indeksu: (owner_id = 1)
Bufory: wspólny hit=4
InitPlan 3 (zwraca $2)
-> Limit (koszt=0.42..1.19 wierszy=1 szerokość=48) (rzeczywisty czas=0.008..0.008 wierszy=1 pętli=1)
Bufory: wspólny hit=4
-> Skanowanie indeksu przy użyciu task_owner_id_task_date_id_idx w zadaniu t_2 (koszt=0.42..387.57 wierszy=500 szerokość=48) (rzeczywisty czas=0.008..0.008 wierszy=1 pętli=1)
Warunek indeksu: (owner_id = 1)
Bufory: wspólny hit=4"
InitPlan 4 (zwraca $3)
-> Limit (koszt=0.42..1.19 wierszy=1 szerokość=48) (rzeczywisty czas=0.009..0.009 wierszy=1 pętli=1)
Bufory: wspólny hit=4
-> Skanowanie indeksu przy użyciu task_owner_id_task_date_id_idx w zadaniu t_3 (koszt=0.42..387.57 wierszy=500 szerokość=48) (rzeczywisty czas=0.009..0.009 wierszy=1 pętli=1)
Warunek indeksu: (owner_id = 1)
Bufory: wspólny hit=4
Ta sama rekord «znajdowała się» 4 razy... Do PostgreSQL 11 to zachowanie występuje regularnie, a rozwiązaniem jest „opakowanie” w CTE, co jest niewątpliwą granicą dla optymalizatora w tych wersjach.
Rekurencyjny akumulator
W poprzedniej wersji łącznie przeczytaliśmy 200 wierszy aby uzyskać potrzebne 20. Już nie 960, ale nadal mniej — czy to możliwe?
Spróbujmy skorzystać z wiedzy, że potrzebujemy zaledwie 20 rekordów. Oznacza to, że będziemy iterować pobieranie danych tylko do osiągnięcia potrzebnej liczby.
Krok 1: lista początkowa
Oczywiście nasza „docelowa” lista 20 rekordów powinna zaczynać się od „pierwszych” rekordów według jednego z naszych kluczy owner_id. Dlatego najpierw znajdźmy takie „najwcześniejsze” według każdego z kluczy i dodajmy je do listy, sortując ją w porządku, który chcemy — (task_date, id).
Krok 2: znajdujemy „następne” rekordy
Teraz, jeśli weźmiemy pierwszą pozycję z naszej listy i zaczniemy „kroczyć” dalej po indeksie z zachowaniem klucza owner_id, to wszystkie znalezione rekordy będą właśnie kolejnymi w wynikowym zbiorze. Oczywiście tylko dopóki nie przekroczyliśmy klucza aplikacji drugiego rekordu na liście.
Jeśli okazało się, że „przecięliśmy” drugi zapis, to ostatni przeczytany zapis powinien zostać dodany do listy zamiast pierwszego (z tym samym owner_id), po czym lista jest ponownie sortowana.
Oznacza to, że zawsze będziemy mieć w liście nie więcej niż jeden zapis dla każdego z kluczy (jeśli zapisy się skończą, a my nie „przecięliśmy”, to pierwszy zapis po prostu zniknie z listy i nic nie zostanie dodane), przy czym są zawsze posortowane w porządku rosnącym klucza aplikacji (task_date, id).
Krok 3: filtrujemy i „rozwijamy” zapisy
W niektórych linijkach naszego rekurencyjnego wyboru niektóre zapisy rv się dublują — najpierw znajdujemy te, które „przecinają granicę 2. zapisu na liście”, a potem wstawiamy jako 1. na liście. Tak więc pierwsze pojawienie się należy odfiltrować.
Straszne końcowe zapytanie
Z REKURSJĄ T JAKO (
-- #1 : zbieramy "pierwsze" zapisy dla każdego z kluczy zbioru
Z wrap JAKO ( -- "materializujemy" rekordy, aby odwołanie do pól nie powodowało mnożenia InitPlan/SubPlan
Z T JAKO (
WYBIERZ
(
WYBIERZ
r
Z
zadanie r
GDZIE
owner_id = unnest
PORZĄDEK Z
task_date, id
LIMIT 1
) r
Z
unnest('{1,2,4,8,16,32,64,128,256,512}'::integer[])
)
WYBIERZ
array_agg(r PORZĄDEK Z (r).task_date, (r).id) lista -- sortujemy listę w odpowiedniej kolejności
Z
T
)
WYBIERZ
lista
, lista[1] rv
, FAŁSZ not_cross
, 0 rozmiar
Z
wrap
UNIA WSZYSTKIE
-- #2 : odczytujemy zapisy 1-wszego klucza, aż nie przeskoczymy przez zapis 2-giego
WYBIERZ
PRZYPADEK
-- jeśli nic nie znaleziono dla klucza 1-wszego zapisu
KIEDY X._r JEST NIE ODRĘBNE OD NULL TO
T.lista[2:] -- usuwamy go z listy
-- jeśli NIE przeszliśmy przez klucz 2-giego zapisu
KIEDY X.not_cross TO
T.lista -- po prostu kontynuujemy tę samą listę bez modyfikacji
-- jeśli lista już nie zawiera 2-giego zapisu
KIEDY T.lista[2] JEST NULL TO
-- po prostu zwracamy pustą listę
'{}'
-- przestawiamy słownik, usuwając 1-szy zapis i dodając ostatni znaleziony
INACZEJ (
WYBIERZ
coalesce(T.lista[2] || array_agg(r PORZĄDEK Z (r).task_date, (r).id), '{}')
Z
unnest(T.lista[3:] || X._r) r
)
KONIEC
, X._r
, X.not_cross
, T.rozmiar + X.not_cross::integer
Z
T
, LATERAL(
Z wrap JAKO ( -- "materializujemy" rekord
WYBIERZ
PRZYPADEK
-- jeśli rzeczywiście "przeszliśmy" przez 2-gi zapis
KIEDY NIE T.not_cross
-- to potrzebny zapis - pierwszy z listy
WTEDY T.lista[1]
INACZEJ ( -- jeśli nie przeszliśmy, to klucz pozostał jak w poprzednim zapisie - od niego się odwołujemy
WYBIERZ
_r
Z
zadanie _r
GDZIE
owner_id = (rv).owner_id I
(task_date, id) > ((rv).task_date, (rv).id)
PORZĄDEK Z
task_date, id
LIMIT 1
)
KONIEC _r
)
WYBIERZ
_r
, PRZYPADKU
-- jeśli 2-gi zapis już nie istnieje na liście, ale coś znaleźliśmy
KIEDY lista[2] JEST NULL I _r JEST ODRĘBNE OD NULL TO
PRAWDA
INACZEJ -- nic nie znaleziono lub "przeszliśmy"
coalesce(((_r).task_date, (_r).id) < ((lista[2]).task_date, (lista[2]).id), FAŁSZ)
KONIEC not_cross
Z
wrap
) X
GDZIE
T.rozmiar < 20 I -- ograniczamy ilość
T.lista JEST NIE ODRĘBNE OD '{}' -- lub dopóki lista się nie skończyła
)
-- #3 : "rozwijamy" zapisy - kolejność gwarantowana przez konstrukcję
WYBIERZ
(rv).*
Z
T
GDZIE
not_cross; -- bierzemy tylko "nieprzeszłe" zapisy 
W ten sposób wymieniliśmy 50% odczytów danych na 20% czasu wykonania . Oznacza to, że jeśli istnieją powody, aby sądzić, że odczyt może być długi (na przykład, gdy dane często nie są w pamięci podręcznej i trzeba je pobierać z dysku), to w ten sposób można zmniejszyć zależność od odczytów.W każdym razie czas wykonania okazał się lepszy niż w «naiwnym» pierwszym wariancie. Ale który z tych 3 wariantów używać — to zależy od Ciebie.
Wydanie środowiska programistycznego KDevelop 5.5
Źródło: habr.com
