Les index GIN JSONB de PostgreSQL peuvent réduire le périmètre des recherches JSONPath à l’aide de tests d’égalité extractibles entre un chemin et une constante. Les comparaisons de plage, les inégalités et les simples tests d’existence d’un chemin ne fournissent pas à eux seuls ces clés de recherche. Les prédicats mixtes nécessitent un examen plus attentif.

Ce guide aide un développeur travaillant dans Terminal sur Mac à choisir un index pour filtrer des lignes d’articles synthétiques, à transmettre la requête via node-postgres et à examiner le plan obtenu. La règle d’extraction principale est documentée dans la référence sur l’indexation JSONB de PostgreSQL.

Quelles formes de prédicats fournissent des clés de recherche GIN utiles ?

Pour @? et @@, PostgreSQL extrait les clauses d’égalité qui relient une chaîne d’accesseurs à une constante. Les accesseurs pris en charge comprennent les membres nommés, les jokers de tableau et les index de tableau. Les deux classes d’opérateurs GIN JSONB prennent en charge ces formes ; jsonb_ops prend également en charge les jokers d’objet et la descente récursive. Types JSON.

Expression JSONPath Condition de recherche utile extraite ?
$.category == "layout" Oui, avec les deux classes d’opérateurs
$.tags[*] == "grid" Oui, avec les deux classes d’opérateurs
$.tags[0] == "grid" Oui, avec les deux classes d’opérateurs ; le prédicat complet doit tout de même être vérifié
$.* == "layout" Prise en charge par jsonb_ops, mais pas par jsonb_path_ops
$.**.category == "layout" Prise en charge par jsonb_ops, mais pas par jsonb_path_ops

Les limites suivantes découlent de l’implémentation de l’extraction de PostgreSQL 18. Ici, « aucune » signifie qu’aucune condition utile n’est extraite de cette expression seule, et ne garantit pas le nom du nœud d’analyse utilisé dans le plan. Implémentation GIN JSONB de PostgreSQL 18.

Expression JSONPath Condition extraite
$.words > 1200 Aucune
$.category != "layout" Aucune
exists($.category) Aucune
$.title like_regex \"Grid\" Aucune
$.words == $.target_words Aucune : aucun des opérandes n’est une constante scalaire

Pourquoi AND et OR se comportent-ils différemment ?

Avec $.category == "layout" && $.words > 1200, l’égalité peut sélectionner des candidats ; la condition complète est ensuite vérifiée. Avec $.category == "layout" || $.words > 1200, utiliser uniquement l’égalité ferait manquer les lignes correspondant à la plage. L’extraction échoue donc pour ce OR si l’une ou l’autre branche n’est pas prise en charge. Un OR reliant deux égalités prises en charge peut fournir des conditions de recherche. Ces règles concernent l’extraction au sein d’une même expression JSONPath ; d’autres conditions SQL peuvent toujours influer sur le plan global. Implémentation GIN JSONB de PostgreSQL 18.

Quel opérateur et quelle classe d’opérateurs choisir ?

Utilisez @? pour vérifier si un chemin renvoie un élément quelconque, généralement au moyen d’un filtre. Utilisez @@ pour vérifier un prédicat booléen. Passer une simple comparaison booléenne à @? est un piège : même une valeur booléenne fausse constitue un élément renvoyé. PostgreSQL distingue explicitement ces formes dans Fonctions et opérateurs JSON.

Ces deux requêtes expriment la même tâche pour la structure d’article d’exemple présentée ci-dessous :

SELECT id
FROM jsonpath_article_demo
WHERE doc @? '$.tags[*] ? (@ == "grid")';

SELECT id
FROM jsonpath_article_demo
WHERE doc @@ '$.tags[*] == "grid"';

Choisissez jsonb_path_ops pour la charge de travail illustrée, qui porte sur l’égalité d’un chemin nommé. Choisissez jsonb_ops, la classe par défaut, si vous avez aussi besoin d’opérateurs SQL d’existence de clé comme ?, ou des accesseurs génériques décrits ci-dessus. Les deux prennent en charge @>, @? et @@ ; la prise en charge d’un opérateur ne suffit pas à rendre chaque prédicat sélectif. Indexation JSONB.

Les exemples ciblent PostgreSQL 18, la version actuellement indiquée par la documentation officielle consultée. La prise en charge du chemin SQL/JSON est apparue dans PostgreSQL 12 ; ces opérateurs ne sont donc pas disponibles sur les serveurs antérieurs. PostgreSQL 12 est désormais indiqué comme non pris en charge ; ses notes de version établissent la date d’introduction, et non une version d’installation recommandée. Notes de version de PostgreSQL 12.

Comment reproduire la requête sur un Mac ?

Commencez avec une base de données PostgreSQL 18 en cours d’exécution, la commande psql disponible dans Terminal et une base de données de test existante sélectionnée au moyen de vos paramètres de connexion habituels. Cet exercice crée une nouvelle table et n’en remplace aucune. Si ce nom de table existe déjà, utilisez une autre base de test ou renommez la table de démonstration de façon cohérente.

Ouvrez psql dans Terminal, exécutez SHOW server_version;, puis collez la configuration complète ci-dessous. Elle crée 20 000 lignes d’articles synthétiques : un article sur cent a la catégorie layout, et le nombre de mots varie cycliquement de 400 à 1 999.

CREATE TABLE jsonpath_article_demo (
    id integer PRIMARY KEY,
    doc jsonb NOT NULL
);

INSERT INTO jsonpath_article_demo (id, doc)
SELECT
    n,
    jsonb_build_object(
        'title', 'Article ' || n,
        'category', CASE
            WHEN n % 100 = 0 THEN 'layout'
            ELSE 'notes'
        END,
        'words', 400 + n % 1600,
        'tags', jsonb_build_array(
            CASE WHEN n % 100 = 0 THEN 'grid' ELSE 'text' END
        )
    )
FROM generate_series(1, 20000) AS sample(n);

CREATE INDEX jsonpath_article_demo_doc_gin
ON jsonpath_article_demo USING gin (doc jsonb_path_ops);

ANALYZE jsonpath_article_demo;

SELECT id, doc->>'title' AS title
FROM jsonpath_article_demo
WHERE doc @@ '$.category == "layout" && $.words > 1200'
ORDER BY id
LIMIT 5;

Les cinq premiers identifiants calculés sont 900, 1000, 1100, 1200 et 1300 ; la condition complète correspond à 84 lignes générées. Il s’agit de résultats illustratifs du jeu de données, et non de mesures de performance. L’arithmétique et les identifiants attendus ont été vérifiés par un petit calcul JavaScript ; l’exécution de PostgreSQL n’était pas disponible dans l’environnement de recherche.

ANALYZE collecte les statistiques de la table utilisées par le planificateur pour choisir les plans d’exécution. L’exécuter après le chargement fournit une base de planification plus utile pour la démonstration. ANALYZE.

Comment savoir si GIN a été utile ?

Après la configuration, exécutez ces deux requêtes de diagnostic complètes dans la même base de données. La première inclut une égalité extractible ; la seconde isole le prédicat de plage.

EXPLAIN (ANALYZE, BUFFERS)
SELECT id
FROM jsonpath_article_demo
WHERE doc @@ '$.category == "layout" && $.words > 1200';

EXPLAIN (ANALYZE, BUFFERS)
SELECT id
FROM jsonpath_article_demo
WHERE doc @@ '$.words > 1200';

Pour la première requête, recherchez un Bitmap Index Scan indiquant jsonpath_article_demo_doc_gin, généralement sous un Bitmap Heap Scan. Examinez ensemble Index Cond, Recheck Cond, le nombre réel de lignes, l’activité des tampons et le temps d’exécution. Une condition d’index affichant l’expression JSONPath complète ne signifie pas que chaque comparaison est devenue une clé de recherche d’index. La documentation PostgreSQL Utiliser EXPLAIN explique ces champs du plan ainsi que la distinction entre la récupération des candidats et le filtrage ultérieur.

Une analyse séquentielle ne réfute pas à elle seule l’éligibilité à l’indexation. Le planificateur choisit en fonction des coûts estimés, et une petite table ou une correspondance étendue peut favoriser une analyse séquentielle. À l’inverse, la simple présence d’un nœud d’index ne prouve pas une réduction utile du périmètre. Comparez le travail effectué et le temps écoulé dans des conditions comparables. EXPLAIN ANALYZE exécute réellement chaque requête. Utiliser EXPLAIN.

Dans cet exercice, la question pratique est de savoir si l’égalité sur la catégorie réduit suffisamment le nombre de candidats pour que l’évaluation de la condition sur le nombre de mots soit peu coûteuse. La distribution de catégories générée fournit un cas de test clair, mais ne permet pas d’établir le meilleur plan pour une collection d’articles dont la distribution serait différente.

Comment node-postgres doit-il transmettre le JSONPath ?

Transmettez le JSONPath complet en un seul paramètre SQL et convertissez-le en jsonpath. L’espace réservé SQL $1 et la racine JSONPath $ appartiennent à des langages différents. node-postgres accepte le texte de la requête ainsi qu’un tableau de valeurs ; le serveur reçoit le chemin fourni comme valeur du paramètre. Requêtes.

Avec une installation actuelle et prise en charge de Node.js, exécutez npm install pg dans un répertoire de travail. Enregistrez le programme complet ci-dessous sous article-query.mjs. Il nécessite la configuration SQL ci-dessus dans la même base de données. La commande d’installation du paquet vient de l’introduction à node-postgres.

import pg from 'pg';

const { Client } = pg;
const client = new Client();
const category = 'layout';
const path = `$.category == ${JSON.stringify(category)} && $.words > 1200`;

try {
  await client.connect();

  const result = await client.query({
    text: `
      SELECT id, doc->>'title' AS title
      FROM jsonpath_article_demo
      WHERE doc @@ $1::jsonpath
      ORDER BY id
      LIMIT 5
    `,
    values: [path],
  });

  console.table(result.rows);
} catch (error) {
  console.error(error.message);
  process.exitCode = 1;
} finally {
  await client.end();
}

Exécutez node article-query.mjs en configurant l’environnement de connexion à la base de données pour la base de test. Client lit les variables de connexion PostgreSQL telles que PGHOST, PGPORT, PGDATABASE et PGUSER. Le programme signale les échecs de connexion et l’absence de la table de démonstration. Connexion.

Ici, JSON.stringify(category) construit le littéral chaîne JSONPath. Le chemin obtenu contient toujours l’égalité avec le littéral \"layout\" ; transmettre ce chemin via un paramètre SQL ne remplace pas l’égalité par une variable JSONPath.

Si vous utilisez plutôt jsonb_path_exists avec son argument vars, vous appelez une fonction avec des variables JSONPath nommées. Il s’agit d’une interface d’évaluation documentée, mais ce n’est pas la forme de l’opérateur indexé @?. Pour cette recherche GIN, conservez la requête avec opérateur présentée ci-dessus. Fonctions et opérateurs JSON, Indexation JSONB.

Que faut-il utiliser lorsque la condition principale est une plage numérique ?

Si la tâche principale consiste à sélectionner des articles selon leur nombre de mots, exposez cette valeur scalaire au moyen d’un index d’expression B-tree. PostgreSQL documente la prise en charge, par B-tree, des égalités et des comparaisons de plage ordonnées, et les index d’expression permettent d’indexer une valeur calculée. Types d’index, Index sur les expressions.

Exécutez cette configuration et cette requête supplémentaires sur la table générée :

CREATE INDEX jsonpath_article_demo_words_btree
ON jsonpath_article_demo (((doc->>'words')::integer));

ANALYZE jsonpath_article_demo;

EXPLAIN (ANALYZE, BUFFERS)
SELECT id
FROM jsonpath_article_demo
WHERE (doc->>'words')::integer > 1200;

La requête utilise volontairement la même expression que l’index. Elle nécessite une unique valeur words compatible avec le type entier pour chaque article. Les lignes générées satisfont cette condition ; des valeurs textuelles arbitraires ou hors plage peuvent faire échouer la conversion lors de la création de l’index ou d’écritures ultérieures. Cette réécriture ne remplace pas de manière générale les comparaisons JSONPath sur des tableaux, des types mixtes ou plusieurs valeurs correspondantes.

Servez-vous de cette limite pour orienter votre choix : commencez par GIN lorsque des égalités sélectives dans les documents guident la recherche, et envisagez un index d’expression B-tree lorsque la recherche porte principalement sur la plage d’un scalaire dont le type est constant. Préservez d’abord l’ensemble de résultats attendu, puis utilisez le plan réel pour déterminer si l’index choisi justifie son coût de maintenance.

Sources