Wie kann ich JSONB-Arrays in Postgres effizient nach Millionen von Zeilen abfragen?

Verwendung von JSONB-Operatoren und GIN-Indizierung für Postgres-Abfragen

In Postgres erfordert der Zugriff auf JSON-Array-Elemente eine andere Syntax als der von Ihnen beschriebene Ansatz. Um auf das erste Element eines JSON-Arrays zuzugreifen, verwenden Sie anstelle Ihres vorherigen Versuchs einen nullindizierten Ausdruck.

Die empfohlene Abfragesyntax für dieses Szenario lautet:

e->0->>'event_slug'

Darüber hinaus bietet Postgres Folgendes an umfassende Unterstützung für JSONB-Datentypen. Mit Postgres 12 und höher können Sie die SQL/JSON-Pfadfunktionalität für „Größer-als“- oder „Kleiner-als“-Vergleiche mithilfe von JSONB-Spalten nutzen.

Für Ihre spezifische Abfrage besteht der empfohlene Ansatz darin, einen GIN-Index mit zu erstellen die Operatorklasse jsonb_path_ops:

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

Wenn dieser Index vorhanden ist, sollte die folgende Abfrage den Index nutzen und effizient bereitstellen Ergebnisse:

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

In älteren Versionen von Postgres ohne direkte „Größer als“- oder „Kleiner als“-Operatoren für JSONB kann eine materialisierte Ansicht verwendet werden, um eine ähnliche Leistung zu erzielen. Dazu gehört das Speichern relevanter Attribute in normalisierter Form, sodass Sie herkömmliche Btree-Indizes für effiziente Abfragen erstellen können.

Angesichts Ihres Anwendungsfalls mit Millionen von Zeilen mit jeweils etwa 10 Ereignissen sind sowohl der GIN-Index- als auch der Materialized-View-Ansatz realisierbar Lösungen. Die beste Option hängt von Faktoren wie der Komplexität Ihrer Abfragen, der Häufigkeit der Aktualisierungen und den gewünschten Leistungsmerkmalen ab.

Das obige ist der detaillierte Inhalt vonWie kann ich JSONB-Arrays in Postgres effizient nach Millionen von Zeilen abfragen?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!