Kontynuujemy serię artykułów poświęconych badaniu mało znanych sposobów na poprawę wydajności "pozornie prostych" zapytań w PostgreSQL:
Nie myślcie, że tak bardzo nie lubię JOIN… 🙂
Jednak często żądanie bez niego okazuje się zauważalnie wydajniejsze. Dlatego dzisiaj spróbujemy całkowicie pozbyć się zasobożernego JOIN — za pomocą słownika.
Rozpoczynając od PostgreSQL 12, część opisanych poniżej sytuacji może być odtwarzana nieco inaczej z powodu . To zachowanie można przywrócić do poprzedniego, podając klucz
MATERIALIZED.
Wiele «faktów» w ograniczonym słowniku
Weźmy całkiem realne zadanie praktyczne — musimy wyświetlić listę lub aktywnych zadań od nadawców:
25.01 | Ivanov I.I. | Przygotować opis nowego algorytmu.
22.01 | Ivanov I.I. | Napisać artykuł na Habrze: życie bez JOIN.
20.01 | Petrov P.P. | Pomóc optymalizować zapytanie.
18.01 | Ivanov I.I. | Napisać artykuł na Habrze: JOIN z uwzględnieniem rozkładu danych.
16.01 | Petrov P.P. | Pomóc optymalizować zapytanie.
W abstrakcyjnym świecie autorzy zadań powinni się jednak równomiernie rozkładać wśród wszystkich pracowników naszej organizacji, ale w rzeczywistości zadania trafiają zazwyczaj od dość ograniczonej liczby osób — „od kierownictwa” w górę hierarchii lub „od współpracowników” z sąsiednich działów (analitycy, projektanci, marketing, …).
Przyjmijmy, że w naszej organizacji z 1000 osób tylko 20 autorów (zwykle nawet mniej) stawia zadania do każdego konkretnego wykonawcy i , aby przyspieszyć „tradycyjne” zapytanie.
Generator skryptów
-- pracownicy
CREATE TABLE person AS
SELECT
id
, repeat(chr(ascii('a') + (id % 26)), (id % 32) + 1) "name"
, '2000-01-01'::date - (random() * 1e4)::integer birth_date
FROM
generate_series(1, 1000) id;
ALTER TABLE person ADD PRIMARY KEY(id);
-- zadania z określonym rozkładem
CREATE TABLE task AS
WITH aid AS (
SELECT
id
, array_agg((random() * 999)::integer + 1) aids
FROM
generate_series(1, 1000) id
, generate_series(1, 20)
GROUP BY
1
)
SELECT
*
FROM
(
SELECT
id
, '2020-01-01'::date - (random() * 1e3)::integer task_date
, (random() * 999)::integer + 1 owner_id
FROM
generate_series(1, 100000) id
) T
, LATERAL(
SELECT
aids[(random() * (array_length(aids, 1) - 1))::integer + 1] author_id
FROM
aid
WHERE
id = T.owner_id
LIMIT 1
) a;
ALTER TABLE task ADD PRIMARY KEY(id);
CREATE INDEX ON task(owner_id, task_date);
CREATE INDEX ON task(author_id);
Pokażemy ostatnie 100 zadań dla konkretnego wykonawcy:
WYBIERZ
task.*
, person.name
Z
task
LEWY DOŁĄCZ
person
ON person.id = task.author_id
GDZIE
owner_id = 777
ZAMÓWIENIE
task_date DESC
LIMIT 100; 
Wynika z tego, że 1/3 całego czasu i 3/4 lektur stron danych zostało stworzonych tylko po to, aby 100 razy poszukać autora — dla każdej wyświetlanej zadania. Ale przecież wiemy, że w tej setce jest zaledwie 20 różnych — czy nie można by wykorzystać tej wiedzy?
słownik hstore
Skorzystamy do generacji "słownika" klucz-wartość:
UTWÓRZ EXTENSION hstoreW słowniku wystarczy umieścić ID autora i jego imię, aby później móc wydobyć według tego klucza:
-- tworzymy docelowy wybór
Z T AS (
WYBIERZ
*
Z
task
GDZIE
owner_id = 777
ZAMÓWIENIE
task_date DESC
LIMIT 100
)
-- tworzymy słownik dla unikalnych wartości
, dict AS (
WYBIERZ
hstore( -- hstore(klucze::text[], wartości::text[])
array_agg(id)::text[]
, array_agg(name)::text[]
)
Z
person
GDZIE
id = ANY(ARRAY(
WYBIERZ UNIKALNE
author_id
Z
T
))
)
-- otrzymujemy powiązane wartości słownika
WYBIERZ
*
, (TABLE dict) -> author_id::text -- hstore -> klucz
Z
T; 
Na uzyskanie informacji o osobach wydano dwa razy mniej czasu i siedem razy mniej przeczytanych danych! Oprócz "słownikowania", tych wyników pomogło nam osiągnąć także masowe wydobycie rekordów z tabeli w jednym przejściu przy pomocy = ANY(ARRAY(...)).
Rekordy tabeli: serializacja i deserializacja
Ale co zrobić, jeśli musimy zachować w słowniku nie jedno pole tekstowe, ale cały rekord? W takim przypadku pomoże nam zdolność PostgreSQL praca z rekordem tabeli jako z pojedynczą wartością:
...
, dict AS (
WYBIERZ
hstore(
array_agg(id)::text[]
, array_agg(p)::text[] -- magia #1
)
Z
person p
GDZIE
...
)
WYBIERZ
*
, (((TABLE dict) -> author_id::text)::person).* -- magia #2
Z
T;Przyjrzyjmy się, co tu właściwie się działo:
- Wzięliśmy p jako alias dla pełnego rekordu tabeli person i zebraliśmy je w tablicę.
- Ta tablica rekordów została przekonwertowana na tablicę tekstowych ciągów (person[]::text[]), aby móc ją umieścić w słowniku hstore jako tablicę wartości.
- Przy uzyskiwaniu powiązanego rekordu wyciągnęliśmy go ze słownika po kluczu jako ciąg tekstowy.
- Ciąg musimy przekształcić w wartość typu tabeli person (dla każdej tabeli automatycznie tworzony jest odpowiadający jej typ).
- "Rozwinęliśmy" typizowany rekord w kolumny za pomocą
(...).*.
słownika json
Jednak taki trik, jak zastosowaliśmy powyżej, nie zadziała, jeśli nie ma odpowiedniego typu tabeli, aby wykonać „rzutowanie”. Taka sama sytuacja wystąpi, jeśli spróbujemy użyć ciągu CTE, a nie „rzeczywistej” tabeli.
W tym przypadku pomogą nam :
...
, p AS ( -- to już CTE
SELECT
*
FROM
person
WHERE
...
)
, dict AS (
SELECT
json_object( -- teraz to już json
array_agg(id)::text[]
, array_agg(row_to_json(p))::text[] -- i wewnątrz json dla każdego wiersza
)
FROM
p
)
SELECT
*
FROM
T
, LATERAL(
SELECT
*
FROM
json_to_record(
((TABLE dict) ->> author_id::text)::json -- wyciągnięte ze słownika jako json
) AS j(name text, birth_date date) -- wypełniliśmy potrzebną nam strukturę
) j; Należy zauważyć, że przy opisywaniu docelowej struktury możemy wymieniać nie wszystkie pola pierwotnego wiersza, a tylko te, które naprawdę są nam potrzebne. Jeśli mamy „rodzinną” tabelę, lepiej skorzystać z funkcji json_populate_record.
Dostęp do słownika odbywa się wciąż jednokrotnie, ale koszty na json-[de]serializację są dość wysokie, dlatego taki sposób rozsądnie stosować tylko w niektórych przypadkach, gdy „uczciwy” CTE Scan wypada gorzej.
Testujemy wydajność
Zatem mamy dwa sposoby serializacji danych do słownika — hstore / json_object. Oprócz tego same tablice kluczy i wartości można również stworzyć na dwa sposoby, z wewnętrzną lub zewnętrzną konwersją do tekstu: array_agg(i::text) / array_agg(i)::text[].
Sprawdźmy efektywność różnych rodzajów serializacji na czysto syntetycznym przykładzie — serializujemy różną liczbę kluczy:
WITH dict AS (
SELECT
hstore(
array_agg(i::text)
, array_agg(i::text)
)
FROM
generate_series(1, ...) i
)
TABLE dict;Skrypt oceniający: serializacja
WITH T AS (
SELECT
*
, (
SELECT
regexp_replace(ea[array_length(ea, 1)], '^Execution Time: (d+.d+) ms$', '1')::real et
FROM
(
SELECT
array_agg(el) ea
FROM
dblink('port= ' || current_setting('port') || ' dbname=' || current_database(), $$
explain analyze
WITH dict AS (
SELECT
hstore(
array_agg(i::text)
, array_agg(i::text)
)
FROM
generate_series(1, $$ || (1 << v) || $$) i
)
TABLE dict
$$) T(el text)
) T
) et
FROM
generate_series(0, 19) v
, LATERAL generate_series(1, 7) i
ORDER BY
1, 2
)
SELECT
v
, avg(et)::numeric(32,3)
FROM
T
GROUP BY
1
ORDER BY
1; 
Na PostgreSQL 11 mniej więcej do rozmiaru słownika 2^12 kluczy serializacja w json wymaga mniej czasu. Najskuteczniejszą metodą jest połączenie json_object i „wewnętrznego” konwersji typów. array_agg(i::text).
Teraz spróbujmy odczytać wartość każdego klucza 8 razy — bo jeśli nie korzystamy ze słownika, to po co on w ogóle jest?
Skrypt oceniający: odczytywanie ze słownika.
WITH T AS (
SELECT
*
, (
SELECT
regexp_replace(ea[array_length(ea, 1)], '^Czas wykonania: (d+.d+) ms$', '1')::real et
FROM
(
SELECT
array_agg(el) ea
FROM
dblink('port= ' || current_setting('port') || ' dbname=' || current_database(), $$
explain analyze
WITH dict AS (
SELECT
json_object(
array_agg(i::text)
, array_agg(i::text)
)
FROM
generate_series(1, $$ || (1 << v) || $$) i
)
SELECT
(TABLE dict) -> (i % ($$ || (1 << v) || $$) + 1)::text
FROM
generate_series(1, $$ || (1 << (v + 3)) || $$) i
$$) T(el text)
) T
) et
FROM
generate_series(0, 19) v
, LATERAL generate_series(1, 7) i
ORDER BY
1, 2
)
SELECT
v
, avg(et)::numeric(32,3)
FROM
T
GROUP BY
1
ORDER BY
1; 
I… już mniej więcej przy 2^6 kluczach odczyt z json-słownika zaczyna wyraźnie przegrywać z odczytem z hstore, dla jsonb to samo dzieje się przy 2^9.
Wnioski końcowe:
- jeśli musisz zrobić JOIN z wielokrotnie powtarzającymi się rekordami — lepiej użyć „słownikowania” tabeli.
- jeśli twój słownik będzie mały i odczytujesz z niego niewiele — można użyć json[b].
- we wszystkich innych przypadkach hstore + array_agg(i::text) będzie bardziej efektywny.
Źródło: habr.com
