يمكن لفهارس GIN لـ JSONB في PostgreSQL تضييق نطاق عمليات البحث باستخدام JSONPath عبر اختبارات مساواة قابلة للاستخراج بين المسار والثابت. لا توفر مقارنات النطاق وعدم المساواة واختبارات وجود المسار المجردة مفاتيح البحث هذه بمفردها. وتتطلب المسندات المختلطة فحصًا أدق.

يساعد هذا الدليل المطوّر الذي يعمل في Terminal على جهاز Mac على اختيار فهرس لتصفية صفوف مقالات اصطناعية، وتمرير الاستعلام عبر node-postgres، وفحص الخطة الناتجة. قاعدة الاستخراج الأساسية موثّقة في مرجع فهرسة JSONB الخاص بـ PostgreSQL.

ما أشكال المسندات التي توفر مفاتيح بحث مفيدة لـ GIN؟

بالنسبة إلى @? و@@، يستخرج PostgreSQL شروط المساواة التي تربط سلسلة من المُوصلات بثابت. تشمل المُوصلات المدعومة الأعضاء المسماة، ومحارف البدل للمصفوفات، وفهارس المصفوفات. تدعم فئتا مشغّلات GIN لـ JSONB هذه الأشكال؛ كما تدعم jsonb_ops محارف البدل للكائنات والاجتياز التكراري. أنواع JSON.

تعبير JSONPath هل يوجد شرط بحث مفيد مستخرج؟
$.category == "layout" نعم، مع أي من فئتي المشغّلات
$.tags[*] == "grid" نعم، مع أي من فئتي المشغّلات
$.tags[0] == "grid" نعم، مع أي من فئتي المشغّلات؛ لكن يجب التحقق من المسند كاملًا
$.* == "layout" مدعوم في jsonb_ops، وليس في jsonb_path_ops
$.**.category == "layout" مدعوم في jsonb_ops، وليس في jsonb_path_ops

تستند الحدود التالية إلى تنفيذ الاستخراج في PostgreSQL 18. تعني كلمة «لا شيء» هنا عدم وجود شرط مفيد مستخرج من ذلك التعبير وحده، وليس ضمانًا لاسم عقدة المسح في الخطة. تنفيذ GIN لـ JSONB في PostgreSQL 18.

تعبير JSONPath الشرط المستخرج
$.words > 1200 لا شيء
$.category != "layout" لا شيء
exists($.category) لا شيء
$.title like_regex \"Grid\" لا شيء
$.words == $.target_words لا شيء: لا أحد المعاملين ثابت عددي

لماذا يتصرف AND وOR بشكل مختلف؟

مع $.category == "layout" && $.words > 1200، يمكن للمساواة تحديد الصفوف المرشحة؛ ثم يُعاد التحقق من الشرط كاملًا. أما مع $.category == "layout" || $.words > 1200، فإن استخدام المساواة وحدها سيُفوّت الصفوف المطابقة للنطاق. لذلك يفشل الاستخراج لذلك الـOR إذا كان أحد الفرعين غير مدعوم. ويمكن لـOR الذي يجمع بين مساواتين مدعومتين أن يوفر شروط بحث. تتعلق هذه القواعد بالاستخراج داخل تعبير JSONPath واحد؛ وقد تظل شروط SQL الأخرى مؤثرة في الخطة الإجمالية. تنفيذ GIN لـ JSONB في PostgreSQL 18.

أي مشغّل وفئة مشغّلات ينبغي أن تختار؟

استخدم @? للسؤال عما إذا كان المسار يُرجع أي عنصر، عادةً من خلال مرشح. واستخدم @@ للتحقق من مسند منطقي. وتمرير مقارنة منطقية مجردة إلى @? فخّ: فحتى القيمة المنطقية false تُعد عنصرًا مُعادًا. يميّز PostgreSQL صراحةً بين هذين الشكلين في دوال JSON ومشغّلاتها.

يعبر هذان الاستعلامان عن المهمة نفسها بالنسبة إلى بنية المقالات النموذجية الموضحة أدناه:

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

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

اختر jsonb_path_ops لعبء العمل المعروض الذي يستخدم مساواة عبر مسار مسمى. واختر jsonb_ops الافتراضي إذا كنت تحتاج أيضًا إلى مشغّلات وجود المفاتيح في SQL مثل ?، أو إلى مُوصلات البدل المذكورة أعلاه. يدعم كلاهما @> و@? و@@؛ لكن دعم المشغّل وحده لا يجعل كل مسند انتقائيًا. فهرسة JSONB.

تستهدف الأمثلة PostgreSQL 18، وهو الإصدار الذي تحدده الوثائق الرسمية التي جرى استرجاعها حاليًا. أُضيف دعم مسارات SQL/JSON في PostgreSQL 12، لذلك لا تتوفر هذه المشغّلات في الخوادم الأقدم. أُدرج PostgreSQL 12 الآن ضمن الإصدارات غير المدعومة؛ وتثبت ملاحظات إصداره حدّ بدء الدعم، لا أنه إصدار موصى بتثبيته. ملاحظات إصدار PostgreSQL 12.

كيف تعيد تنفيذ الاستعلام على جهاز Mac؟

ابدأ بقاعدة بيانات PostgreSQL 18 قيد التشغيل، وبإتاحة الأمر psql في Terminal، وبقاعدة بيانات مؤقتة موجودة محددة عبر إعدادات الاتصال المعتادة لديك. ينشئ هذا التمرين جدولًا جديدًا ولا يستبدل جدولًا موجودًا. إذا كان اسم الجدول مستخدمًا بالفعل، فاستخدم قاعدة بيانات مؤقتة أخرى أو أعد تسمية جدول العرض باستمرار في جميع المواضع.

افتح psql في Terminal، وشغّل SHOW server_version;، ثم الصق إعداد التهيئة الكامل التالي. ينشئ 20,000 صف مقالات اصطناعيًا: تحمل كل مقالة مئة الفئة layout، وتتكرر أعداد الكلمات من 400 إلى 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;

المعرّفات الخمسة الأولى الناتجة هي 900 و1000 و1100 و1200 و1300؛ ويطابق الشرط الكامل 84 صفًا مُنشأً. هذه نتائج توضيحية لمجموعة البيانات، وليست قياسات أداء. جرى التحقق من الحساب والمعرّفات المتوقعة باستخدام عملية حسابية صغيرة في JavaScript؛ ولم يكن تنفيذ PostgreSQL متاحًا في بيئة البحث.

يجمع ANALYZE إحصاءات الجدول التي يستخدمها المخطط عند اختيار خطط التنفيذ. يمنح تشغيله بعد تحميل البيانات أساسًا أنسب للتخطيط في هذا العرض التوضيحي. ANALYZE.

كيف تعرف ما إذا كان GIN قد ساعد؟

بعد الإعداد، شغّل استعلامَي التشخيص الكاملين هذين في قاعدة البيانات نفسها. يتضمن الأول مساواة قابلة للاستخراج؛ أما الثاني فيعزل مسند النطاق.

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

في الاستعلام الأول، ابحث عن Bitmap Index Scan يذكر jsonpath_article_demo_doc_gin، ويكون عادةً أسفل Bitmap Heap Scan. اقرأ معًا Index Cond وRecheck Cond وأعداد الصفوف الفعلية ونشاط المخازن المؤقتة ووقت التنفيذ. ظهور JSONPath كاملًا في شرط الفهرس لا يعني أن كل مقارنة تحولت إلى مفتاح بحث في الفهرس. يشرح استخدام EXPLAIN من PostgreSQL حقول الخطة هذه والفرق بين جلب الصفوف المرشحة وتصفيتها لاحقًا.

لا يُثبت المسح التسلسلي وحده عدم أهلية الاستعلام لاستخدام الفهرس. يختار المخطط بين التكاليف المقدرة، وقد يكون المسح أفضل لجدول صغير أو تطابق واسع. وفي المقابل، مجرد ظهور عقدة فهرس لا يثبت أنها ضيّقت نطاق الصفوف بفاعلية. قارن العمل المنفذ والوقت المنقضي في ظروف متقاربة. ينفذ EXPLAIN ANALYZE كل استعلام بالفعل. استخدام EXPLAIN.

في هذا التمرين، السؤال العملي هو ما إذا كانت مساواة الفئة تقلل عدد الصفوف المرشحة بما يكفي لجعل تقييم شرط عدد الكلمات قليل الكلفة. يوفر توزيع الفئات المُنشأ حالة اختبار واضحة، لكنه لا يثبت أن الخطة نفسها هي الأفضل لمجموعة مقالات ذات توزيع مختلف.

كيف ينبغي لـ node-postgres تمرير JSONPath؟

مرّر JSONPath كاملًا كمعامل SQL واحد، وحوّله إلى النوع jsonpath. ينتمي العنصر النائب $1 في SQL والجذر $ في JSONPath إلى لغتين مختلفتين. يقبل node-postgres نص الاستعلام ومصفوفة قيم؛ ويتلقى الخادم المسار المُمرّر بوصفه قيمة المعامل. الاستعلامات.

باستخدام تثبيت حالي ومدعوم لـ Node.js، شغّل npm install pg في دليل عمل. احفظ البرنامج الكامل التالي باسم article-query.mjs. ويتطلب إعداد SQL أعلاه في قاعدة البيانات نفسها. أمر تثبيت الحزمة مأخوذ من مقدمة 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();
}

شغّل node article-query.mjs بعد إعداد متغيرات بيئة اتصال قاعدة البيانات للقاعدة المؤقتة. يقرأ Client متغيرات اتصال PostgreSQL مثل PGHOST وPGPORT وPGDATABASE وPGUSER. ويعرض البرنامج أخطاء الاتصال وحالة غياب جدول العرض التوضيحي. الاتصال.

هنا، تنشئ JSON.stringify(category) سلسلة JSONPath حرفية. ويظل المسار الناتج يتضمن مساواة بالقيمة الحرفية \"layout\"؛ وتمرير هذا المسار عبر معامل SQL لا يستبدل المساواة بمتغير JSONPath.

إذا استخدمت jsonb_path_exists مع وسيط vars بدلًا من ذلك، فأنت تستدعي دالة بمتغيرات JSONPath مسماة. هذه واجهة تقييم موثّقة، لكنها ليست صيغة مشغّل @? المفهرسة. لعملية البحث هذه باستخدام GIN، أبقِ استعلام المشغّل الموضح أعلاه. دوال JSON ومشغّلاتها، فهرسة JSONB.

ما الذي ينبغي استخدامه عندما يكون شرط النطاق العددي هو الأهم؟

إذا كان اختيار المقالات بحسب عدد الكلمات هو المهمة الأساسية، فأظهر هذه القيمة المفردة عبر فهرس تعبير من نوع B-tree. يوثّق PostgreSQL دعم B-tree للمساواة ومقارنات النطاق المرتبة، كما تتيح فهارس التعبير فهرسة قيمة محسوبة. أنواع الفهارس، الفهارس على التعبيرات.

شغّل إعداد التهيئة والاستعلام الإضافيين التاليين على الجدول المُنشأ:

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;

يستخدم الاستعلام عمدًا التعبير نفسه المستخدم في الفهرس. ويتطلب وجود قيمة واحدة لكل مقال من words متوافقة مع النوع integer. تحقق الصفوف المُنشأة هذا الشرط؛ لكن القيم النصية الاعتباطية أو الخارجة عن النطاق قد تتسبب في فشل التحويل أثناء إنشاء الفهرس أو في عمليات الكتابة اللاحقة. هذه إعادة صياغة ليست بديلًا عامًا لمقارنات JSONPath على المصفوفات أو الأنواع المختلطة أو القيم المتعددة المطابقة.

استخدم هذا الحدّ دليلًا لاتخاذ القرار: ابدأ بـ GIN عندما تقود مساواة المستندات الانتقائية عملية البحث، وفكّر في فهرس تعبير من نوع B-tree عندما يقود نطاق قيمة مفردة ذات نوع ثابت باستمرار عملية البحث. حافظ أولًا على مجموعة النتائج المقصودة، ثم استخدم الخطة الفعلية لتحديد ما إذا كان الفهرس المختار يستحق كلفة صيانته.

المصادر