Comment indexer efficacement les tableaux JSON dans PostgreSQL pour une récupération plus rapide des éléments ?

Indexez efficacement les tableaux JSON dans PostgreSQL pour une récupération plus rapide des éléments

Identifier et récupérer rapidement des éléments dans un tableau JSON dans une table PostgreSQL peut être un défi. Pour des performances optimales, il est important de comprendre la technologie d’indexation adaptée à vos besoins spécifiques en matière de données et de requêtes.

Utilisation de JSONB dans PostgreSQL 9.4

PostgreSQL 9.4 introduit le type de données binaire JSON jsonb, qui améliore considérablement les possibilités d'indexation. Utilisez jsonb pour créer des index GIN directement sur les tableaux JSON.

<code class="language-sql">CREATE TABLE tracks (id serial, artists jsonb);
CREATE INDEX tracks_artists_gin_idx ON tracks USING gin (artists);</code>

Cela active les requêtes pouvant utiliser les index GIN, par exemple :

<code class="language-sql">SELECT * FROM tracks WHERE artists @> '["The Dirty Heads"]';</code>

où @> représente l'opérateur "contient" de jsonb.

Vous pouvez également utiliser la classe d'opérateur jsonb_path_ops GIN (non définie par défaut, généralement plus petite et plus rapide) :

<code class="language-sql">CREATE INDEX tracks_artists_gin_idx ON tracks USING gin (artists jsonb_path_ops);</code>

Optimiser pour les valeurs de nom uniques

Si votre colonne artists contient uniquement un tableau de valeurs nommées de manière unique, il est plus efficace de stocker les valeurs sous forme de primitives de texte JSON plutôt que d'objets. Cela élimine le besoin de clés redondantes.

<code class="language-sql">CREATE TABLE tracks (id serial, artistnames jsonb);
INSERT INTO tracks VALUES (2, '["The Dirty Heads", "Louis Richards"]');
CREATE INDEX tracks_artistnames_gin_idx ON tracks USING gin (artistnames);</code>

Exemple de requête :

<code class="language-sql">SELECT * FROM tracks WHERE artistnames ? 'The Dirty Heads';
SELECT * FROM tracks WHERE artistnames @> '"The Dirty Heads"'::jsonb;</code>

Gestion de json dans PostgreSQL 9.3

Pour les versions PostgreSQL antérieures à 9.4, des fonctions invariantes et des index GIN peuvent être utilisés.

Créer une fonction :

<code class="language-sql">CREATE OR REPLACE FUNCTION json2arr(_j json, _key text)
RETURNS text[] LANGUAGE sql IMMUTABLE AS
'SELECT ARRAY(SELECT elem->>_key FROM json_array_elements(_j) elem)';</code>

Créez ensuite la fonction index :

<code class="language-sql">CREATE INDEX tracks_artists_gin_idx ON tracks USING gin (json2arr(artists, 'name'));</code>

Utilisez une requête qui correspond à l'expression d'index :

<code class="language-sql">SELECT * FROM tracks WHERE '{"The Dirty Heads"}'::text[]</code>

Assurez-vous toujours que les fonctions utilisées dans l'index sont immuables pour permettre l'indexation des fonctions. Opérateurs clés...

L'article a réécrit le texte original, en utilisant un langage plus concis, et a ajusté certaines phrases, mais conserve le contenu principal du texte original et l'emplacement des images.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!