Jointure ou jsonb indexé

La question des jointures et la performance revient souvent.
Toutefois j'ai une question particulière pour laquelle j'ai du mal à trouver un retour d'expérience.

Prenons l'exemple d'une table contenant des informations sur de nombreux produits.
Ces produits disposent d'infos de base identiques pour tous les produits. Ce sont donc des colonnes de la table.
Ces produits peuvent aussi avoir des attributs supplémentaires très variables.

Pour stocker ces attributs, on a plusieurs possibilités :
- utiliser une table d'attributs liées à la table produit
- utiliser un champ d'attributs jsonb en tant que colonne de la table produit

Ma question concerne les recherches au sein de la table produit sur un nombre très grands de produits et avec environ 50 attributs par produit.
Imaginons que l'on souhaite trouver tous les produits ayant l'attribut couleur à la valeur rouge.

Aura-t-on de meilleures performances avec une architecture utilisant 2 tables ou avec une seule table (et un index GIN sur la colonne des attributs jsonb) ?
Et si différence il y a, quelle est son ordre de grandeur ?

Merci par avance.

Il y a différentes solutions pour différents cas.
Les différents cas sont liés à la fréquences des mises (INSERT, UPDATE, DELETE) à jour et/ou des recherches (SELECT et dans une moindre mesure UPDATE et DELETE).

En gros il faut se rapprocher plutôt du relationnel si beaucoup de mise à jour.

Note que le format de stockage JSON ne présente aucun intérêt ni pour l'un, ni pour l'autre cas par rapport à du XML... En effet, le modèle DOM est natif en XML ce qui n'est pas le cas du JSON qu'il faut "convertir" en XML pour avoir une représentation DOM.

C'est pourquoi les bon SGBDR savent indexer du XML au niveau PATH, PROPERTY ou VALUE, mais ne proposent pas ce type de service au niveau du JSON...

Dans tous les cas, PostGreSQL ne sait ni indexer du XML ni du JSON, donc, toutes les recherches seront lentes...

A +

Salut

Citation:

---

Envoyé par SQLpro

Dans tous les cas, PostGreSQL ne sait ni indexer du XML ni du JSON, donc, toutes les recherches seront lentes...

---

On s'informe avant de raconter des mensonges.
PostgreSQL indexe bien le type json au format binaire (jsonb)[/URL], format d'ailleurs précisé dans la demande. On peut aussi indexer une expression XPATH pour le type XML.
Pour répondre à yoandm...

• A défaut de "retour d'expérience", Il faut faire vos propres essais pour conclure. (En faire un article si possible;))
• Pour l'option du json, il serait mieux d'avoir deux tables : produit (idp, ...), propriete(idp, prorpriete en json). Cela assouplira les traitements de la table produit.

@+

Citation:

---

Envoyé par alassanediakite

Salut

On s'informe avant de raconter des mensonges.
PostgreSQL indexe bien le type json au format binaire (jsonb), format d'ailleurs précisé dans la demande.

---

Désolé de te le dire aussi cruement, mais l'indexation GIN (utilisée aussi pour le XML) est une vaste merde. Comme elle forme un dictionnaire de paire clef valeur, elle permet juste de savoir si telle ou telle clef existe, mais pas de la situer efficacement (chemin) ni de faire l'inverse efficacement, c'est à dire retrouver la ou les clefs à partir d'une valeur...
Encore une fois il ne s'agit pas de dire il est possible de poser des index dans du JSON, mais que ces index servent à quelques chose en pratique dans l'exploitation...

C'est un peu comme MySQL qui dit qu'il fait tout... Mais comme il fait tout mal, ça sert à rien en pratique !

A +

Salut
S'il te plait, prend le temps de lire pour éviter les incohérences...

Citation:

---

Envoyé par SQLpro

...elle forme un dictionnaire de paire clef valeur, elle permet juste de savoir si telle ou telle clef existe, mais pas de ... retrouver la ou les clefs à partir d'une valeur

---

A quoi bon alors de stocker clé et valeur?
En ce qui concerne la recherche de position, je ne vois aucun intérêt avec le json ou le xml. Si tu veux tel degré de recherche utilise plutôt le full text.
@+

Citation:

---

Envoyé par SQLpro

mais l'indexation GIN (utilisée aussi pour le XML) est une vaste merde. Comme elle forme un dictionnaire de paire clef valeur, elle permet juste de savoir si telle ou telle clef existe, mais pas de la situer efficacement (chemin) ni de faire l'inverse efficacement, c'est à dire retrouver la ou les clefs à partir d'une valeur...

---

Personnellement, je ne l'aurais pas exprimé dans ces termes... Pas besoin d'être vulgaire...
Effectivement, l'indexation GIN indexe les clés, pas les valeurs.
Par contre, cette indexation ne se limite quand même pas seulement à savoir "si telle ou telle clef existe", mais également où elle se trouve.

Salut ced

Citation:

---

Envoyé par ced

Effectivement, l'indexation GIN indexe les clés, pas les valeurs.

---

Que doit-on comprendre alors dans la documentation paragraphe 8.14.4. Indexation jsonb :question:

Citation:

---

Les index GIN peuvent être utilisés pour chercher efficacement des clés ou paires clé/valeurs se trouvant parmi un grand nombre de documents (datums) jsonb. Deux « classes d'opérateur » GIN sont fournies, offrant différents compromis entre performance et flexibilité.

La classe d'opérateur GIN par défaut pour jsonb supporte les requêtes avec des opérateurs clé-existe au niveau haut ?, ?& et des opérateurs ?| et l'opérateur chemin/valeur-existe @>.

---

Plus loin dans le même paragraphe...

Citation:

---

Mais il faut noter qu'un tel index stockera des copies de chaque clé et chaque valeur de la colonne jdoc, alors que l'index sur expression de l'exemple précedent ne stockera que les données trouvées pour la clé tags. Alors que l'approche d'index simple est bien plus souple (puisqu'elle supporte les requêtes sur n'importe quelle clé), les index sur des expression ciblées ont bien plus de chances d'être plus petits et plus rapide pour la recherche qu'un simple index.

---

Si vous faites allusion au fonctionnement de GIN...

Citation:

---

GIN est l'acronyme de Generalized Inverted Index (ou index générique inverse). GIN est prévu pour traiter les cas où les items à indexer sont des valeurs composites, et où les requêtes devant être accélérées par l'index doivent rechercher des valeurs d'éléments apparaissant dans ces items composites. Par exemple, les items pourraient être des documents, et les requêtes pourraient être des recherches de documents contenant des mots spécifiques.

Nous utilisons le mot item pour désigner une valeur composite qui doit être indexée, et le mot clé pour désigner une valeur d'élément. GIN stocke et recherche toujours des clés, jamais des items eux même.

Un index GIN stocke un jeu de paires de (clé, posting list), où posting list est un jeu d'adresse d'enregistrement (row ID) où la clé existe. Le même row ID peut apparaître dans plusieurs posting lists, puisqu'un item peut contenir plus d'une clé. Chaque clé est stockée une seule fois, ce qui fait qu'un index GIN est très compact dans le cas où une clé apparaît de nombreuses fois.

---

A ma compréhension (parlant de jsonb), "item" de GIN désigne un document json et "clé" de GIN désigne une entrée "clé:valeur" OU une "clé" du document json selon le choix de l'opérateur de l'index (cf: explication ci-dessus).
@+

Citation:

---

Envoyé par ced

Personnellement, je ne l'aurais pas exprimé dans ces termes... Pas besoin d'être vulgaire...
Effectivement, l'indexation GIN indexe les clés, pas les valeurs.
Par contre, cette indexation ne se limite quand même pas seulement à savoir "si telle ou telle clef existe", mais également où elle se trouve.

---

CORRECTION :pas "ou elle se trouve", mais "ou elles se trouvent". En effet en cas de pluralité d'une balise, la recherche GIN en sus d'être moins efficace sortira toutes les occurrences.

Exeplique moi comment tu te tire de cette affaire :

Code:

---

1 2 3 4 5 6 7 8 9

CREATE TABLE T_J (K SERIAL PRIMARY KEY, J JSON);   INSERT INTO T_J (J) VALUES ('{ "rac": { "bal1": { "ballot": "rantanplan" }, "bal2": { "ballot": "lucky luke" } } }');

---

Extraire, avec utilisation de l'index, la valeur de la base "ballot" relative à bal1 => "rantanplan"

Code:

---

CREATE INDEX X_XML ON T_J USING gin (J jsonb_path_ops);

---

Déjà ceci ne fonctionne pas :

Code:

---

1 2 3

ERROR: ERREUR: la classe d'opérateur « jsonb_path_ops » n'accepte pas le type de données json SQL state: 42804

---

A +

Citation:

---

Envoyé par alassanediakite

Salut ced

Que doit-on comprendre alors dans la documentation paragraphe 8.14.4. Indexation jsonb :question:

Plus loin dans le même paragraphe...


Si vous faites allusion au fonctionnement de GIN...

A ma compréhension (parlant de jsonb), "item" de GIN désigne un document json et "clé" de GIN désigne une entrée "clé:valeur" OU une "clé" du document json selon le choix de l'opérateur de l'index (cf: explication ci-dessus).
@+

---

Donc on est bien d'accord, aucune recherche directe de valeur n'est possible.
Indexation de clef, ou paire clef valeur, mais pas d'indexation de valeur sans clef.
De la même manière une clef n'est pas un chemin.
Donc, aucune indexation de chemin n'est possible

Ce sont bien les limitations que je t’ai données... (pour le XML, le JSON revenant au même (XML <=> JSON).

Au lieu de te focaliser sur PostgreSQL, regarde ce que font les autres SGBDR en la matière...
Par exemple :
dans Oracle tu peux indexer les chemins, les positions hiérarchiques des balises et les valeurs
http://www.ksi.mff.cuni.cz/~svoboda/...-Oracle-DB.pdf
Dans SQL Server, tu peux indexer les positions hiérarchiques des nœuds avec leur type (index XML primaire), les chemins, les valeurs et les propriétés (soit 4 types d'index)
https://docs.microsoft.com/fr-fr/sql...xes-sql-server

Si tu devais indexer tous les chemins dans PostGreSQL tu devrais connaître les chemins à l'avance et s'il y a 1000 chemins, créer mille index sur fonction... !!!!

C'est donc stupidement inefficace

Et dans le cas de notre ami, l'absence d'index sur valeur rend toute jointure avec une valeur exprimée sous forme JSN d'une extrême inefficacité.

A +

Salut

Citation:

---

Envoyé par SQLpro

Donc on est bien d'accord, aucune recherche directe de valeur n'est possible.

---

J'ai vraiment de la peine à imaginer une requête cherchant une VALEUR dans le json ou le xml sans préciser la CLE ou le CHEMIN.
Le reste du message n'a pas plus d’intérêt.
Un petit demo...
J'ai créé une table avec du jsonb...

Code:

---

1 2 3 4 5 6

CREATE TABLE pjsonb ( idp integer NOT NULL, prenomp character varying(30), nomp character varying(30), parent jsonb );

---

et insérer des données de cette manière...

Code:

---

INSERT INTO pjsonb (idp, prenomp, nomp, parent) VALUES (x, 'xx', 'yyy', '{"mere": {"nom": "iii", "prenom": "uuu"}, "pere": {"nom": "ooo", "prenom": "eee"}}');

---

plus un index

Code:

---

CREATE INDEX idxgin ON personne.pjsonb USING gin (parent);

---

Ci-joint le dump de 2615 lignes:oops:Pièce jointe 371105
Tu peux faire tes essaies avec les requêtes du genre...

Code:

---

explain analyse select * from pjsonb where parent @>'{"mere":{"prenom":"Aminata"}}'::jsonb

---

et comparer les performances.
@+

Citation:

---

Envoyé par alassanediakite

Salut

J'ai vraiment de la peine à imaginer une requête cherchant une VALEUR dans le json ou le xml sans préciser la CLE ou le CHEMIN.

---

La plupart du temps, c'est justement pour le retrouver !!!!

Un petit exemple simple dans SQL Server...
Microsoft SQL Server constitue les plans d'exécution de ses requêtes sous forme XML. Un petit exemple :
https://tomaztsql.files.wordpress.co...ng?w=618&h=417
Un tel plan peut faire plusieurs centaines de milliers de balises...
SQL Server conserve des dizaines de milliers de plan en mémoire pour les réutiliser mais aussi pour les diagnostics post exploitation...
Un diagnostic très intéressant est de retrouver tous les plans contenant un "warning" c'est à dire une condition de génération du plan qui informe d'un problème potentiel...
Associé à un module nommé Query Store qui analyse en permanence ces plans et surtout leur réutilisation au fil du temps, ces extractions permettent d'améliorer sensiblement la détection des requêtes mal écrites...

Pense tu que cela ne serve à rien ???

A +

Bonjour,

J'étais en vacances et je viens prendre connaissance de vos réponses.
Déjà, merci pour ces retours que je vais analyser avec soin.

En revanche, je ne pensais pas générer autant d'échanges musclés avec cette question :-)

Encore merci.

J'avoue que j'espérais beaucoup de cette combinaison JSONB et GIN après les quelques lectures d'articles à ce sujet.
Que pensez-vous de cet article : https://blog.2ndquadrant.com/jsonb-t...ostgresql-9-4/ ?

Merci à vous.

Citation:

---

Envoyé par yoandm

J'avoue que j'espérais beaucoup de cette combinaison JSONB et GIN après les quelques lectures d'articles à ce sujet.
Que pensez-vous de cet article : https://blog.2ndquadrant.com/jsonb-t...ostgresql-9-4/ ?

Merci à vous.

---

L'article que vous mentionnez est intéressant... Mais la boite qui l'a publié "second quandrant" est spécialisé sur PostgreSQL et ne travaille que sur ce moteur. Un peu comme si vous demandiez à un garagiste qui ne vend que des Renault, si les performances des Renault dont bonnes....
De plus le volume des données indiqué : "209MB and contains approximately 600k" tient en RAM même sur un smartphone.... Qu'en sera t-il avec une base de plusieurs Go ?
Pour info, 1,1 s pour ce traitement sans index est totalement inacceptable. Et 0.167ms une foi indexé aussi !
Aucune comparaison n'est faite avec la solution non JSON....
Pour moi cet article est à mettre à la poubelle !

A +

Citation:

---

Un peu comme si vous demandiez à un garagiste qui ne vend que des Renault, si les performances des Renault dont bonnes....
Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.

---

Sans commentaire.

Citation:

---

Pour info, 1,1 s pour ce traitement sans index est totalement inacceptable.

---

En anglais, 1.100ms signifie 1,1 ms et non 1,1 s.

Revenons au besoin exprimé...

Citation:

---

Prenons l'exemple d'une table contenant des informations sur de nombreux produits.
Ces produits disposent d'infos de base identiques pour tous les produits. Ce sont donc des colonnes de la table.
Ces produits peuvent aussi avoir des attributs supplémentaires très variables.

Pour stocker ces attributs, on a plusieurs possibilités :
- utiliser une table d'attributs liées à la table produit
- utiliser un champ d'attributs jsonb en tant que colonne de la table produit

Ma question concerne les recherches au sein de la table produit sur un nombre très grands de produits et avec environ 50 attributs par produit.

---

Règle de gestion :
Un produit peut avoir plusieurs attributs et un attribut peut s'appliquer à plusieurs produits.

MCD :
Produit -0,n----avoir----0,n- Attribut

Tables :
te_produit_prd (prd_id, [autres colonnes communes à tous les produits]...)
te_attribut_att (att_id, att_code, att_nom...)
tj_prd_avoir_att_paa (paa_id_produit, paa_id_attribut, paa_valeur...)

Citation:

---

Imaginons que l'on souhaite trouver tous les produits ayant l'attribut couleur à la valeur rouge.

---

Code:

---

1 2 3 4 5 6

SELECT p.prd_id, p.prd_nom, p.prd_reference FROM te_produit_prd p INNER JOIN tj_prd_avoir_att_paa pa ON pa.paa_id_produit = p.prd_id INNER JOIN te_attribut_att a ON a.att_id = pa.paa_id_attribut WHERE a.att_nom = 'couleur' AND pa.paa_valeur = 'rouge'

---

La jointure étant une opération très optimisée par les SGBD, cette requête devrait s'exécuter très rapidement, même avec plusieurs millions de lignes dans les tables.