Discussion :

[yoandm]

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.

[SQLpro]

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 +

[alassanediakite]

Salut

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.

@+

[SQLpro]

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 +

[alassanediakite]

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

...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.
@+

[ced]

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.

[alassanediakite]

Salut 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

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

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

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).
@+

[SQLpro]

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

Déjà ceci ne fonctionne pas :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 +