Indeks GIN JSONB PostgreSQL boleh mengecilkan skop carian JSONPath menggunakan ujian kesamaan laluan-ke-pemalar yang boleh diekstrak. Perbandingan julat, ketaksamaan dan ujian kewujudan laluan sahaja tidak membekalkan kunci carian tersebut dengan sendirinya. Predikat campuran perlu diteliti dengan lebih dekat.

Panduan ini membantu pembangun yang bekerja dalam Terminal pada Mac memilih indeks untuk menapis baris artikel sintetik, menghantar pertanyaan melalui node-postgres dan memeriksa pelan yang terhasil. Peraturan pengekstrakan teras didokumenkan dalam rujukan pengindeksan JSONB PostgreSQL.

Bentuk predikat manakah yang membekalkan kunci carian GIN yang berguna?

Bagi @? dan @@, PostgreSQL mengekstrak klausa kesamaan yang menghubungkan rantaian pengakses dengan pemalar. Pengakses yang disokong termasuk ahli bernama, kad bebas tatasusunan dan indeks tatasusunan. Kedua-dua kelas operator GIN JSONB menyokong bentuk ini; jsonb_ops turut menyokong kad bebas objek dan penurunan rekursif. Jenis JSON.

Ungkapan JSONPath Syarat carian berguna yang diekstrak?
$.category == "layout" Ya, dengan mana-mana kelas operator
$.tags[*] == "grid" Ya, dengan mana-mana kelas operator
$.tags[0] == "grid" Ya, dengan mana-mana kelas operator; predikat lengkap masih perlu diperiksa
$.* == "layout" Disokong oleh jsonb_ops, bukan jsonb_path_ops
$.**.category == "layout" Disokong oleh jsonb_ops, bukan jsonb_path_ops

Batasan berikut berpunca daripada pelaksanaan pengekstrakan PostgreSQL 18. Di sini, “tiada” bermaksud tiada syarat berguna yang diekstrak daripada ungkapan itu sahaja, bukannya jaminan tentang nama nod imbasan dalam pelan. Pelaksanaan GIN JSONB PostgreSQL 18.

Ungkapan JSONPath Syarat yang diekstrak
$.words > 1200 Tiada
$.category != "layout" Tiada
exists($.category) Tiada
$.title like_regex \"Grid\" Tiada
$.words == $.target_words Tiada: kedua-dua operan bukan pemalar skalar

Mengapakah AND dan OR berkelakuan berbeza?

Dengan $.category == "layout" && $.words > 1200, kesamaan boleh memilih calon; syarat lengkap diperiksa semula. Dengan $.category == "layout" || $.words > 1200, penggunaan kesamaan sahaja akan terlepas baris yang sepadan dengan julat. Oleh itu, pengekstrakan gagal bagi OR itu apabila mana-mana cabangnya tidak disokong. OR yang menggabungkan dua kesamaan yang disokong boleh membekalkan syarat carian. Peraturan ini berkaitan dengan pengekstrakan dalam satu ungkapan JSONPath; syarat SQL lain masih boleh mempengaruhi pelan keseluruhan. Pelaksanaan GIN JSONB PostgreSQL 18.

Operator dan kelas operator manakah patut dipilih?

Gunakan @? untuk menyemak sama ada sesuatu laluan mengembalikan sebarang item, lazimnya melalui penapis. Gunakan @@ untuk menyemak predikat Boolean. Perbandingan Boolean biasa yang dihantar kepada @? ialah perangkap: Boolean palsu pun tetap merupakan item yang dikembalikan. PostgreSQL membezakan bentuk ini dengan jelas dalam Fungsi dan Operator JSON.

Dua pertanyaan ini menyatakan tugasan yang sama untuk struktur artikel sampel 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 kesamaan laluan bernama yang ditunjukkan. Pilih jsonb_ops lalai apabila anda juga memerlukan operator kewujudan kunci SQL seperti ?, atau pengakses kad bebas yang diterangkan di atas. Kedua-duanya menyokong @>, @? dan @@; sokongan operator sahaja tidak menjadikan setiap predikat selektif. Pengindeksan JSONB.

Contoh ini menyasarkan PostgreSQL 18, versi yang kini dikenal pasti dalam dokumentasi rasmi yang diperoleh. Sokongan laluan SQL/JSON diperkenalkan dalam PostgreSQL 12, jadi operator ini tidak tersedia pada pelayan yang lebih lama. PostgreSQL 12 kini disenaraikan sebagai tidak disokong; nota keluarannya menetapkan sempadan pengenalan ciri, bukannya versi pemasangan yang disyorkan. Nota keluaran PostgreSQL 12.

Bagaimanakah anda boleh mengulangi pertanyaan ini pada Mac?

Mulakan dengan pangkalan data PostgreSQL 18 yang sedang berjalan, arahan psql yang tersedia dalam Terminal dan pangkalan data sementara sedia ada yang dipilih melalui tetapan sambungan biasa anda. Latihan ini mencipta jadual baharu dan tidak menggantikan jadual sedia ada. Jika nama jadual itu sudah wujud, gunakan pangkalan data sementara yang lain atau namakan semula jadual demonstrasi secara konsisten.

Buka psql dalam Terminal, jalankan SHOW server_version;, kemudian tampal persediaan lengkap ini. Ia mencipta 20,000 baris artikel sintetik: setiap artikel ke-100 mempunyai kategori layout, dan kiraan perkataan 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 terhasil ialah 900, 1000, 1100, 1200 dan 1300; syarat penuh sepadan dengan 84 baris yang dijana. Ini ialah hasil ilustrasi set data, bukannya ukuran prestasi. Aritmetik dan ID yang dijangka disemak menggunakan pengiraan JavaScript ringkas; pelaksanaan PostgreSQL tidak tersedia dalam persekitaran penyelidikan.

ANALYZE mengumpulkan statistik jadual yang digunakan oleh perancang semasa memilih pelan pelaksanaan. Menjalankannya selepas pemuatan memberikan demonstrasi ini asas perancangan yang lebih berguna. ANALYZE.

Bagaimanakah anda menentukan sama ada GIN membantu?

Selepas persediaan, jalankan dua pertanyaan diagnostik lengkap ini dalam pangkalan data yang sama. Pertama mengandungi kesamaan yang boleh diekstrak; kedua mengasingkan predikat julat.

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

Bagi pertanyaan pertama, cari Bitmap Index Scan yang menamakan jsonpath_article_demo_doc_gin, biasanya di bawah Bitmap Heap Scan. Baca Index Cond, Recheck Cond, bilangan baris sebenar, aktiviti penimbal dan masa pelaksanaan secara bersama. Syarat indeks yang memaparkan JSONPath lengkap tidak bermakna setiap perbandingan menjadi kunci carian indeks. Menggunakan EXPLAIN PostgreSQL menerangkan medan pelan ini serta perbezaan antara mendapatkan calon dengan penapisan seterusnya.

Imbasan berjujukan sahaja tidak membuktikan bahawa indeks tidak layak digunakan. Perancang memilih berdasarkan anggaran kos, dan jadual kecil atau padanan yang luas mungkin menjadikan imbasan lebih sesuai. Sebaliknya, kewujudan nod indeks sahaja tidak membuktikan bahawa skop carian berjaya dikecilkan. Bandingkan kerja yang dilakukan dan masa berlalu dalam keadaan yang setanding. EXPLAIN ANALYZE benar-benar melaksanakan setiap pertanyaan. Menggunakan EXPLAIN.

Bagi latihan ini, persoalan praktikalnya ialah sama ada kesamaan kategori mengurangkan bilangan calon secukupnya sehingga penilaian syarat kiraan perkataan menjadi murah. Taburan kategori yang dijana menyediakan kes ujian yang jelas, tetapi tidak dapat menentukan pelan terbaik untuk koleksi artikel dengan taburan yang berbeza.

Bagaimanakah node-postgres patut menghantar JSONPath?

Hantar keseluruhan JSONPath sebagai satu parameter SQL dan tukarkannya kepada jsonpath. Pemegang tempat SQL $1 dan punca JSONPath $ tergolong dalam bahasa yang berbeza. node-postgres menerima teks pertanyaan bersama tatasusunan nilai; pelayan menerima laluan yang dibekalkan sebagai nilai parameter. Pertanyaan.

Dengan pemasangan Node.js semasa yang disokong, jalankan npm install pg dalam direktori kerja. Simpan program lengkap berikut sebagai article-query.mjs. Program ini memerlukan persediaan SQL di atas dalam pangkalan data yang sama. Arahan pemasangan pakej ini daripada Pengenalan 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 persekitaran sambungan pangkalan data dikonfigurasikan untuk pangkalan data sementara tersebut. Client membaca pemboleh ubah sambungan PostgreSQL seperti PGHOST, PGPORT, PGDATABASE dan PGUSER. Kegagalan sambungan dan jadual demonstrasi yang tiada dilaporkan oleh program ini. Menyambung.

Di sini, JSON.stringify(category) membina literal rentetan JSONPath. Laluan yang terhasil masih mengandungi kesamaan terhadap literal \"layout\"; menghantar laluan itu melalui parameter SQL tidak menggantikan kesamaan tersebut dengan pemboleh ubah JSONPath.

Jika anda menggunakan jsonb_path_exists dengan argumen vars sebaliknya, anda memanggil fungsi dengan pemboleh ubah JSONPath bernama. Itu ialah antara muka penilaian yang didokumenkan, tetapi bukan bentuk operator @? yang diindeks. Untuk carian GIN ini, kekalkan pertanyaan operator yang ditunjukkan di atas. Fungsi dan Operator JSON, Pengindeksan JSONB.

Apakah yang patut digunakan apabila syarat utama ialah julat angka?

Jika tugasan utamanya ialah memilih artikel berdasarkan kiraan perkataan, dedahkan skalar itu melalui indeks ungkapan B-tree. PostgreSQL mendokumenkan sokongan B-tree untuk kesamaan dan perbandingan julat bertertib, manakala indeks ungkapan membolehkan nilai terkomputasi diindeks. Jenis Indeks, Indeks pada Ungkapan.

Jalankan persediaan dan pertanyaan tambahan ini terhadap jadual yang dijana:

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;

Pertanyaan ini sengaja menggunakan ungkapan yang sama seperti indeks. Prasyaratnya ialah setiap artikel mempunyai satu nilai words yang serasi dengan integer. Baris yang dijana memenuhi syarat itu; teks sewenang-wenangnya atau nilai di luar julat boleh menyebabkan penukaran gagal semasa penciptaan indeks atau penulisan seterusnya. Penulisan semula ini bukan pengganti umum bagi perbandingan JSONPath merentas tatasusunan, jenis bercampur atau berbilang nilai padanan.

Gunakan sempadan itu sebagai panduan membuat keputusan: mulakan dengan GIN apabila kesamaan dokumen yang selektif mendorong carian, dan pertimbangkan indeks ungkapan B-tree apabila julat skalar bertip konsisten menjadi pemacu. Kekalkan set hasil yang dimaksudkan terlebih dahulu, kemudian gunakan pelan sebenar untuk menentukan sama ada indeks yang dipilih berbaloi dengan kos penyelenggaraannya.

Sumber