Cyklicznie pojawia się potrzeba wyszukiwania powiązanych danych na podstawie zestawu kluczy, dopóki nie zgromadzimy potrzebne sumaryczne liczby rekordów..
Najbardziej „żywym” przykładem jest wydobycie 20 najstarszych zadań, które figurują na liście pracowników (na przykład w ramach jednego działu). W przypadku różnych zarządczych „dashboardów” z krótkimi podsumowaniami z obszarów pracy podobny temat jest często wymagany.
W artykule omówimy implementację na PostgreSQL „naiwnej” wersji rozwiązania tego zadania, „sprytniejszy” i zupełnie skomplikowany algorytm „cyklu” w SQL z warunkiem wyjścia w zależności od znalezionych danych,który może być przydatny zarówno do ogólnego użytku, jak i do zastosowania w innych podobnych przypadkach.
Weźmy zestaw danych testowych z Aby wyświetlane rekordy nie „skakały” od razu do razu przy pasujących wartościach sortowanych, rozszerzymy indeks tematyczny poprzez dodanie klucza głównego.Od razu nada to mu unikalność i zagwarantuje jednoznaczność kolejności sortowania:
CREATE INDEX ON task(owner_id, task_date, id);
-- stary - usuniemy
DROP INDEX task_owner_id_task_date_idx;Jak słychać, tak i pisać.
Na początku zarysujmy 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; 
Nieco smutno — zamawialiśmy tylko 20 rekordów, a skanowanie indeksu zwróciło nam 960 wierszy,, które potem jeszcze musieliśmy sortować… A spróbujmy czytać mniej.
unnest + ARRAY
Pierwsza myśl, która nam pomoże — jeśli potrzebujemy tylko 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) mamy.
Skorzystamy z tego samego mechanizmu wydobywania i „odwracania w kolumny” pełnych rekordów tabeli,, jak w . Zastosujemy również zgrupowanie do tablicy za pomocą funkcji ARRAY():
WITH T AS (
SELECT
unnest(ARRAY(
SELECT
t
FROM
task t
WHERE
owner_id = unnest
ORDER BY
task_date, id
LIMIT 20 -- ograniczamy tu...
)) 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ż zdecydowanie lepiej! O 40% szybciej, i 4.5 razy mniej danych musieliśmy czytać.
Materializacja rekordów tabeli przez CTE.Zwracam uwagę, że w niektórych przypadkach. Próba pracy z polami zapisu bezpośrednio po ich wyszukaniu w podzapytaniu, bez 'opakowania' w CTE, może prowadzić do 'mnożenia' InitPlan proporcjonalnie do liczby tych 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ólne 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ólne hit=4
-> Skanowanie indeksu za pomocą task_owner_id_task_date_id_idx na task 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ólne 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ólne hit=4
-> Skanowanie indeksu za pomocą task_owner_id_task_date_id_idx na task 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ólne 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ólne hit=4
-> Skanowanie indeksu za pomocą task_owner_id_task_date_id_idx na task 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ólne 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ólne hit=4
-> Skanowanie indeksu za pomocą task_owner_id_task_date_id_idx na task 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ólne hit=4
Ten sam zapis był 'szukany' 4 razy... Do PostgreSQL 11 takie zachowanie występuje regularnie, a rozwiązaniem jest 'opakowanie' w CTE, co stanowi bezwarunkową granicę dla optymalizatora w tych wersjach.
Rekurencyjny akumulator
W poprzedniej wersji sumarycznie przeczytaliśmy 200 wierszy dla 20 potrzebnych. Już nie 960, ale jeszcze mniej – czy można?
Spróbujmy skorzystać z wiedzy, że potrzebujemy łącznie 20 zapisów. Oznacza to, że będziemy iterować pobieranie danych tylko do osiągnięcia potrzebnej nam liczby.
Krok 1: lista startowa
Oczywiście, że nasza 'docelowa' lista 20 zapisów powinna zaczynać się od 'pierwszych' zapisów z jednego z naszych kluczy owner_id. Dlatego najpierw znajdziemy takie 'najwcześniejsze' dla każdego z kluczy i dodamy je do listy, sortując w porządku, który chcemy – (task_date, id).
Krok 2: znajdujemy 'następne' zapisy
Teraz, jeśli weźmiemy pierwszą zapisaną z naszej listy i zaczniemy 'kroczyć' dalej po indeksie z zachowaniem klucza owner_id, to wszystkie znalezione zapisy będą właśnie następnymi w wynikowym zbiorze. Oczywiście, tylko dopóki nie przetniemy klucza aplikacyjnego drugiego wpisu na liście.
Jeśli zdarzyło się, że 'przetnęliśmy' drugi wpis, to ostatnio przeczytany wpis powinien być dodany do listy zamiast pierwszego (z tym samym owner_id), po czym lista jest ponownie sortowana.
To znaczy, że zawsze mamy na liście nie więcej niż jeden wpis dla każdego z kluczy (jeśli wpisy się skończyły, a my nie 'przetnęliśmy', to pierwszy wpis po prostu zniknie z listy i nic nie zostanie dodane), przy czym są zawsze posortowane w porządku rosnącym klucza aplikacyjnego (task_date, id).
Krok 3: filtrujemy i 'rozwijamy' wpisy
W niektórych wierszach naszej rekurencyjnej selekcji niektóre wpisy rv są duplikowane — najpierw znajdujemy te, które 'przecinają granicę 2. wpisu na liście', a potem podstawiamy jako 1. z listy. Tak więc pierwsze wystąpienie należy odfiltrować.
Straszne końcowe zapytanie
Z REKURSJĄ T AS (
-- #1 : dodajemy do listy "pierwsze" rekordy dla każdego z kluczy zbioru
Z wrap AS ( -- "materializujemy" rekordy, aby dostęp do pól nie powodował mnożenia InitPlan/SubPlan
Z T AS (
WYBIERZ
(
WYBIERZ
r
Z
task r
GDZIE
owner_id = unnest
PORZĄDEK
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 (r).task_date, (r).id) lista -- sortujemy listę w żądanym porządku
Z
T
)
WYBIERZ
lista
, lista[1] rv
, FAŁSZ not_cross
, 0 rozmiar
Z
wrap
UNION ALL
-- #2 : odczytujemy rekordy 1-szego klucza, aż nie przeskoczymy przez rekord 2-go
WYBIERZ
PRZYPADKI
-- jeśli nic nie znaleziono dla klucza 1-szego rekordu
GDY X._r JEST NIEODRĘBNY OD NULL WTEDY
T.lista[2:] -- usuwamy go z listy
-- jeśli NIE przeszliśmy przez klucz 2-go rekordu
GDY X.not_cross WTEDY
T.lista -- po prostu kontynuujemy tę samą listę bez modyfikacji
-- jeśli w liście już nie ma 2-go rekordu
GDY T.lista[2] JEST NULL WTEDY
-- po prostu zwracamy pustą listę
'{}'
-- przeszukujemy słownik, usuwając 1-szy rekord i dodając ostatni znaleziony
INACZEJ (
WYBIERZ
coalesce(T.lista[2] || array_agg(r PORZĄDEK (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 AS ( -- "materializujemy" rekord
WYBIERZ
PRZYPADKI
-- jeśli jednak "przeskoczyliśmy" przez 2-gi rekord
GDY NIE T.not_cross
-- to potrzebny rekord - pierwszy z listy
WTEDY T.lista[1]
INACZEJ ( -- jeśli nie przeszliśmy, to klucz pozostał jak w poprzednim rekordzie - opieramy się na nim
WYBIERZ
_r
Z
task _r
GDZIE
owner_id = (rv).owner_id I
(task_date, id) > ((rv).task_date, (rv).id)
PORZĄDEK
task_date, id
LIMIT 1
)
KONIEC _r
)
WYBIERZ
_r
, PRZYPADKI
-- jeśli 2-go rekordu już nie ma na liście, ale znaleźliśmy coś
GDY lista[2] JEST NULL I _r JEST NIEODRĘBNY OD NULL WTEDY
PRAWDA
INACZEJ -- nic nie znaleziono lub "przeskoczyliś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 tutaj liczbę
T.lista JEST NIEODRĘBNY OD '{}' -- lub dopóki lista się nie skończy
)
-- #3 : "rozwijamy" rekordy - porządek jest zapewniony przez strukturę
WYBIERZ
(rv).*
Z
T
GDZIE
not_cross; -- bierzemy tylko "nieprzesuwające" rekordy 
W ten sposób, my wymieniliśmy 50% odczytów danych na 20% czasu wykonaniaOznacza to, że jeśli macie powody, by sądzić, że odczyt może być długi (na przykład, gdy dane często nie znajdują się w pamięci podręcznej i trzeba sięgać po nie z dysku), to w ten sposób można mniej polegać na odczycie.
W każdym razie czas wykonania okazał się lepszy niż w "naiwnym" pierwszym wariancie. Ale który z tych 3 wariantów wybrać — to już zależy od Ciebie.
Źródło: habr.com
