Продължаваме поредицата от статии, посветени на изследването на малко известни начини за подобряване на производителността на "уж простите" заявки в PostgreSQL:
Не мислете, че не обичам JOIN толкова много… 🙂
Но често без него заявката е значително по-производителна, отколкото с него. Затова днес ще опитаме да се избавим от ресурсните JOIN — с помощта на речник.
Започвайки с PostgreSQL 12, част от описаните по-долу ситуации може да се възпроизведат малко по-различно заради . Това поведение може да бъде върнато на предишното, като се посочи ключ
MATERIALIZED.
Много "факти" по ограничен речник
Нека да вземем напълно реална практически задача — трябва да изведем списък или активни задачи с изпратители:
25.01 | Иванов И.И. | Подгответе описанието на новия алгоритъм.
22.01 | Иванов И.И. | Напишете статия на Хабр: живот без JOIN.
20.01 | Петров П.П. | Помогнете да се оптимизира заявката.
18.01 | Иванов И.И. | Напишете статия на Хабр: JOIN с оглед на разпределението на данните.
16.01 | Петров П.П. | Помогнете да се оптимизира заявката.
В абстрактния свят авторите на задачите би трябвало да се разпределят равномерно между всички служители в нашата организация, но в реалността задачите идват, като правило, от достатъчно ограничен брой хора — "от ръководството" нагоре по иерархията или "от съседите" от съседни отдели (анализатори, дизайнери, маркетинг, …).
Нека приемем, че в нашата организация от 1000 души само 20 автори (обикновено дори по-малко) поставят задачи на всеки конкретен изпълнител и , за да ускорим "традиционната" заявка.
Скрипт-генератор
-- служители
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);
Нека покажем последните 100 задачи за конкретен изпълнител:
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; 
Излиза, че 1/3 от общото време и 3/4 от прочетените данни страници с данни бяха направени само за да се търси автора 100 пъти — за всяка показвана задача. Но знаем, че сред тези сто има само 20 различни — не можем ли да използваме това знание?
hstore-словарь
Нека изпробваме за генериране на «словар» ключ-стойност:
CREATE EXTENSION hstoreВ словаря ни е достатъчно да поставим ID на автора и неговото име, за да можем после да извлечем по този ключ:
-- формируем целевата извадка
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; 
За получаване на информация за лицата е изразходвано в 2 пъти по-малко време и в 7 пъти по-малко прочетени данни! Освен «ословаряването», тези резултати ни помогнаха да постигнем и масово извличане на записи от таблицата с един проход с помощта на = ANY(ARRAY(...)).
Записите на таблицата: сериализация и десериализация
Но какво да правим, ако трябва да запазим в словаря не едно текстово поле, а цяло запис? В този случай ни помага способността на PostgreSQL да работи с записа на таблицата като с едно единствено значение:
...
, 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;Нека разгледаме какво точно се случва тук:
- Взехме p като псевдоним за пълния запис на таблицата person и събрахме от тях масив.
- Тази маса записи беше каствана в масив от текстови низове (person[]::text[]), за да бъде поставена в hstore речника като масив от стойности.
- При извличане на свързана запись, ние я извлекохме от речника по ключа като текстов низ.
- Текста трябва да превърнем в стойност от тип таблица person (за всяка таблица автоматично се създава съответстващ тип).
- Ние „разгръщаме“ типизирана запись в колони с помощта на
(...).*.
json речник
Но подобен номер, какъвто приложихме по-горе, няма да проработи, ако няма съответстващ табличен тип, за да направим „кастоване“. Същата ситуация ще се получи и ако опитаме да използваме стринг CTE, а не „истинска“ таблица..
В този случай ще ни помогнат :
...\n, p AS ( -- това вече е CTE\n SELECT\n *\n FROM\n person\n WHERE\n ...\n)\n, dict AS (\n SELECT\n json_object( -- сега вече е json\n array_agg(id)::text[]\n , array_agg(row_to_json(p))::text[] -- и вътре в json за всеки ред\n )\n FROM\n p\n)\nSELECT\n *\nFROM\n T\n, LATERAL(\n SELECT\n *\n FROM\n json_to_record(\n ((TABLE dict) ->> author_id::text)::json -- извлекли сме от речника като json\n ) AS j(name text, birth_date date) -- запълнили сме необходимата структура\n ) j; Важно е да отбележим, че при описването на целевата структура можем да изброим не всички полета от изходния низ, а само тези, които наистина са ни нужни. Ако имаме „родна“ таблица, по-добре е да използваме функцията json_populate_record.
Достъпът до речника се извършва по-скоро веднъж, но разходите за json-[де]сериализация са значителни, така че този метод е разумно да се използва само в определени случаи, когато „честният“ CTE скан показва по-лоши резултати.
Тестваме производителността
И така, получихме два начина за сериализация на данни в речник — hstore / json_object. Освен това самите масиви от ключове и стойности могат да бъдат генерирани също по два начина, с вътрешно или външно преобразуване в текст: array_agg(i::text) / array_agg(i)::text[].
Нека проверим ефективността на различните видове сериализация на строго синтетичен пример — сериализираме различен брой ключове:
WITH dict AS (\n SELECT\n hstore(\n array_agg(i::text)\n , array_agg(i::text)\n )\n FROM\n generate_series(1, ...) i\n)\nTABLE dict;Оценъчен скрипт: сериализация
С T КАТО (
ИЗ
*
, (
ИЗ
(SELECT
regexp_replace(ea[array_length(ea, 1)], '^Execution Time: (d+.d+) ms$', '1')::real et
ОТ
(
ИЗ
array_agg(el) ea
ИЗ
dblink('port= ' || current_setting('port') || ' dbname=' || current_database(), $$
explain analyze
С T КАТО (
ИЗ
hstore(
array_agg(i::text)
, array_agg(i::text)
)
ИЗ
generate_series(1, $$ || (1 << v) || $$) i
)
ТАБЛИЦА dict
$$) T(el text)
) T
) et
ИЗ
generate_series(0, 19) v
, LATERAL generate_series(1, 7) i
ПОРЯДОК ПО
1, 2
)
ИЗБЕРИ
v
, avg(et)::numeric(32,3)
ИЗ
T
ГРУППИРАЙ ПО
1
ПОРЯДОК ПО
1; 
На PostgreSQL 11 примерно до размера словаря в 2^12 ключа сериализация в json изисква по-малко време. При това най-ефективна е комбинацията от json_object и "вътрешно" преобразуване на типове array_agg(i::text).
Сега нека опитаме да прочетем стойността на всеки ключ по 8 пъти — все пак, ако не се обръщаме към словаря, за какво ни е той?
Оценъчен скрипт: четене от словаря
С T КАТО (
ИЗ
*
, (
ИЗ
(SELECT
regexp_replace(ea[array_length(ea, 1)], '^Execution Time: (d+.d+) ms$', '1')::real et
ОТ
(
ИЗ
array_agg(el) ea
ИЗ
dblink('port= ' || current_setting('port') || ' dbname=' || current_database(), $$
explain analyze
С T КАТО (
ИЗ
json_object(
array_agg(i::text)
, array_agg(i::text)
)
ИЗ
generate_series(1, $$ || (1 < (i % ($$ || (1 << v) || $$) + 1)::text
ИЗ
generate_series(1, $$ || (1 << (v + 3)) || $$) i
$$) T(el text)
) T
) et
ИЗ
generate_series(0, 19) v
, LATERAL generate_series(1, 7) i
ПОРЯДОК ПО
1, 2
)
ИЗБЕРИ
v
, avg(et)::numeric(32,3)
ИЗ
T
ГРУППИРАЙ ПО
1
ПОРЯДОК ПО
1; 
И… вече приблизително при 2^6 ключа четенето от json-словника започва многократно да изостава четенето от hstore, при jsonb същото става при 2^9.
Итоговите изводи:
- ако трябва да направите JOIN с многократно повтарящи се записи — по-добре е да използвате "ословаряване" на таблицата
- ако вашият речник очаквано малък и четете от него малко — може да използвате json[b]
- във всички останали случаи hstore + array_agg(i::text) ще бъде по-ефективно
Източник: habr.com
