Comment puis-je optimiser les requêtes Postgres sur les tableaux jsonb pour de meilleures performances ?

Interrogation de structures dans des tableaux à l'aide de Postgres jsonb

Les tableaux Jsonb peuvent stocker des données structurées dans Postgres, facilitant ainsi les requêtes avec des objets imbriqués. Cependant, l'accès aux valeurs de tableau avec des index séquentiels peut entraîner des analyses séquentielles.

Indexation appropriée pour des performances de requête améliorées

Pour optimiser les requêtes impliquant des comparaisons de tableaux JSONB, en particulier les requêtes telles que celui fourni (vérification des événements dans une certaine plage de temps), les étapes suivantes peuvent être pris :

• Utiliser la classe d'opérateur jsonb_path_ops : Cela garantit une correspondance efficace pour les comparaisons jsonb complexes impliquant des opérateurs supérieurs ou inférieurs à.

Approche de base (Postgres 12 et Plus tard)

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

Approche avancée utilisant des vues matérialisées

Si des requêtes complexes entraînent toujours de mauvaises performances, envisagez de créer une vue matérialisée avec des attributs pertinents normalisés :

• Créer des données d'événement Type :

  CREATE TYPE event_type AS (
   , event_slug  text
   , start_time  timestamp
   , end_time    timestamp
  );

• 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;

• Index matérialisé Vue :

  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;

  En utilisant l'opérateur approprié et en considérant des approches avancées telles que les vues matérialisées, vous pouvez obtenir des performances optimales pour les requêtes impliquant des comparaisons sur les données du tableau jsonb.

  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!