Wie kann ich Postgres-Abfragen auf JSONB-Arrays optimieren, um die Leistung zu verbessern?

Abfragen von Strukturen in Arrays mit Postgres jsonb

Jsonb-Arrays können strukturierte Daten in Postgres speichern und so Abfragen mit verschachtelten Objekten erleichtern. Der Zugriff auf Array-Werte mit sequentiellen Indizes kann jedoch zu sequentiellen Scans führen.

Richtige Indizierung für verbesserte Abfrageleistung

Zur Optimierung von Abfragen mit JSONB-Array-Vergleichen, insbesondere Abfragen wie Bei einer bereitgestellten Methode (Überprüfung auf Ereignisse innerhalb eines bestimmten Zeitraums) können die folgenden Schritte ausgeführt werden Genommen:

• Verwenden Sie die Operatorklasse „jsonb_path_ops“: Dies gewährleistet einen effizienten Abgleich für komplexe JSONB-Vergleiche mit Größer-als- oder Kleiner-als-Operatoren.

Grundlegender Ansatz (Postgres 12 und Später)

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

Erweiterter Ansatz unter Verwendung materialisierter Ansichten

Wenn komplexe Abfragen immer noch zu einer schlechten Leistung führen, sollten Sie die Erstellung einer materialisierten Ansicht mit normalisierten relevanten Attributen in Betracht ziehen:

• Ereignisdaten erstellen Typ:

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

• Materialisierte Ansicht erstellen:

  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 materialisiert Ansicht:

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

• Materialisierte Ansicht abfragen:

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

  Durch Verwendung des richtigen Operators Klasse und unter Berücksichtigung fortgeschrittener Ansätze wie materialisierter Ansichten können Sie eine optimale Leistung für Abfragen erzielen, die Vergleiche mit JSONB-Array-Daten beinhalten.

  Das obige ist der detaillierte Inhalt vonWie kann ich Postgres-Abfragen auf JSONB-Arrays optimieren, um die Leistung zu verbessern?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!