Comment optimiser les requêtes de tableau Postgres JSONB avec les index GIN ?

Comment utiliser les index GIN avec des tableaux JSONB dans Postgres

Problème :
Lors de l'interrogation de tableaux JSONB dans Postgres, un index GIN utilisant events_gin_idx ON some_table USING GIN (events) ; peut ne pas optimiser les requêtes qui recherchent des éléments de tableau spécifiques.

Cause :
La requête convertit les éléments du tableau en éléments du tableau JSON à l'aide de jsonb_array_elements(events) AS e, ce qui perturbe le indexation.

Indexation appropriée pour Postgres 12 ou Plus tard :
Pour créer un index GIN prenant en charge les requêtes avec des expressions de chemin, utilisez la classe d'opérateur jsonb_path_ops :

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

Syntaxe de requête pour Postgres 12 ou version ultérieure :
Utiliser le @? opérateur pour tester les éléments du tableau qui correspondent à une condition :

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

Syntaxe de requête pour les anciennes versions de Postgres :
Pour les versions antérieures à Postgres 12, vous pouvez utiliser l'opérateur @> ; opérateur avec un objet JSON partiel :

SELECT *
FROM locations
WHERE events @> '[{ "event_slug": "test_1"}]';

Solution avancée utilisant la vue matérialisée :
Si les solutions d'indexation de base ne sont pas assez performantes, pensez à créer une vue matérialisée :

CREATE MATERIALIZED VIEW loc_event AS
SELECT l.location_id, e.event_slug, e.end_time  -- start_time not needed
FROM locations l, jsonb_populate_recordset(null::event_type, l.events) e;

CREATE INDEX loc_event_idx ON loc_event (event_slug, end_time, location_id);

Requête de vue matérialisée :

SELECT *
FROM loc_event
WHERE event_slug = 'test_1'
AND end_time >= '2014-10-30 14:04:06 -0400'::timestamptz;

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!