Nastavljamo seriju članaka posvećenih proučavanju malo poznatih načina za poboljšanje performansi "naizgled jednostavnih" PostgreSQL upita:
Nemojte misliti da mi se JOIN ne sviđa toliko... :)
Ali često se bez njega zahtjev pokaže znatno produktivnijim nego s njim. Danas ćemo pokušati riješite se resursno intenzivnog JOIN-a - korištenjem rječnika.
Počevši od PostgreSQL 12, neke od dolje opisanih situacija mogu se reproducirati malo drugačije zbog . Ovo ponašanje se može vratiti navođenjem ključa
MATERIALIZED.
Mnogo "činjenica" u ograničenom rječniku
Uzmimo vrlo stvarni zadatak aplikacije - trebamo prikazati listu ili aktivni zadaci sa pošiljaocima:
25.01 | Иванов И.И. | Подготовить описание нового алгоритма.
22.01 | Иванов И.И. | Написать статью на Хабр: жизнь без JOIN.
20.01 | Петров П.П. | Помочь оптимизировать запрос.
18.01 | Иванов И.И. | Написать статью на Хабр: JOIN с учетом распределения данных.
16.01 | Петров П.П. | Помочь оптимизировать запрос.
U apstraktnom svijetu, autori zadataka bi trebali biti ravnomjerno raspoređeni među svim zaposlenima naše organizacije, ali u stvarnosti zadaci dolaze, po pravilu, od prilično ograničenog broja ljudi - “od menadžmenta” naviše u hijerarhiji ili “od podizvođača” iz susjednih odjela (analitičari, dizajneri, marketing,...).
Prihvatimo da u našoj organizaciji od 1000 ljudi samo 20 autora (obično i manje) postavlja zadatke za svakog konkretnog izvođača i da ubrzate "tradicionalni" upit.
Generator skripti
-- сотрудники
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);
-- задачи с указанным распределением
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žimo zadnjih 100 zadataka za određenog izvršioca:
SELECT
task.*
, person.name
FROM
task
LEFT JOIN
person
ON person.id = task.author_id
WHERE
owner_id = 777
ORDER BY
task_date DESC
LIMIT 100;
Ispostavilo se da je to 1/3 ukupnog vremena i 3/4 očitavanja stranice podataka napravljene su samo za traženje autora 100 puta - za svaki izlazni zadatak. Ali znamo da je među ovim stotinama samo 20 različitih - Da li je moguće koristiti ovo znanje?
hstore-dictionary
Hajde da iskoristimo prednost za generiranje "rječnika" ključ/vrijednost:
CREATE EXTENSION hstoreSamo treba da unesemo ID autora i njegovo ime u rečnik kako bismo mogli da izdvojimo pomoću ovog ključa:
-- формируем целевую выборку
WITH T AS (
SELECT
*
FROM
task
WHERE
owner_id = 777
ORDER BY
task_date DESC
LIMIT 100
)
-- формируем словарь для уникальных значений
, dict AS (
SELECT
hstore( -- hstore(keys::text[], values::text[])
array_agg(id)::text[]
, array_agg(name)::text[]
)
FROM
person
WHERE
id = ANY(ARRAY(
SELECT DISTINCT
author_id
FROM
T
))
)
-- получаем связанные значения словаря
SELECT
*
, (TABLE dict) -> author_id::text -- hstore -> key
FROM
T;
Potrošeno na dobijanje informacija o osobama 2 puta manje vremena i 7 puta manje čitanih podataka! Pored „rečnika“, ono što nam je takođe pomoglo da postignemo ove rezultate je masovno preuzimanje zapisa sa stola u jednom prolazu koristeći = ANY(ARRAY(...)).
Unosi u tablicu: serijalizacija i deserijalizacija
Ali šta ako treba da sačuvamo ne samo jedno tekstualno polje, već ceo unos u rečnik? U ovom slučaju, PostgreSQL-ova sposobnost će nam pomoći tretirati unos tabele kao jednu vrijednost:
...
, dict AS (
SELECT
hstore(
array_agg(id)::text[]
, array_agg(p)::text[] -- магия #1
)
FROM
person p
WHERE
...
)
SELECT
*
, (((TABLE dict) -> author_id::text)::person).* -- магия #2
FROM
T;Pogledajmo šta se ovde dešavalo:
- Smo uzeli p kao pseudonim za pun unos u tablicu osoba i sastavio ih niz.
- ovo niz snimaka je prerađen na niz tekstualnih stringova (person[]::text[]) da ga smjestite u hstore rječnik kao niz vrijednosti.
- Kada primimo povezanu evidenciju, mi izvučen iz rječnika po ključu kao tekstualni niz.
- Treba nam tekst pretvoriti u vrijednost tipa tablice osoba (za svaku tabelu automatski se kreira tip istog imena).
- “Proširite” upisani zapis u kolone koristeći
(...).*.
json rječnik
Ali takav trik kakav smo primijenili gore neće raditi ako ne postoji odgovarajući tip tablice za "casting". Potpuno ista situacija će se pojaviti, i ako pokušamo koristiti CTE red, a ne "prava" tabela.
U ovom slučaju oni će nam pomoći :
...
, p AS ( -- это уже CTE
SELECT
*
FROM
person
WHERE
...
)
, dict AS (
SELECT
json_object( -- теперь это уже json
array_agg(id)::text[]
, array_agg(row_to_json(p))::text[] -- и внутри json для каждой строки
)
FROM
p
)
SELECT
*
FROM
T
, LATERAL(
SELECT
*
FROM
json_to_record(
((TABLE dict) ->> author_id::text)::json -- извлекли из словаря как json
) AS j(name text, birth_date date) -- заполнили нужную нам структуру
) j;
Treba napomenuti da prilikom opisivanja ciljne strukture ne možemo navesti sva polja izvornog niza, već samo ona koja su nam zaista potrebna. Ako imamo „nativnu“ tabelu, onda je bolje koristiti funkciju json_populate_record.
I dalje jednom pristupamo rječniku, ali json-[de]troškovi serijalizacije su prilično visoki, stoga je razumno koristiti ovu metodu samo u nekim slučajevima kada se „pošteno“ CTE skeniranje pokaže lošije.
Testiranje performansi
Dakle, imamo dva načina za serijalizaciju podataka u rečnik − hstore/json_object. Osim toga, sami nizovi ključeva i vrijednosti mogu se generirati na dva načina, internom ili eksternom konverzijom u tekst: array_agg(i::text) / array_agg(i)::text[].
Provjerimo efikasnost različitih tipova serijalizacije koristeći čisto sintetički primjer - serijalizirati različite brojeve ključeva:
WITH dict AS (
SELECT
hstore(
array_agg(i::text)
, array_agg(i::text)
)
FROM
generate_series(1, ...) i
)
TABLE dict;Skripta evaluacije: serijalizacija
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, do otprilike veličine rječnika od 2^12 ključeva serijalizacija u json traje manje vremena. U ovom slučaju, najefikasnija je kombinacija json_object i "interne" konverzije tipa array_agg(i::text).
Pokušajmo sada pročitati vrijednost svakog ključa 8 puta - na kraju krajeva, ako ne pristupite rječniku, zašto je onda potreban?
Skripta za evaluaciju: čitanje iz rječnika
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
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... već otprilike sa 2^6 ključeva, čitanje iz json rječnika počinje gubiti više puta čitanje iz hstore-a, za jsonb isto se dešava na 2^9.
Konačni zaključci:
- ako treba da to uradite JOIN sa višestrukim zapisima koji se ponavljaju — bolje je koristiti „rečnik“ tabele
- ako se očekuje vaš rječnik mali i nećete puno čitati iz njega - možete koristiti json[b]
- u svim ostalim slučajevima hstore + array_agg(i::text) će biti efikasnije
izvor: www.habr.com