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

ما الذي ينبغي قياسه معًا؟

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

استخدم EXPLAIN (ANALYZE, BUFFERS, TIMING OFF, FORMAT JSON). ينفّذ الخيار ANALYZE الاستعلام؛ ويعطّل TIMING OFF قياسات الساعة لكل عقدة مع الاحتفاظ بوقت تنفيذ العبارة كاملة. تسهّل المخرجات القابلة للقراءة آليًا جمع القياسات المتكررة. PostgreSQL: EXPLAIN

اقرأ هذه القياسات معًا:

القياس ما الذي يخبرك به
Execution Time مدة التنفيذ على الخادم، باستثناء التخطيط
Planning Time الوقت المستغرق في إنشاء الخطة وتحسينها
Shared Hit Blocks عمليات الوصول إلى المخازن المؤقتة التي لُبّيت من ذاكرة PostgreSQL المؤقتة المشتركة
Shared Read Blocks الكتل التي أُدخلت إلى المخازن المؤقتة المشتركة
مجموع مرات الإصابة والقراءة مقارنة مفيدة لحجم الوصول إلى المخازن المؤقتة المشتركة
Temp Read Blocks وTemp Written Blocks نشاط ملفات العمل المؤقتة، مثل تجاوز عملية فرز سعة الذاكرة

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

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

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

جهّز تجربة Mac وتحقق من حدود الإصدارات

ابدأ بقاعدة بيانات PostgreSQL محلية قيد التشغيل وNode.js مع إتاحة npm في Terminal. يستهدف هذا الدليل PostgreSQL 18 ويستخدم Node.js 22 أو 24. تسرد وثائق node-postgres إصدارات Node.js هذه على أنها متوافقة، وتوثق التثبيت باستخدام npm install pg. node-postgres

يظهر أمر المقارنة أيضًا في وثائق PostgreSQL 16 و17. يضمّن PostgreSQL 18 مخرجات المخازن المؤقتة تلقائيًا مع EXPLAIN ANALYZE؛ وإضافة BUFFERS صراحةً توضح المقصود عبر هذه الإصدارات. لا تفترض وجود إعدادات اختيارية أحدث في الخوادم الأقدم. PostgreSQL 16: EXPLAIN، PostgreSQL 17: EXPLAIN، ملاحظات إصدار PostgreSQL 18

في دليل عمل جديد، ثبّت المشغّل:

mkdir pg-plan-comparison
cd pg-plan-comparison
npm init -y
npm install pg

استخدم قاعدة تدريب محلية موجودة يمكن لاتصال PostgreSQL المعتاد فتحها. يستخدم البرنامج new Client()، الذي يقرأ متغيرات بيئة الاتصال نفسها التي يقرأها psql. على سبيل المثال، إذا كان اسم قاعدة البيانات plan_lab، فشغّل البرنامج باستخدام PGDATABASE=plan_lab. أضف إعدادات PGHOST أو PGPORT الموجودة لديك عند الحاجة. node-postgres: الاتصال

ينشئ البرنامج 100,000 مقالة اصطناعية داخل معاملة، ويقارن حالتين للمخطط، ثم يتراجع عن المعاملة. يستخدم جدولًا عاديًا حتى يظهر الفحص ضمن المخازن المؤقتة المشتركة. توثّق PostgreSQL أن ROLLBACK يتخلص من تحديثات المعاملة. إذا كان اسم جدول العرض التوضيحي موجودًا بالفعل، يفشل الإنشاء بدلًا من استبداله. PostgreSQL: ROLLBACK

نفّذ مقارنة كاملة قبل التغيير وبعده

احفظ البرنامج الكامل التالي باسم compare-plans.mjs. ينشئ البرنامج مدخلاته بنفسه، وينفّذ مرة إحماء غير مسجلة لكل مرحلة، ويحتفظ بسبع مرات تشغيل مقاسة، ويطبع أول خطة مقاسة لكل مرحلة. يستخدم مولّد الصفوف الدالة الموثقة في PostgreSQL، generate_series. PostgreSQL: الدوال المعيدة للصفوف

import pg from 'pg';

const { Client } = pg;
const client = new Client();
const query = `
  SELECT article_id, title, published_at
  FROM plan_compare_articles_demo
  WHERE category_id = $1
  ORDER BY published_at DESC, article_id DESC
  LIMIT $2
`;
const values = [7, 50];
const explain = `
  EXPLAIN (ANALYZE, BUFFERS, TIMING OFF, FORMAT JSON)
  ${query}
`;
const median = values =>
  [...values].sort((a, b) => a - b)[Math.floor(values.length / 2)];

async function measure(label) {
  await client.query(explain, values);
  const runs = [];
  let firstPlan;
  for (let run = 1; run <= 7; run++) {
    const result = await client.query(explain, values);
    const document = result.rows[0]['QUERY PLAN'][0];
    const root = document.Plan;
    firstPlan ??= document;
    const hits = root['Shared Hit Blocks'] ?? 0;
    const reads = root['Shared Read Blocks'] ?? 0;
    runs.push({
      phase: label,
      run,
      execution_ms: document['Execution Time'],
      planning_ms: document['Planning Time'],
      shared_hit: hits,
      shared_read: reads,
      shared_accesses: hits + reads,
      temp_read: root['Temp Read Blocks'] ?? 0,
      temp_written: root['Temp Written Blocks'] ?? 0
    });
  }
  console.table(runs);
  console.log(label, 'median execution ms:',
    median(runs.map(run => run.execution_ms)));
  console.dir(firstPlan, { depth: null });
}

try {
  await client.connect();
  console.log((await client.query('SHOW server_version')).rows[0]);
  await client.query('BEGIN');
  await client.query(`
    CREATE TABLE plan_compare_articles_demo (
      article_id integer PRIMARY KEY,
      category_id integer NOT NULL,
      title text NOT NULL,
      published_at timestamp NOT NULL,
      body text NOT NULL
    )
  `);
  await client.query(`
    INSERT INTO plan_compare_articles_demo
      (article_id, category_id, title, published_at, body)
    SELECT g, g % 20, 'Article ' || g,
           timestamp '2025-01-01' + g * interval '1 minute',
           repeat('Synthetic article text. ', 12)
    FROM generate_series(1, 100000) AS series(g)
  `);
  await client.query('ANALYZE plan_compare_articles_demo');
  const before = await client.query(query, values);
  await measure('before index');

  await client.query(`
    CREATE INDEX plan_compare_articles_demo_list_idx
    ON plan_compare_articles_demo
      (category_id, published_at DESC, article_id DESC)
  `);
  const after = await client.query(query, values);
  if (JSON.stringify(before.rows) !== JSON.stringify(after.rows)) {
    throw new Error('Result mismatch between schema states');
  }
  await measure('after index');
} catch (error) {
  console.error(error);
  process.exitCode = 1;
} finally {
  try {
    await client.query('ROLLBACK');
  } finally {
    await client.end();
  }
}

شغّله من الدليل نفسه، مع استبدال اسم قاعدة البيانات الفعلي لقاعدة التدريب لديك:

PGDATABASE=plan_lab node compare-plans.mjs

يرسل البرنامج SQL وقيم المعاملات كلًا على حدة عبر client.query(text, values). يقرأ الخطة من العمود المعاد المسمى QUERY PLAN؛ إذ يربط node-postgres أعمدة النتائج بخصائص الكائنات ويحلل JSON الخاص بـ PostgreSQL إلى كائنات JavaScript. node-postgres: الاستعلامات، node-postgres: pg.Result، node-postgres: أنواع البيانات

يجمع الأمر المستقل ANALYZE إحصاءات الجدول للمخطط. وهو يختلف عن خيار ANALYZE الذي يقيس التنفيذ داخل EXPLAIN. هنا، يمنح جمع الإحصاءات بعد تحميل الصفوف الاصطناعية كلتا المرحلتين المعلومات الأولية نفسها عن الجدول. PostgreSQL: ANALYZE

تم التحقق من حسابات الوسيط وإجماليات المخازن المؤقتة في JavaScript باستخدام مدخلات اصطناعية حتمية. لم تُنفّذ تجربة قاعدة البيانات الكاملة أثناء الإعداد لأن PostgreSQL لم يكن متاحًا في بيئة البحث؛ ولا تتضمن المقالة نتائج قياس أداء فعلية.

ما الذي ينبغي أن يتغير في الخطتين؟

يضع الفهرس المرشح الفئة الخاضعة لشرط المساواة أولًا، تليها أعمدة الترتيب المطلوبة. توثّق PostgreSQL أن شروط المساواة على أعمدة B-tree الرائدة تحدد نطاق الفهرس الذي سيُمسح. وهذا يجعل هذا الفهرس خيارًا مرجحًا للاستعلام. PostgreSQL: الفهارس متعددة الأعمدة

توقّع وجود فحص وفرز قبل الفهرسة، ثم فحص فهرس مرتب أسفل الحد بعدها. هذه توقعات للتحقق منها في مخرجاتك، وليست أشكالًا مضمونة للخطة. يمكن لفهرس B-tree مطابق توفير الصفوف مرتبة مباشرةً، كما يمكن للترتيب المصحوب بحد أن يتجنب معالجة بقية الصفوف المطابقة. PostgreSQL: الفهارس وORDER BY

افحص ثلاثة أشياء في الخطط المطبوعة:

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

هذه مقارنة مرحلية في حالة ذاكرة مؤقتة دافئة. يؤثر إدراج الصفوف واستعلامات التحقق من الصحة ومرات الإحماء وإنشاء الفهرس جميعها في حالة ذاكرة التخزين المؤقت. أبقِ حمل عمل Mac نفسه وإعدادات قاعدة البيانات كما هي خلال المرحلتين. احتفظ بالمشاهدات السبع الفردية حتى تبقى أي مرة تشغيل بطيئة على نحو غير معتاد ظاهرة؛ فالوسيط وحده قد يخفي التباين.

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

كيف تميّز بين تقليل العمل ودفء المخازن المؤقتة؟

الأرقام التالية مدخلات توضيحية وليست مخرجات مسجلة. وهي تبيّن سبب ضرورة النظر إلى القراءات والإصابات والوقت معًا:

تشغيل توضيحي التنفيذ الإصابات المشتركة القراءات المشتركة الإصابات + القراءات
A، الملاحظة الأولية 12 ms 900 100 1,000
A، الملاحظة المتكررة 10 ms 1,000 0 1,000
B، الخطة المرشحة 3 ms 80 0 80

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

استخدم أداة اتخاذ القرار هذه إذا اختلفت نتائجك:

النمط المرصود التفسير والخطوة التالية
وقت أقل وعمليات وصول مشتركة أقل دليل على التحسن لحمل العمل المختبَر؛ كرر للتحقق من الاتساق
وقت أقل وقراءات أقل وإجمالي عمليات وصول متقارب قد تفسر حالة ذاكرة التخزين المؤقت التغيير؛ قارن التكرارات بعد الإحماء
عمليات وصول أقل لكن وقت مماثل أو أطول افحص الفرز والتعبيرات وتباين التوقيت قبل الاختيار
لا قراءات لكن إصابات كثيرة يظل التنفيذ المخزّن مؤقتًا يجري عمليات وصول إلى المخازن المؤقتة؛ افحص العقد ذات النشاط المرتفع
ظهور قراءات أو كتابات مؤقتة افحص عملية العمل التي أنتجتها قبل تغيير الإعدادات

تجنّب جعل نسبة إصابات ذاكرة التخزين المؤقت المقياس الحاسم. في المثال التوضيحي، تبلغ نسبة إصابات المخازن المؤقتة المشتركة 100% في كل من A بعد الإحماء وB، لكن أعداد عمليات الوصول تختلف كثيرًا. احتفظ بالأعداد الخام وحجم النتائج بجانب الأوقات.

كيف ترتبط هذه القياسات بسرعة التطبيق؟

لا يشمل Execution Time على الخادم التخطيط، كما أن EXPLAIN ANALYZE العادي لا يشمل إرسال النتائج، ولا تحويل المخرجات افتراضيًا. وتضيف أدوات القياس نفسها عبئًا إضافيًا. لذلك يقارن البرنامج تنفيذ قاعدة البيانات مع تفعيل أدوات القياس، ولا يقيس الوقت الكامل اللازم لعرض قائمة المقالات. PostgreSQL: استخدام EXPLAIN

لفحص تحويل المخرجات بشكل منفصل في PostgreSQL 17 أو 18، أضف SERIALIZE TEXT إلى خيارات الشرح نفسها. لا يذكر PostgreSQL 16 هذا الخيار. يظل التسلسل لا يرسل صفوف نتائج الاستعلام، ولذلك لا يمكنه قياس التسليم عبر الشبكة. أبقِ إعدادات التسلسل متطابقة بين مرات التشغيل المقارنة. PostgreSQL 17: EXPLAIN، PostgreSQL 16: EXPLAIN

لإجراء قياس على مستوى التطبيق، قِس الزمن المستغرق لاستدعاء المقالة العادي SELECT حتى اكتمال استدعاء المشغّل المنتظر، مع إنشاء الاتصال مسبقًا. سجّل ذلك كقياس منفصل للوقت المنقضي لدى العميل. احتفظ بإعداد الاستعلام الفعلي للتطبيق كما هو: إن إضافة name إلى استعلام node-postgres تفعّل سلوك العبارة المحضّرة لكل اتصال وتغيّر شروط المقارنة. node-postgres: الاستعلامات

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

المصادر