قد يستخدم PostgreSQL فهرسًا مع id < 11 وفحصًا تسلسليًا مع id > 11 لأن الشرط الأول يطابق عددًا قليلًا جدًا من الصفوف، بينما يطابق الثاني الجدول بأكمله تقريبًا. يدعم كلا العاملين فهارس B-tree؛ ويقارن المخطط التكاليف المقدرة.

يساعد هذا الدليل مطورًا يعمل على Mac على إعادة إنتاج هذا الاختيار باستخدام صفوف مقالات اصطناعية، وقراءة الخطط، والتحقق مما إذا كان الفرق يستدعي الانتباه. وهو يستند إلى وثائق PostgreSQL 18 الحالية التي جرى استرجاعها، وإلى وثائق node-postgres الحالية. تحقّق من إصدار الخادم قبل مقارنة تفاصيل الخطط؛ فالأمثلة لا تضمن تطابق الخطط بين الإصدارات أو الأجهزة. PostgreSQL: استخدام EXPLAIN

لماذا قد يؤدي الفهرس نفسه إلى اختيارين مختلفين للفحص؟

يدعم فهرس B-tree مقارنات أصغر من وأكبر من. تغيير اتجاه المقارنة لا يجعل الفهرس غير قابل للاستخدام. ما يتغير هو نطاق القيم، وبالتالي عدد الصفوف التي يتوقع المخطط استرجاعها. PostgreSQL: أنواع الفهارس

لنفترض وجود جدول توضيحي يحتوي بالضبط على 100,000 صف مقالات، بمعرّفات صحيحة متتالية من 1 إلى 100,000:

الشرط الصفوف المطابقة نسبة الجدول
id < 11 10 0.01%
id > 11 99,989 99.989%
id = 11 1 0.001%

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

يجلب فحص الفهرس العادي في PostgreSQL مدخلات من الفهرس والصفوف المقابلة من التخزين الرئيسي للجدول، المسمى الكومة (heap). وقد تتوزع صفوف الكومة هذه على صفحات عديدة. عندما تكون هناك حاجة إلى معظم الصفحات، قد تكون قراءة الجدول مباشرة أقل تكلفة من اجتياز الفهرس وجلب الصفوف التي يشير إليها. PostgreSQL: الفحوصات التي تستخدم الفهرس فقط والفهارس المغطية

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

كيف تعيد إنتاج المقارنة على Mac؟

تحتاج إلى اتصال PostgreSQL موجود، وإلى توفر الأمر psql في Terminal على جهاز Mac. التثبيت خارج نطاق هذا التمرين. افتح اتصال قاعدة البيانات المعتاد باستخدام psql؛ فهو يقبل إعدادات الاتصال ويوفر موجه SQL تفاعليًا. نفّذ الكتل التالية في جلسة جديدة واحدة. PostgreSQL: psql

ينشئ الإعداد جدولًا مؤقتًا لا يحتوي إلا على نصوص مقالات اصطناعية. تختفي الجداول المؤقتة عند انتهاء الجلسة. ينشئ المفتاح الأساسي الصحيح العادي تلقائيًا فهرس B-tree فريدًا، لذا لا حاجة إلى فهرس ثانٍ على id. PostgreSQL: CREATE TABLE

SHOW server_version;

CREATE TEMP TABLE article_scan_demo (
    id integer PRIMARY KEY,
    title text NOT NULL,
    body text NOT NULL
);

INSERT INTO article_scan_demo (id, title, body)
SELECT
    g,
    'Article ' || g,
    repeat('Sample article text. ', 20)
FROM generate_series(1, 100000) AS s(g);

ANALYZE article_scan_demo;

SELECT
    count(*) AS total_rows,
    count(*) FILTER (WHERE id < 11) AS below_11,
    count(*) FILTER (WHERE id > 11) AS above_11
FROM article_scan_demo;

تتضمن generate_series الصحيحة في PostgreSQL كلا الطرفين، وتكون الخطوة الافتراضية واحدًا. لذا ينبغي أن يعيد استعلام العد 100,000 صف إجمالًا، و10 صفوف أقل من 11، و99,989 صفًا أكبر من 11. PostgreSQL: الدوال التي تعيد مجموعات

نفّذ ANALYZE بعد تحميل الصفوف حتى تتوفر للمخطط إحصاءات عن الجدول المعبأ. يجمع هذا الأمر الإحصاءات؛ وله غرض مختلف عن خيار قياس التنفيذ في EXPLAIN ANALYZE. PostgreSQL: ANALYZE

بعد ذلك، قارن استعلامي الصفوف الكاملة:

EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT * FROM article_scan_demo WHERE id < 11;

EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT * FROM article_scan_demo WHERE id > 11;

تنفذ هذه الأوامر كل استعلام وتعرض خطته وأعداد الصفوف الفعلية، من دون إعادة جميع صفوف المقالات المحددة. يضيف BUFFERS نشاط الكتل؛ ويعطّل TIMING OFF توقيت العقد الفردية. يفيد ذلك في دراسة التنفيذ، لكنه لا يقيس الوقت اللازم لإرسال كل بيانات المقالات المطابقة إلى تطبيق سطح المكتب. PostgreSQL: EXPLAIN

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

ما أجزاء الخطة التي تفسر القرار؟

ابدأ بعقدة الفحص، وrows المقدرة لها، وrows الفعلية. في الاستعلام الأول، ابحث عن Index Scan مع Index Cond، أو عن Bitmap Index Scan أسفل Bitmap Heap Scan. وفي الثاني، ابحث عن Seq Scan مع Filter. ما تزال الخطة النقطية تستخدم فهرسًا؛ فهي ترتب مواقع الصفوف المطابقة قبل جلب صفحات الكومة. PostgreSQL: استخدام EXPLAIN

ثم تحقّق من الملاحظات التالية:

الملاحظة في هذا المثال ما ينبغي التحقق منه
التقديرات قريبة من 10 و99,989 صفًا فعليًا تفسر أعداد المطابقات المختلفة خيارات الفحص المختلفة.
الصفوف المقدرة تختلف كثيرًا عن الصفوف الفعلية حدّث الإحصاءات قبل تغيير إعدادات المخطط.
يستخدم كلا الشرطين فحصًا تسلسليًا في عينة أصغر بكثير قد يجعل حجم الجدول الفحص المباشر أقل تكلفة.
تظهر خطة نقطية قارن تقديرات صفوفها وعمل الكومة بوصفها بديلًا آخر يستخدم الفهرس.

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

إذا بدت التقديرات مفاجئة، نفّذ استعلام التشخيص التالي في الجلسة نفسها:

SELECT
    schemaname,
    n_distinct,
    histogram_bounds,
    correlation
FROM pg_stats
WHERE schemaname = (
    SELECT nspname
    FROM pg_namespace
    WHERE oid = pg_my_temp_schema()
)
  AND tablename = 'article_scan_demo'
  AND attname = 'id';

يصف المدرج التكراري توزيع القيم المستخدم لتقدير النطاق. ويصف الارتباط مدى قرب الترتيب المنطقي للعمود من ترتيب الصفوف الفعلي؛ وقد تجعل القيم القريبة من أي من الطرفين الوصول إلى الكومة عبر الفهرس أقل تكلفة في نظر المخطط. لا يحدد أي من الحقلين نسبة عامة يجب عندها على PostgreSQL التحول بين أنواع الفحص. PostgreSQL: pg_stats

كيف تقارن الاستعلامات عبر node-postgres؟

يرسل node-postgres نص الاستعلام وقيم المعاملات إلى PostgreSQL، حيث يحدث التخطيط. تقبل واجهة الاستعلام SQL متبوعًا بمصفوفة قيم، لذا يمكن أن تستخدم المقارنتان قيمة الحد نفسها، وهي 11. node-postgres: الاستعلامات

لإعادة إنتاج مستقلة باستخدام Node.js، استخدم تثبيتًا عاملًا لـ Node.js/npm وإعدادات اتصال PostgreSQL الحالية لديك. في مجلد مشروع جديد، نفّذ npm init -y، ثم npm install pg. احفظ البرنامج الكامل أدناه باسم compare-scans.mjs وشغّل node compare-scans.mjs. يستخدم العميل إعدادات بيئة PostgreSQL مثل PGHOST وPGPORT وPGDATABASE؛ استخدم الإعدادات الخاصة بقاعدة بياناتك المحلية الحالية. node-postgres: الاتصال

import pg from 'pg';

const client = new pg.Client();

try {
  await client.connect();

  await client.query(`
    CREATE TEMP TABLE article_scan_demo (
      id integer PRIMARY KEY,
      title text NOT NULL,
      body text NOT NULL
    )
  `);

  await client.query(`
    INSERT INTO article_scan_demo (id, title, body)
    SELECT
      g,
      'Article ' || g,
      repeat('Sample article text. ', 20)
    FROM generate_series(1, 100000) AS s(g)
  `);

  await client.query('ANALYZE article_scan_demo');

  const queries = [
    'EXPLAIN (ANALYZE, BUFFERS, TIMING OFF) SELECT * FROM article_scan_demo WHERE id < $1',
    'EXPLAIN (ANALYZE, BUFFERS, TIMING OFF) SELECT * FROM article_scan_demo WHERE id > $1'
  ];

  for (const text of queries) {
    const result = await client.query(text, [11]);
    console.log(text);
    console.log(
      result.rows.map(row => row['QUERY PLAN']).join('\n')
    );
  }
} finally {
  await client.end();
}

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

هل يمكن للتعليمات المعدّة المسماة تغيير المقارنة؟

تؤدي إضافة name إلى إعداد استعلام node-postgres إلى إنشاء تعليمة معدّة مسماة على كل اتصال. ويضيف ذلك تمييزًا آخر: يمكن لـ PostgreSQL استخدام خطة مخصصة تستند إلى قيم المعاملات، أو خطة عامة مشتركة بين عمليات التنفيذ. node-postgres: الاستعلامات، PostgreSQL: PREPARE

وفق سياسة PostgreSQL 18 التلقائية الافتراضية، تستخدم عمليات التنفيذ الخمس الأولى خططًا مخصصة. ثم يقارن PostgreSQL التكلفة المقدرة لخطة عامة بمتوسط تكلفة الخطط المخصصة؛ ولا يتحول تلقائيًا إلى خطة عامة عند التنفيذ السادس. PostgreSQL: PREPARE

إذا تصرفت تعليمة مسماة في تطبيقك على نحو مختلف عن اختبار SQL حرفي، فافحص تلك التعليمة على الاتصال نفسه باستخدام EXPLAIN EXECUTE. تعرض الخطة العامة رموز المعاملات، بينما تستبدل الخطة المخصصة بها القيم المقدمة. أبقِ معاملي المقارنة في تعليمتين مسماتين منفصلتين لأن نص SQL مختلف. PostgreSQL: PREPARE

متى ينبغي تغيير الاستعلام أو إعدادات المخطط؟

حافظ أولًا على المهمة الفعلية للاستعلام. فتغيير SELECT * إلى SELECT id يغيّر البيانات التي يجب على PostgreSQL استرجاعها. وعندما تتوفر كل الأعمدة المطلوبة في فهرس مناسب، يصبح الفحص الذي يستخدم الفهرس فقط ممكنًا. كما يعتمد تجنب جلب الصفوف من الكومة على خريطة الرؤية، لذا لا تضمن خطة الفحص باستخدام الفهرس فقط عدم الوصول إلى الكومة مطلقًا. PostgreSQL: الفحوصات التي تستخدم الفهرس فقط والفهارس المغطية

إذا كانت المهمة تتطلب كل مقالة مطابقة، فاحتفظ بهذا الشرط أثناء مقارنة الخطط. أما إذا كانت تحتاج إلى مجموعة فرعية صغيرة مرتبة فقط، فاختبر استعلام ORDER BY وLIMIT الفعلي بدلًا من ذلك؛ إذ يمكن للترتيب والتوقف المبكر تغيير الخطة المفضلة. PostgreSQL: استخدام EXPLAIN

بعد التحقق من الإحصاءات، يمكنك مقارنة خطة بديلة في جلسة SQL الحالية:

BEGIN;
SET LOCAL enable_seqscan = off;

EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT * FROM article_scan_demo WHERE id > 11;

ROLLBACK;

يثبط هذا الإعداد الفحوصات التسلسلية، لكنه لا يمنعها تمامًا. استخدم النتيجة للمقارنة التشخيصية. كما ينمذج PostgreSQL الوصول التسلسلي وغير التسلسلي إلى الصفحات من خلال seq_page_cost وrandom_page_cost. قد يؤدي تغيير قيمهما النسبية إلى تغيير خيارات الخطط، لكن الوثائق تنصح بتقييم إعدادات التكلفة عبر عبء الاستعلامات بدلًا من الاكتفاء ببضعة اختبارات. PostgreSQL: تخطيط الاستعلامات

في هذا الجدول الاصطناعي، المقارنة الحاسمة هي بين 10 صفوف مطابقة و99,989 صفًا. تحقّق من الاستعلام المحدد، وحدّث الإحصاءات، وقارن الصفوف المقدرة والفعلية، وافحص سلوك الخطط المعدّة عند الاقتضاء. توضح هذه الخطوات ما إذا كان الفحص التسلسلي مناسبًا للعمل المطلوب، وتوفر أدلة لأي تغيير لاحق.

المصادر