Bagaimanakah Saya Boleh Menyoal Tatasusunan JSONB dengan Cekap dalam Postgres untuk Berjuta-juta Baris?

Menggunakan Operator JSONB dan Pengindeksan GIN untuk Pertanyaan Postgres

Dalam Postgres, mengakses elemen tatasusunan JSON memerlukan sintaks yang berbeza daripada pendekatan yang telah anda gariskan. Untuk mengakses elemen pertama tatasusunan JSON, gunakan ungkapan berindeks sifar dan bukannya percubaan anda sebelum ini.

Sintaks pertanyaan yang disyorkan untuk senario ini ialah:

e->0->>'event_slug'

Selain itu, Postgres menawarkan sokongan komprehensif untuk jenis data jsonb. Dengan Postgres 12 dan lebih baru, anda boleh menggunakan fungsi laluan SQL/JSON untuk perbandingan "lebih besar daripada" atau "kurang daripada" menggunakan lajur jsonb.

Untuk pertanyaan khusus anda, pendekatan yang disyorkan adalah untuk mencipta indeks GIN menggunakan kelas operator jsonb_path_ops:

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

Dengan indeks ini disediakan, pertanyaan berikut harus menggunakan indeks dan berikan hasil yang cekap:

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

Dalam versi lama Postgres tanpa pengendali langsung "lebih besar daripada" atau "kurang daripada" untuk jsonb, pandangan terwujud boleh digunakan untuk mencapai prestasi yang serupa. Ini melibatkan penyimpanan atribut yang berkaitan dalam bentuk ternormal, membolehkan anda mencipta indeks btree tradisional untuk pertanyaan yang cekap.

Memandangkan kes penggunaan anda dengan berjuta-juta baris yang mengandungi kira-kira 10 peristiwa setiap satu, kedua-dua indeks GIN dan pendekatan paparan terwujud adalah berdaya maju penyelesaian. Pilihan terbaik bergantung pada faktor seperti kerumitan pertanyaan anda, kekerapan kemas kini dan ciri prestasi yang diingini.

Atas ialah kandungan terperinci Bagaimanakah Saya Boleh Menyoal Tatasusunan JSONB dengan Cekap dalam Postgres untuk Berjuta-juta Baris?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!