قد يمنع تحديد محتوى المقالة إجراء مسح للفهرس فقط: إذ يتعين على PostgreSQL استرجاع عمود لا يتضمنه الفهرس. يوضح هذا الدليل كيفية تشخيص هذا التغيير على جهاز Mac وقياس أثره في Node.js. متطلبات المسح للفهرس فقط.

لماذا يؤدي تغيير الأعمدة المحددة إلى تغيير الخطة؟

لنفترض وجود جدول مقالات يتضمن id وcategory وbody، بالإضافة إلى فهرس B-tree على (category, id). يمكن لاستعلام يحدد المعرّفات حسب الفئة الحصول على قيمة التصفية والنتيجة من ذلك الفهرس. إضافة body تجعل هذا الفهرس غير كافٍ لإجراء مسح للفهرس فقط. فهارس PostgreSQL منفصلة عن التخزين الرئيسي للجدول، المسمى الكومة؛ كما أن مسح الفهرس العادي يسترجع بيانات الجدول أيضًا. عمليات المسح للفهرس فقط والفهارس المغطية.

تقارن المخططة الآن طرق استرجاع صفوف الجدول المطلوبة. قد تحتفظ بـIndex Scan، أو تجمع بين Bitmap Index Scan وBitmap Heap Scan، أو تختار Seq Scan. تجمع عمليات المسح النقطية عمليات الوصول إلى الكومة حسب الموقع الفعلي. عند استرجاع عدد كبير من الصفوف المتفرقة، قد يكون مسح الجدول أقل تكلفة من جلبها كلًا على حدة. تظل خطة المسح النقطي تستخدم فهرسًا رغم أن عقدة المسح العليا تشير إلى الكومة. استخدام EXPLAIN.

تحقق أيضًا من الخطة الأصلية بعناية. قد تعرض Index Only Scan قيمة غير صفرية في Heap Fetches: إذ يحتاج PostgreSQL أحيانًا إلى زيارة صفوف الجدول للتحقق من إمكانية رؤيتها. يعتمد تجنب هذه الزيارات على خريطة الرؤية للجدول، إلى جانب وجود الأعمدة المطلوبة في الفهرس. فحوصات الرؤية لعمليات المسح للفهرس فقط.

أعد تنفيذ المقارنة على جهاز Mac

استخدم قاعدة بيانات PostgreSQL محلية قيد التشغيل وإصدارًا مثبتًا ومدعومًا حاليًا من Node.js مع npm. يستخدم المثال الأساسي ميزات متاحة في PostgreSQL 14–18؛ ويشير دليل PostgreSQL الحالي الذي جرى الرجوع إليه إلى الإصدار 18. ويتطلب تشخيص التسلسل الإضافي أدناه PostgreSQL 17 أو أحدث. توضح وثائق node-postgres توافقه مع Node.js وأمر التثبيت. وثائق PostgreSQL، تثبيت node-postgres والتوافق.

افتح Terminal واعمل داخل مجلد جديد. تنشئ هذه الأوامر المشروع وتثبت برنامج تشغيل قاعدة البيانات:

mkdir article-plan-lab
cd article-plan-lab
npm init -y
npm install pg

يستخدم البرنامج إعدادات الاتصال المحلية الموجودة لديك عبر PGHOST وPGPORT وPGDATABASE وPGUSER. اضبطها لقاعدة بيانات يمكنك الاتصال بها بالفعل. يقرأ node-postgres متغيرات البيئة هذه عند إنشاء Client من دون خيارات اتصال صريحة. الاتصال باستخدام متغيرات البيئة.

احفظ البرنامج الكامل التالي باسم article-plan.cjs. ينشئ صفوفًا اصطناعية في جدول مؤقت، ويبني فهرسًا، ويطبع خطتي التنفيذ، ثم يقيس الاستعلامين العاديين. تستخدم المقارنتان الاتصال نفسه وقيمة الفئة نفسها. يمرر إعداد الاستعلام SQL عبر text والمعامل عبر values، وفقًا لواجهة node-postgres الموثقة. إعداد الاستعلام.

const { Client } = require('pg');
const { performance } = require('node:perf_hooks');

async function main() {
  const client = new Client();
  try {
    await client.connect();
    const version = await client.query('SHOW server_version');
    console.log(version.rows[0]);

    await client.query(`CREATE TEMP TABLE article_plan_demo (
      id integer PRIMARY KEY,
      category integer NOT NULL,
      body text NOT NULL
    )`);
    await client.query(`INSERT INTO article_plan_demo
      SELECT g, g % 10, repeat('Synthetic article paragraph. ', 40)
      FROM generate_series(1, 50000) AS s(g)`);
    await client.query(`CREATE INDEX article_plan_demo_category_id
      ON article_plan_demo (category, id)`);
    await client.query('VACUUM (ANALYZE) article_plan_demo');

    const queries = [
      ['IDs', 'SELECT id FROM article_plan_demo WHERE category = $1'],
      ['Bodies', 'SELECT id, body FROM article_plan_demo WHERE category = $1']
    ];

    for (const [label, text] of queries) {
      const plan = await client.query({
        text: `EXPLAIN (ANALYZE, BUFFERS, VERBOSE) ${text}`,
        values: [3]
      });
      console.log(label);
      console.log(plan.rows.map(row => row['QUERY PLAN']).join('\n'));

      const start = performance.now();
      const result = await client.query({ text, values: [3] });
      const elapsed = performance.now() - start;
      const bodyBytes = result.rows.reduce((sum, row) =>
        sum + (row.body === undefined ? 0 : Buffer.byteLength(row.body)), 0);
      console.log({
        rows: result.rows.length,
        elapsedMs: elapsed.toFixed(2),
        bodyBytes
      });
    }
  } finally {
    await client.end();
  }
}

main().catch(error => {
  console.error(error.message);
  process.exitCode = 1;
});

شغّله من المجلد نفسه:

node article-plan.cjs

يرسل الإعداد VACUUM (ANALYZE) بوصفه أمرًا مستقلًا. ينفذ هذا الأمر التنظيف بالمكنسة ثم جمع الإحصاءات، ولا يمكن تشغيله داخل كتلة معاملة. أبقه خارج أي غلاف للمعاملات تضيفه إلى المثال. VACUUM.

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

اقرأ الخطط بترتيب مفيد

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

الملاحظة التفسير الفحص التالي
تستخدم المعرّفات Index Only Scan؛ ويستخدم المحتوى Index Scan يحتاج استعلام المحتوى إلى بيانات الجدول قارن المخازن المؤقتة وزمن الاستعلام العادي
يستخدم المحتوى Bitmap Heap Scan افحص العقدة التابعة لمعرفة ما إذا كان يستخدم فهرسًا قارن كتل الكومة والصفوف المعادة
يستخدم المحتوى Seq Scan تفضّل المخططة مسح الجدول قارن عدد الصفوف المطابقة المقدر بالفعلي
تعرض المعرّفات عددًا كبيرًا من Heap Fetches يظل تنفيذ المسح للفهرس فقط يزور الكومة تحقق مما إذا كان ذلك يعكس تغييرات حديثة في الجدول
تختلف التقديرات كثيرًا عن عدد الصفوف الفعلي قد لا تصف الإحصاءات البيانات جيدًا اجمع الإحصاءات وقارن مرة أخرى

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

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

يُفترض أن يعيد كلا الاستعلامين 5,000 صف في مجموعة البيانات هذه. يحتوي كل محتوى اصطناعي على 1,160 بايتًا من ASCII، لذا يعيد استعلام المحتوى 5,800,000 بايت من المحتوى، أي نحو 5.53 MiB، قبل احتساب المعرّفات وعبء البروتوكول. هذا حساب مبني على مدخلات العينة، وليس قياسًا لأداء قاعدة البيانات. يتيح لك الحقل bodyBytes في البرنامج التحقق من هذا الفرق.

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

لماذا قد يستغرق جلب المحتوى وقتًا أطول مما يوحي به EXPLAIN؟

قد تُضغط القيم النصية الكبيرة أو تُخزّن خارج الجدول الرئيسي عبر آلية TOAST في PostgreSQL. لذلك قد يتطلب استرجاع المدخل الرئيسي للصف وإنتاج محتواه الكامل للعميل مقادير مختلفة من العمل. الغرض من بيانات الاختبار القصيرة والمتكررة أعلاه هو توضيح تغطية الأعمدة؛ وهي لا تمثل جميع أنماط تخزين المحتوى. تخزين TOAST.

افتراضيًا، يتخلص EXPLAIN ANALYZE من نتائج الاستعلام من دون تحويلها لإرسالها. في PostgreSQL 17 والإصدارات الأحدث، أضف SERIALIZE TEXT لفحص تحويل المخرجات، بما في ذلك جلب القيم المخزنة خارج الصف عند الحاجة. أُضيف هذا الخيار في PostgreSQL 17. سلوك التسلسل في EXPLAIN، ملاحظات إصدار PostgreSQL 17.

لتجربة ذلك، استبدل سطر القالب الحالي EXPLAIN في البرنامج بهذا المقتطف وأعد تشغيل البرنامج كاملًا:

text: `EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SERIALIZE TEXT) ${text}`,

في PostgreSQL 14–16، أبقِ السطر الأصلي واستخدم مؤقت الاستعلام العادي لقياس استلام النتيجة. لا يرسل SERIALIZE بيانات النتائج إلى العميل، لذا لا يمكنه قياس النقل أو معالجة JavaScript. حدود EXPLAIN.

يجمع node-postgres عادةً صفوف النتائج في كائنات JavaScript. يغيّر الخيار rowMode: 'array' تمثيل النتيجة؛ ولا يحذف قيم المحتوى المحددة من نتيجة SQL. لذلك لا يمكنه استعادة تغطية الأعمدة التي فُقدت عند إضافة body إلى هذا الاستعلام. وضع الصفوف.

اختر تغييرًا يناسب مهمة المقالة

إذا كانت مهمة سطح المكتب تحتاج إلى قائمة معرّفات فقط، فأبقِ الإسقاط محدودًا. وإذا كانت تحتاج إلى النص الكامل للمقالات، فقيّم الزمن المنقضي الفعلي لاستعلام المحتوى وحجم النتيجة. هدف التشخيص هو تحديد العمل غير الضروري مع الحفاظ على النتيجة المطلوبة.

إذا كانت الشاشة تعرض أول 50 مقالة فقط في فئة ما، فإن الحد من النتائج المطلوبة بديل ذو معنى. في مصفوفة queries في البرنامج، استبدل نص استعلام المحتوى بهذا SQL:

SELECT id, body
FROM article_plan_demo
WHERE category = $1
ORDER BY id
LIMIT 50

يغيّر هذا المهمة عمدًا من استرجاع كل المقالات المطابقة إلى استرجاع صفحة مرتبة واحدة. عند تثبيت الفئة، يوفر فهرس (category, id) الحالي مسارًا مرشحًا بترتيب المعرّف. يمكن لفهرس B-tree مطابق أن يجعل ORDER BY مع LIMIT فعالًا، إذ يمكن للتنفيذ التوقف بعد العثور على عدد كافٍ من الصفوف. تظل الخطة المختارة بحاجة إلى الفحص. الفهارس وORDER BY.

لا تتعامل مع INCLUDE (body) باعتباره إصلاحًا تلقائيًا. إذ تكرر الأعمدة المضمّنة بيانات الجدول، وتزيد حجم الفهرس، وقد تؤدي إلى فشل الإدراج إذا تجاوزت مجموعة الفهرس حد حجمها. قد تكون أعمدة الحمولة الصغيرة مرشحة مفيدة لتجارب مقاسة. أُضيف INCLUDE في PostgreSQL 11؛ وهو غير متاح في الإصدارات الأقدم. محاذير الفهارس المغطية، ملاحظات إصدار PostgreSQL 11.

اختتم التحقيق بثلاث نتائج ملموسة: هل أدى تحديد المحتوى إلى فقدان أهلية المسح للفهرس فقط، وهل عملية المسح الجديدة ذات أداء مقبول، وكم من الوقت يستغرق وصول النتيجة الكاملة إلى Node.js. توضح لك هذه النتائج ما إذا كان ينبغي الإبقاء على الخطة، أو طلب عدد أقل من المقالات، أو اختبار فهرس مختلف للاستعلام الفعلي.

المصادر