Wie optimiere ich Postgres-JSONB-Array-Abfragen mit GIN-Indizes?

So verwenden Sie GIN-Indizes mit JSONB-Arrays in Postgres

Problem:
Bei der Abfrage von JSONB-Arrays in Postgres, ein GIN-Index mit events_gin_idx ON some_table USING GIN (events); Möglicherweise werden Abfragen, die nach bestimmten Array-Elementen suchen, nicht optimiert.

Ursache:
Die Abfrage konvertiert die Array-Elemente mithilfe von jsonb_array_elements(events) AS e in JSON-Array-Elemente, was die Funktion stört Indizierung.

Richtige Indizierung für Postgres 12 oder Später:
Um einen GIN-Index zu erstellen, der Abfragen mit Pfadausdrücken unterstützt, verwenden Sie die Operatorklasse jsonb_path_ops:

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

Abfragesyntax für Postgres 12 oder höher:
Verwenden Sie das @? Operator zum Testen auf Array-Elemente, die einer Bedingung entsprechen:

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

Abfragesyntax für ältere Versionen von Postgres:
Für Versionen vor Postgres 12 können Sie das @> verwenden ; Operator mit einem partiellen JSON-Objekt:

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

Erweiterte Lösung mit materialisierter Ansicht:
Wenn die grundlegenden Indizierungslösungen nicht leistungsfähig genug sind, sollten Sie die Erstellung einer materialisierten Ansicht in Betracht ziehen:

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

Materialisierte Ansichtsabfrage:

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

Das obige ist der detaillierte Inhalt vonWie optimiere ich Postgres-JSONB-Array-Abfragen mit GIN-Indizes?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!