PostgreSQL Antipatterns: hit hit on heavy JOIN

Nous continuons une série d'articles consacrés à l'étude de moyens peu connus d'améliorer les performances des requêtes PostgreSQL "apparemment simples":

• record rare atteindra le milieu de JOIN
• sisyphus rejoindre des tableaux
• JOIN et OR nuisibles
• CTE REJOIGNEZ CTE

Ne pensez pas que je n'aime pas tellement JOIN ... :)

Mais souvent sans elle, la demande est nettement plus productive qu'avec lui. Par conséquent, aujourd'hui, nous essaierons de nous débarrasser complètement du JOIN gourmand en ressources - à l'aide d'un dictionnaire.

À partir de PostgreSQL 12, certaines des situations décrites ci-dessous peuvent être lues un peu différemment en raison de la non-matérialisation de CTE par défaut . Ce comportement peut être inversé à l'aide de la clé MATERIALIZED .

Beaucoup de "faits" sur un vocabulaire limité

Prenons une application très réelle - vous devez répertorier les messages entrants ou les 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 également entre tous les employés de notre organisation, mais en réalité, les tâches proviennent, en règle générale, d'un nombre assez limité de personnes - «des supérieurs hiérarchiques» ou «des alliés» des départements voisins (analystes, concepteurs marketing ...).

Supposons que dans notre organisation de 1000 personnes, seulement 20 auteurs (généralement encore moins) définissent des tâches pour chaque artiste spécifique et utilisent cette connaissance du sujet pour accélérer la demande «traditionnelle».


Nous montrons les 100 dernières tâches pour un artiste 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; 

[regardez expliquez.tensor.ru]

Il s'avère que 1/3 du temps entier et 3/4 des lectures des pages de données ont été faites uniquement afin de rechercher l'auteur 100 fois - pour chaque tâche affichée. Mais nous savons que parmi cette centaine, il n'y en a que 20 différents - est-il possible d'utiliser ces connaissances?

dictionnaire hstore

Nous utilisons le type hstore pour générer un "dictionnaire" de valeurs-clés:

 CREATE EXTENSION hstore 

Il nous suffit de mettre l'ID de l'auteur et son nom dans le dictionnaire, pour que plus tard nous puissions 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; 

[regardez expliquez.tensor.ru]

Il a fallu 2 fois moins de temps pour obtenir des informations sur les personnes et 7 fois moins de données lues ! En plus de la «tromperie», ces résultats nous ont aidés à réaliser l' extraction de masse des 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 enregistrer dans le dictionnaire non pas un champ de texte, mais un enregistrement entier? Dans ce cas, la capacité de PostgreSQL à travailler avec l'écriture d'une table en tant que valeur unique nous aidera:

 ... , 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; 

Voyons ce qui s'est passé ici:

1. Nous avons pris p comme alias pour l'enregistrement complet de la table person et assemblé un tableau à partir d'eux.
2. Ce tableau d'entrées a été refondu en un tableau de chaînes de texte (person [] :: text []) pour le placer dans le dictionnaire hstore en tant que tableau de valeurs.
3. Lors de la réception de l'enregistrement lié, nous l'avons retiré du dictionnaire par clé sous forme de chaîne de texte.
4. Nous devons transformer le texte en valeur du type de la table personne (pour chaque table, le type du même nom est automatiquement créé).
5. «Déployé» un enregistrement tapé dans des colonnes à l'aide de (...).* .

dictionnaire json

Mais une telle astuce, comme nous l'avons appliqué ci-dessus, ne fonctionnera pas s'il n'y a pas de type de table correspondant pour faire un "détachement". Exactement, la même situation se produira et si, en tant que source de données pour la sérialisation, nous essayons d'utiliser la ligne CTE, et non la «vraie» table .

Dans ce cas, les fonctions de travail avec json 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 convient de noter que lors de la description de la structure cible, nous ne pouvons pas répertorier tous les champs de la chaîne source, mais uniquement ceux dont nous avons vraiment besoin. Si nous avons une table "native", alors il vaut mieux utiliser la fonction json_populate_record .

Nous avons toujours accès au dictionnaire une fois, mais les coûts de sérialisation json- [de] sont assez élevés , il est donc raisonnable de n'utiliser cette méthode que dans certains cas lorsque le scan CTE «honnête» se montre pire.

Test des performances

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 peuvent également être générés de deux manières, avec une conversion interne ou externe en texte: array_agg (i :: text) / array_agg (i) :: text [] .

Vérifions l'efficacité de différents types de sérialisation à l'aide d'un exemple purement synthétique - nous sérialisons un nombre différent de clés :

 WITH dict AS ( SELECT hstore( array_agg(i::text) , array_agg(i::text) ) FROM generate_series(1, ...) i ) TABLE dict; 

Sur PostgreSQL 11, jusqu'à une taille de dictionnaire d'environ 2 ^ 12 clés, la sérialisation en json prend moins de temps . La combinaison de json_object et de la conversion de type "interne" de array_agg(i::text) est la plus efficace.

Essayons maintenant de lire la valeur de chaque clé 8 fois - car si vous n'accédez pas au dictionnaire, pourquoi est-il nécessaire?




Et ... déjà à environ 2 ^ 6 touches, la lecture du dictionnaire json commence à perdre à la lecture de hstore plusieurs fois, pour jsonb la même chose se produit à 2 ^ 9.

Conclusions finales:

• si vous avez besoin de faire un JOIN avec des enregistrements répétés à plusieurs reprises - il est préférable d'utiliser la "correspondance de table"
• si votre dictionnaire est petit et que vous en lirez un peu , vous pouvez utiliser json [b]
• dans tous les autres cas, hstore + array_agg (i :: text) sera plus efficace