Nous poursuivons la série d'articles consacrés à l'étude de moyens peu connus pour améliorer les performances des requêtes PostgreSQL « en apparence simples » :
Ne pensez pas que je n'aime pas tellement JOIN... :)
Mais souvent sans cela, la demande s'avère nettement plus productive qu'avec. Alors aujourd'hui, nous allons essayer se débarrasser des JOIN gourmands en ressources - à l'aide d'un dictionnaire.
À partir de PostgreSQL 12, certaines des situations décrites ci-dessous peuvent être reproduites légèrement différemment en raison de . Ce comportement peut être inversé en spécifiant la clé
MATERIALIZED.
Beaucoup de « faits » dans un vocabulaire limité
Prenons une tâche d'application très réelle : nous devons afficher une liste ou tâches actives avec les expéditeurs :
25.01 | Иванов И.И. | Подготовить описание нового алгоритма.
22.01 | Иванов И.И. | Написать статью на Хабр: жизнь без JOIN.
20.01 | Петров П.П. | Помочь оптимизировать запрос.
18.01 | Иванов И.И. | Написать статью на Хабр: JOIN с учетом распределения данных.
16.01 | Петров П.П. | Помочь оптимизировать запрос.
Dans le monde abstrait, les auteurs de tâches devraient être répartis équitablement entre tous les employés de notre organisation, mais en réalité les tâches émanent, en règle générale, d'un nombre assez limité de personnes - « du management » remontant à la hiérarchie ou « des sous-traitants » des services voisins (analystes, designers, marketing, ...).
Acceptons que dans notre organisation de 1000 personnes, seuls 20 auteurs (généralement encore moins) fixent des tâches pour chaque interprète spécifique et pour accélérer la requête "traditionnelle".
Générateur de scripts
-- сотрудники
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);
Montrons les 100 dernières tâches pour un exécuteur spécifique :
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;
Il s'avère que 1/3 du temps total et 3/4 des lectures des pages de données ont été créées uniquement pour rechercher l'auteur 100 fois - pour chaque tâche de sortie. Mais nous savons que parmi ces centaines seulement 20 différents - Est-il possible d'utiliser ces connaissances ?
dictionnaire hstore
utilisons pour générer une valeur-clé « dictionnaire » :
CREATE EXTENSION hstoreIl suffit de mettre l’identifiant de l’auteur et son nom dans le dictionnaire pour pouvoir ensuite extraire à l’aide de cette clé :
-- формируем целевую выборку
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;
Dépensé pour obtenir des informations sur des personnes 2 fois moins de temps et 7 fois moins de données lues! En plus du « vocabulaire », ce qui nous a également aidé à atteindre ces résultats était récupération groupée d'enregistrements de la table en un seul passage en utilisant = ANY(ARRAY(...)).
Entrées de table : sérialisation et désérialisation
Mais que se passe-t-il si nous devons sauvegarder non seulement un champ de texte, mais une entrée entière dans le dictionnaire ? Dans ce cas, la capacité de PostgreSQL nous aidera traiter une entrée de table comme une valeur unique:
...
, 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;Regardons ce qui se passait ici :
- Nous avons pris p comme alias de l'entrée de la table des personnes complètes et en a rassemblé une série.
- cette la gamme d'enregistrements a été refondue à un tableau de chaînes de texte (person[]::text[]) pour le placer dans le dictionnaire hstore sous forme de tableau de valeurs.
- Lorsque nous recevons un enregistrement connexe, nous extrait du dictionnaire par clé comme une chaîne de texte.
- Nous avons besoin de texte se transformer en valeur de type table personne (pour chaque table un type du même nom est automatiquement créé).
- « Développez » l'enregistrement saisi en colonnes en utilisant
(...).*.
dictionnaire json
Mais une astuce telle que celle que nous avons appliquée ci-dessus ne fonctionnera pas s'il n'y a pas de type de table correspondant pour effectuer le « casting ». Exactement la même situation se présentera, et si nous essayons d'utiliser une ligne CTE, pas une "vraie" table.
Dans ce cas, ils nous aideront :
...
, 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;
Il est à noter que lors de la description de la structure cible, on ne peut pas lister tous les champs de la chaîne source, mais uniquement ceux dont nous avons réellement besoin. Si nous avons une table « native », alors il vaut mieux utiliser la fonction json_populate_record.
Nous accédons toujours au dictionnaire une fois, mais json-[de]les coûts de sérialisation sont assez élevés, par conséquent, il est raisonnable d'utiliser cette méthode uniquement dans certains cas où le CTE Scan « honnête » se révèle pire.
Performances des tests
Nous avons donc deux façons de sérialiser les données dans un dictionnaire - hstore/json_object. De plus, les tableaux de clés et de valeurs eux-mêmes peuvent également être générés de deux manières, avec conversion interne ou externe en texte : array_agg(i::text) / array_agg(i)::text[].
Vérifions l'efficacité des différents types de sérialisation à l'aide d'un exemple purement synthétique - sérialiser différents nombres de clés:
WITH dict AS (
SELECT
hstore(
array_agg(i::text)
, array_agg(i::text)
)
FROM
generate_series(1, ...) i
)
TABLE dict;Script d'évaluation : sérialisation
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;
Sur PostgreSQL 11, jusqu'à environ une taille de dictionnaire de 2 ^ 12 clés la sérialisation vers json prend moins de temps. Dans ce cas, le plus efficace est la combinaison de json_object et de conversion de type « interne » array_agg(i::text).
Essayons maintenant de lire la valeur de chaque clé 8 fois - après tout, si vous n'accédez pas au dictionnaire, alors pourquoi est-il nécessaire ?
Script d'évaluation : lecture d'un dictionnaire
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;
Et... déjà environ avec 2 ^ 6 clés, la lecture d'un dictionnaire json commence à perdre plusieurs fois en lisant depuis hstore, pour jsonb, la même chose se produit à 2 ^ 9.
Conclusions finales :
- si tu dois le faire REJOIGNEZ avec plusieurs enregistrements répétitifs — il vaut mieux utiliser le « dictionnaire » du tableau
- si votre dictionnaire est attendu petit et vous n'en lirez pas grand chose - vous pouvez utiliser json[b]
- dans tous les autres cas hstore + array_agg(i::text) sera plus efficace
Source: habr.com