Bagaimana untuk Mengoptimumkan Pertanyaan Array JSONB Postgres dengan Indeks GIN?

Cara Menggunakan Indeks GIN dengan Tatasusunan JSONB dalam Postgres

Masalah:
Apabila menanyakan tatasusunan JSONB dalam Postgres, indeks GIN menggunakan events_gin_idx PADA some_table MENGGUNAKAN GIN (peristiwa); mungkin tidak mengoptimumkan pertanyaan yang mencari elemen tatasusunan tertentu.

Punca:
Pertanyaan sedang menukar elemen tatasusunan kepada elemen tatasusunan JSON menggunakan jsonb_array_elements(events) AS e, yang mengganggu pengindeksan.

Pengindeksan yang Betul untuk Postgres 12 atau Kemudian:
Untuk mencipta indeks GIN yang menyokong pertanyaan dengan ungkapan laluan, gunakan kelas operator jsonb_path_ops:

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

Sintaks Pertanyaan untuk Postgres 12 atau Kemudian:
Gunakan @? operator untuk menguji elemen tatasusunan yang sepadan dengan syarat:

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

Sintaks Pertanyaan untuk Versi Lama Postgres:
Untuk versi sebelum Postgres 12, anda boleh menggunakan @> ; operator dengan objek JSON separa:

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

Penyelesaian Terperinci Menggunakan Paparan Terwujud:
Jika penyelesaian pengindeksan asas tidak cukup berprestasi, pertimbangkan untuk mencipta paparan terwujud:

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

Pertanyaan Paparan Termaterial:

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

Atas ialah kandungan terperinci Bagaimana untuk Mengoptimumkan Pertanyaan Array JSONB Postgres dengan Indeks GIN?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!