Comment puis-je interroger efficacement des tableaux JSONB dans Postgres pour des millions de lignes ?

Utilisation des opérateurs JSONB et de l'indexation GIN pour les requêtes Postgres

Dans Postgres, l'accès aux éléments du tableau JSON nécessite une syntaxe différente de l'approche que vous avez décrite. Pour accéder au premier élément d'un tableau JSON, utilisez une expression à index zéro au lieu de votre tentative précédente.

La syntaxe de requête recommandée pour ce scénario est :

e->0->>'event_slug'

De plus, Postgres propose prise en charge complète des types de données jsonb. Avec Postgres 12 et versions ultérieures, vous pouvez utiliser la fonctionnalité de chemin SQL/JSON pour les comparaisons « supérieur à » ou « inférieur à » à l'aide de colonnes jsonb.

Pour votre requête spécifique, l'approche recommandée consiste à créer un index GIN en utilisant la classe d'opérateur jsonb_path_ops :

CREATE INDEX locations_events_gin_idx ON locations USING GIN (events jsonb_path_ops);

Avec cet index en place, la requête suivante doit utiliser l'index et fournir un résultats :

SELECT l.*
FROM locations l
WHERE l.events @? '$[*] ? (@.event_slug == "test_1") ? (@.end_time.datetime() < "2014-10-13".datetime())'

Dans les anciennes versions de Postgres sans opérateurs directs « supérieur à » ou « inférieur à » pour jsonb, une vue matérialisée peut être utilisée pour obtenir des performances similaires. Cela implique de stocker les attributs pertinents sous une forme normalisée, vous permettant de créer des index btree traditionnels pour des requêtes efficaces.

Compte tenu de votre cas d'utilisation avec des millions de lignes contenant environ 10 événements chacune, les approches d'index GIN et de vue matérialisée sont viables. solutions. La meilleure option dépend de facteurs tels que la complexité de vos requêtes, la fréquence des mises à jour et les caractéristiques de performances souhaitées.

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!