Indeks GIN JSONB PostgreSQL dapat mempersempit pencarian JSONPath menggunakan pengujian kesetaraan jalur-ke-konstanta yang dapat diekstrak. Perbandingan rentang, ketidaksamaan, dan pengujian keberadaan jalur tanpa syarat tidak menyediakan kunci pencarian tersebut dengan sendirinya. Predikat campuran perlu diperiksa lebih saksama.

Panduan ini membantu pengembang yang bekerja di Terminal pada Mac memilih indeks untuk memfilter baris artikel sintetis, meneruskan kueri melalui node-postgres, dan memeriksa rencana yang dihasilkan. Aturan ekstraksi inti didokumentasikan dalam referensi pengindeksan JSONB PostgreSQL.

Bentuk predikat mana yang menyediakan kunci pencarian GIN yang berguna?

Untuk @? dan @@, PostgreSQL mengekstrak klausa kesetaraan yang menghubungkan rantai aksesor dengan konstanta. Aksesor yang didukung mencakup anggota bernama, wildcard larik, dan indeks larik. Kedua kelas operator GIN JSONB mendukung bentuk-bentuk ini; jsonb_ops juga mendukung wildcard objek dan penelusuran rekursif. Tipe JSON.

Ekspresi JSONPath Kondisi pencarian berguna yang diekstrak?
$.category == "layout" Ya, dengan kedua kelas operator
$.tags[*] == "grid" Ya, dengan kedua kelas operator
$.tags[0] == "grid" Ya, dengan kedua kelas operator; predikat lengkap tetap perlu diperiksa
$.* == "layout" Didukung oleh jsonb_ops, bukan jsonb_path_ops
$.**.category == "layout" Didukung oleh jsonb_ops, bukan jsonb_path_ops

Batasan berikut berasal dari implementasi ekstraksi PostgreSQL 18. Di sini, “tidak ada” berarti tidak ada kondisi berguna yang diekstrak dari ekspresi itu saja, bukan jaminan tentang nama simpul pemindaian dalam rencana. Implementasi GIN JSONB PostgreSQL 18.

Ekspresi JSONPath Kondisi yang diekstrak
$.words > 1200 Tidak ada
$.category != "layout" Tidak ada
exists($.category) Tidak ada
$.title like_regex \"Grid\" Tidak ada
$.words == $.target_words Tidak ada: kedua operand bukan konstanta skalar

Mengapa AND dan OR berperilaku berbeda?

Dengan $.category == "layout" && $.words > 1200, kesetaraan dapat memilih kandidat; kondisi lengkap akan diperiksa ulang. Dengan $.category == "layout" || $.words > 1200, hanya menggunakan kesetaraan akan melewatkan baris yang cocok dengan rentang. Karena itu, ekstraksi gagal untuk OR tersebut jika salah satu bagian tidak didukung. OR yang menggabungkan dua kesetaraan yang didukung dapat menyediakan kondisi pencarian. Aturan ini menyangkut ekstraksi di dalam satu ekspresi JSONPath; kondisi SQL lainnya tetap dapat memengaruhi rencana keseluruhan. Implementasi GIN JSONB PostgreSQL 18.

Operator dan kelas operator mana yang sebaiknya dipilih?

Gunakan @? untuk menanyakan apakah suatu jalur menghasilkan item apa pun, biasanya melalui filter. Gunakan @@ untuk pemeriksaan predikat Boolean. Perbandingan Boolean tanpa syarat yang diteruskan ke @? adalah jebakan: Boolean false pun merupakan item yang dihasilkan. PostgreSQL secara eksplisit membedakan bentuk-bentuk ini dalam Fungsi dan Operator JSON.

Kedua kueri ini menyatakan tugas yang sama untuk struktur artikel contoh yang diperkenalkan di bawah:

SELECT id
FROM jsonpath_article_demo
WHERE doc @? '$.tags[*] ? (@ == "grid")';

SELECT id
FROM jsonpath_article_demo
WHERE doc @@ '$.tags[*] == "grid"';

Pilih jsonb_path_ops untuk beban kerja kesetaraan jalur bernama yang diperagakan. Pilih jsonb_ops sebagai kelas bawaan jika Anda juga memerlukan operator keberadaan kunci SQL seperti ?, atau aksesor wildcard yang dijelaskan di atas. Keduanya mendukung @>, @?, dan @@; dukungan operator saja tidak membuat setiap predikat selektif. Pengindeksan JSONB.

Contoh ini menargetkan PostgreSQL 18, versi yang saat ini diidentifikasi oleh dokumentasi resmi yang diambil. Dukungan jalur SQL/JSON hadir di PostgreSQL 12, sehingga operator ini tidak tersedia pada server yang lebih lama. PostgreSQL 12 kini tercantum sebagai tidak didukung; catatan rilisnya menetapkan batas pengenalan fitur, bukan versi instalasi yang direkomendasikan. Catatan rilis PostgreSQL 12.

Bagaimana cara mereproduksi kueri di Mac?

Mulailah dengan basis data PostgreSQL 18 yang sedang berjalan, perintah psql yang tersedia di Terminal, dan basis data kerja sementara yang sudah ada serta dipilih melalui pengaturan koneksi biasa Anda. Latihan ini membuat tabel baru dan tidak mengganti tabel yang sudah ada. Jika nama tabel tersebut sudah digunakan, pakai basis data kerja sementara lain atau ganti nama tabel demonstrasi secara konsisten.

Buka psql di Terminal, jalankan SHOW server_version;, lalu tempelkan seluruh penyiapan berikut. Ini membuat 20.000 baris artikel sintetis: setiap artikel keseratus memiliki kategori layout, dan jumlah kata berulang dari 400 hingga 1.999.

CREATE TABLE jsonpath_article_demo (
    id integer PRIMARY KEY,
    doc jsonb NOT NULL
);

INSERT INTO jsonpath_article_demo (id, doc)
SELECT
    n,
    jsonb_build_object(
        'title', 'Article ' || n,
        'category', CASE
            WHEN n % 100 = 0 THEN 'layout'
            ELSE 'notes'
        END,
        'words', 400 + n % 1600,
        'tags', jsonb_build_array(
            CASE WHEN n % 100 = 0 THEN 'grid' ELSE 'text' END
        )
    )
FROM generate_series(1, 20000) AS sample(n);

CREATE INDEX jsonpath_article_demo_doc_gin
ON jsonpath_article_demo USING gin (doc jsonb_path_ops);

ANALYZE jsonpath_article_demo;

SELECT id, doc->>'title' AS title
FROM jsonpath_article_demo
WHERE doc @@ '$.category == "layout" && $.words > 1200'
ORDER BY id
LIMIT 5;

Lima ID pertama yang diturunkan adalah 900, 1000, 1100, 1200, dan 1300; kondisi lengkap cocok dengan 84 baris yang dihasilkan. Ini adalah hasil ilustratif dari himpunan data, bukan pengukuran kinerja. Aritmetika dan ID yang diharapkan diperiksa dengan perhitungan JavaScript kecil; eksekusi PostgreSQL tidak tersedia di lingkungan riset.

ANALYZE mengumpulkan statistik tabel yang digunakan perencana saat memilih rencana eksekusi. Menjalankannya setelah pemuatan data memberi demonstrasi ini dasar perencanaan yang lebih berguna. ANALYZE.

Bagaimana mengetahui apakah GIN membantu?

Setelah penyiapan, jalankan dua kueri diagnostik lengkap ini di basis data yang sama. Kueri pertama mencakup kesetaraan yang dapat diekstrak; kueri kedua mengisolasi predikat rentang.

EXPLAIN (ANALYZE, BUFFERS)
SELECT id
FROM jsonpath_article_demo
WHERE doc @@ '$.category == "layout" && $.words > 1200';

EXPLAIN (ANALYZE, BUFFERS)
SELECT id
FROM jsonpath_article_demo
WHERE doc @@ '$.words > 1200';

Untuk kueri pertama, cari Bitmap Index Scan yang menyebut jsonpath_article_demo_doc_gin, biasanya di bawah Bitmap Heap Scan. Baca Index Cond, Recheck Cond, jumlah baris aktual, aktivitas buffer, dan waktu eksekusi secara bersamaan. Kondisi indeks yang menampilkan seluruh JSONPath tidak berarti setiap perbandingan menjadi kunci pencarian indeks. Menggunakan EXPLAIN dari PostgreSQL menjelaskan kolom rencana ini dan perbedaan antara pengambilan kandidat dan pemfilteran sesudahnya.

Pemindaian sekuensial tidak dengan sendirinya membuktikan bahwa indeks tidak memenuhi syarat. Perencana memilih berdasarkan perkiraan biaya, dan tabel kecil atau kecocokan luas dapat membuat pemindaian lebih efisien. Sebaliknya, sekadar melihat simpul indeks tidak membuktikan penyempitan yang berguna. Bandingkan pekerjaan yang dilakukan dan waktu yang berlalu dalam kondisi sebanding. EXPLAIN ANALYZE benar-benar menjalankan setiap kueri. Menggunakan EXPLAIN.

Untuk latihan ini, pertanyaan praktisnya adalah apakah kesetaraan kategori cukup mengurangi kandidat sehingga evaluasi kondisi jumlah kata menjadi murah. Distribusi kategori yang dihasilkan menyediakan kasus uji yang jelas, tetapi tidak dapat menetapkan rencana terbaik untuk kumpulan artikel dengan distribusi berbeda.

Bagaimana node-postgres sebaiknya meneruskan JSONPath?

Teruskan seluruh JSONPath sebagai satu parameter SQL dan lakukan cast ke jsonpath. Placeholder SQL $1 dan akar JSONPath $ berasal dari bahasa yang berbeda. node-postgres menerima teks kueri beserta larik nilai; server menerima jalur yang diberikan sebagai nilai parameter. Kueri.

Dengan instalasi Node.js terkini yang didukung, jalankan npm install pg di direktori kerja. Simpan program lengkap berikut sebagai article-query.mjs. Program ini memerlukan penyiapan SQL di atas dalam basis data yang sama. Perintah instalasi paket berasal dari pengantar node-postgres.

import pg from 'pg';

const { Client } = pg;
const client = new Client();
const category = 'layout';
const path = `$.category == ${JSON.stringify(category)} && $.words > 1200`;

try {
  await client.connect();

  const result = await client.query({
    text: `
      SELECT id, doc->>'title' AS title
      FROM jsonpath_article_demo
      WHERE doc @@ $1::jsonpath
      ORDER BY id
      LIMIT 5
    `,
    values: [path],
  });

  console.table(result.rows);
} catch (error) {
  console.error(error.message);
  process.exitCode = 1;
} finally {
  await client.end();
}

Jalankan node article-query.mjs dengan lingkungan koneksi basis data yang dikonfigurasi untuk basis data kerja sementara. Client membaca variabel koneksi PostgreSQL seperti PGHOST, PGPORT, PGDATABASE, dan PGUSER. Kegagalan koneksi dan tabel demonstrasi yang tidak ditemukan akan dilaporkan oleh program. Menghubungkan.

Di sini, JSON.stringify(category) membentuk literal string JSONPath. Jalur yang dihasilkan tetap memuat kesetaraan terhadap literal \"layout\"; meneruskan jalur itu melalui parameter SQL tidak mengganti kesetaraan tersebut dengan variabel JSONPath.

Jika Anda menggunakan jsonb_path_exists dengan argumen vars sebagai gantinya, Anda memanggil fungsi dengan variabel JSONPath bernama. Ini adalah antarmuka evaluasi yang terdokumentasi, tetapi bukan bentuk operator @? yang diindeks. Untuk pencarian GIN ini, gunakan kueri operator yang ditampilkan di atas. Fungsi dan Operator JSON, Pengindeksan JSONB.

Apa yang sebaiknya digunakan jika kondisi utamanya adalah rentang numerik?

Jika tugas utamanya memilih artikel berdasarkan jumlah kata, tampilkan nilai skalar tersebut melalui indeks ekspresi B-tree. PostgreSQL mendokumentasikan dukungan B-tree untuk kesetaraan dan perbandingan rentang berurutan, sedangkan indeks ekspresi memungkinkan pengindeksan nilai hasil perhitungan. Jenis Indeks, Indeks pada Ekspresi.

Jalankan penyiapan tambahan dan kueri berikut pada tabel yang dihasilkan:

CREATE INDEX jsonpath_article_demo_words_btree
ON jsonpath_article_demo (((doc->>'words')::integer));

ANALYZE jsonpath_article_demo;

EXPLAIN (ANALYZE, BUFFERS)
SELECT id
FROM jsonpath_article_demo
WHERE (doc->>'words')::integer > 1200;

Kueri ini sengaja menggunakan ekspresi yang sama dengan indeks. Prasyaratnya adalah satu nilai words yang kompatibel dengan integer untuk setiap artikel. Baris yang dihasilkan memenuhi kondisi tersebut; teks sembarang atau nilai di luar rentang dapat menyebabkan cast gagal saat pembuatan indeks atau penulisan berikutnya. Penulisan ulang ini bukan pengganti umum untuk perbandingan JSONPath pada larik, tipe campuran, atau beberapa nilai yang cocok.

Gunakan batasan ini sebagai panduan keputusan: mulai dengan GIN ketika kesetaraan dokumen yang selektif menjadi dasar pencarian, dan pertimbangkan indeks ekspresi B-tree ketika rentang skalar bertipe konsisten menjadi dasarnya. Pertahankan hasil yang dimaksud terlebih dahulu, lalu gunakan rencana aktual untuk menentukan apakah indeks yang dipilih sepadan dengan biaya pemeliharaannya.

Sumber