Ne vazhdojmë serinë tonë të artikujve që eksplorojnë mënyra pak të njohura për të përmirësuar performancën e pyetjeve në dukje të thjeshta në PostgreSQL:
Mos mendo se nuk më pëlqen aq shumë JOIN... 🙂
Por shpesh, pa të, pyetja rezulton të jetë dukshëm më produktive sesa me të. Pra, sot do ta provojmë plotësisht. hiqni qafe JOIN-in që kërkon shumë burime — duke përdorur një fjalor.
Duke filluar me PostgreSQL 12, disa nga situatat e përshkruara më poshtë mund të riprodhohen paksa ndryshe për shkak të Kjo sjellje mund të rikthehet në sjelljen e mëparshme duke specifikuar një çelës
MATERIALIZED.
Shumë "fakte" në një fjalor të kufizuar
Le të marrim një problem shumë të vërtetë të aplikuar - duhet të nxjerrim një listë ose detyra aktive me dërgues:
25.01 | Иванов И.И. | Подготовить описание нового алгоритма.
22.01 | Иванов И.И. | Написать статью на Хабр: жизнь без JOIN.
20.01 | Петров П.П. | Помочь оптимизировать запрос.
18.01 | Иванов И.И. | Написать статью на Хабр: JOIN с учетом распределения данных.
16.01 | Петров П.П. | Помочь оптимизировать запрос.
Në një botë abstrakte, autorët e detyrave duhet të shpërndahen në mënyrë të barabartë midis të gjithë punonjësve të organizatës sonë, por në realitet Detyrat vijnë, si rregull, nga një numër mjaft i kufizuar njerëzish — “nga menaxhmenti” lart në hierarki ose “nga njerëz të lidhur” nga departamentet fqinje (analistë, dizajnerë, marketing, ...).
Le të supozojmë se në organizatën tonë prej 1000 personash, vetëm 20 autorë (zakonisht edhe më pak) caktojnë detyra për secilin interpretues specifik dhe për të shpejtuar pyetjen "tradicionale".
Gjenerator skriptesh
-- сотрудники
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);
Do të tregojmë 100 detyrat e fundit për një interpretues specifik:
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; 
Ajo rezulton se 1/3 e kohës totale dhe 3/4 e leximeve Faqet e të dhënave u krijuan vetëm për të kërkuar autorin 100 herë - për secilën detyrë të shfaqur. Por ne e dimë se midis këtyre qindra vetëm 20 të ndryshme — A është e mundur të përdoret kjo njohuri?
fjalor hstore
Le të përfitojmë për të gjeneruar një "fjalor" me çelës-vlerë:
CREATE EXTENSION hstoreNa duhet vetëm të vendosim ID-në dhe emrin e autorit në fjalor, në mënyrë që ta nxjerrim atë duke përdorur këtë çelës:
-- формируем целевую выборку
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; 
U desh kohë për të mbledhur informacion në lidhje me individët 2 herë më pak kohë dhe 7 herë më pak të dhëna të lexuaraPërveç "fjalorizimit", na ndihmuan edhe për të arritur këto rezultate nxjerrja e të dhënave në masë nga tabela me një kalim të vetëm duke përdorur = ANY(ARRAY(...)).
Hyrjet në tabelë: serializimi dhe deserializimi
Po sikur të na duhet të ruajmë jo vetëm një fushë teksti, por një regjistrim të tërë në fjalor? Në këtë rast, aftësia e PostgreSQL për të trajtoni një hyrje në tabelë si një vlerë të vetme:
...
, 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;Le të kuptojmë se çfarë po ndodhte këtu:
- Ne morëm p si një pseudonim për regjistrimin e plotë të tabelës së personave dhe mblodhi një grup prej tyre.
- kjo grupi i të dhënave u rimodelua në një varg vargjesh teksti (person[]::text[]) për ta vendosur atë në fjalorin hstore si një varg vlerash.
- Kur marrim një regjistrim përkatës, ne u nxor nga fjalori me anë të çelësit si një varg teksti.
- Na duhet teksti konverto në vlerën e tipit tabelë person (për secilën tabelë krijohet automatikisht një lloj me të njëjtin emër).
- "Zgjerova" regjistrimin e shtypur në kolona duke përdorur
(...).*.
fjalor json
Por truku që përdorëm më sipër nuk do të funksionojë nëse nuk ka një lloj tabele përkatëse për të kryer "çaktivizimin". Pikërisht e njëjta situatë do të lindë nëse përpiqemi të përdorim [të dhënat] si burim të dhënash për serializim. Rreshti CTE, jo tabela "e vërtetë".
Në këtë rast, ata do të na ndihmojnë :
...
, 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; Vlen të përmendet se kur përshkruajmë strukturën e synuar, nuk kemi nevojë të rendisim të gjitha fushat në rreshtin burimor, por vetëm ato që na nevojiten vërtet. Nëse kemi një tabelë vendase, është më mirë të përdorim funksionin json_populate_record.
Ne ende kemi qasje një herëshe në fjalor, por Kostoja e JSON-[de]serializimit është mjaft e lartë, kështu që ka kuptim ta përdorim këtë metodë vetëm në disa raste kur skanimi "i ndershëm" CTE ka performancë më të keqe.
Testimi i performancës
Pra, kemi dy mënyra për të serializuar të dhënat në një fjalor - hstore/json_objectPërveç kësaj, vargjet e çelësave dhe vlerave vetë mund të gjenerohen gjithashtu në dy mënyra, me konvertim të brendshëm ose të jashtëm në tekst: array_agg(i::tekst) / array_agg(i)::tekst[].
Le të testojmë efektivitetin e llojeve të ndryshme të serializimit duke përdorur një shembull thjesht sintetik - serializoni një numër të ndryshëm çelësash:
WITH dict AS (
SELECT
hstore(
array_agg(i::text)
, array_agg(i::text)
)
FROM
generate_series(1, ...) i
)
TABLE dict;Skripti i Vlerësimit: Serializimi
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; 
Në PostgreSQL 11, deri në një madhësi fjalori prej afërsisht 2^12 çelësash Serializimi në JSON kërkon më pak kohëNë këtë rast, kombinimi më efektiv është json_object dhe konvertimi i tipit "i brendshëm". array_agg(i::text).
Tani le të përpiqemi ta lexojmë kuptimin e secilit çelës 8 herë - në fund të fundit, nëse nuk konsultoheni me një fjalor, atëherë çfarë kuptimi ka kjo?
Skript vlerësimi: lexim nga një fjalor
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; 
Dhe... tashmë afërsisht me 2^6 çelësa, leximi nga një fjalor json fillon të humbasë shumëfisha Duke lexuar nga hstore, për jsonb e njëjta gjë ndodh në 2^9.
Përfundimet përfundimtare:
- nëse duhet të bëhet JOIN me të dhëna të shumëfishta të dyfishta — është më mirë të përdoret "fjalori" i tabelës
- nëse fjalori juaj pritet Është e vogël dhe nuk do të mund të lexosh shumë prej saj — mund të përdorni json[b]
- në të gjitha rastet e tjera hstore + array_agg(i::text) do të jetë më efektive
Burimi: www.habr.com
