الدرس 5 · الوحدة 3: تحسين الأداء وتنفيذ استعلامات محسّنة

كيف ينفّذ PostgreSQL الاستعلام؟ EXPLAIN

قبل أن تحسّن استعلامًا، اعرف كيف ينفّذه المحرك فعلًا. ⏱ نحو 40 دقيقة.

بعد هذا الدرس ستستطيع أن:

تشرح مراحل تنفيذ الاستعلام ودور المخطِّط.
تقرأ خطة EXPLAIN: العقدة، والتكلفة، والصفوف المتوقعة.
تميّز Seq Scan من Index Scan و Bitmap Scan.
تقرأ خطة الربط والتجميع من الداخل إلى الخارج.

١من النص إلى خطة التنفيذ

حين ترسل استعلامًا، لا ينفّذه PostgreSQL مباشرة. يمر بثلاث مراحل: التحليل (هل الصياغة صحيحة؟)، ثم المخطِّط Planner الذي يقارن طرقًا عدة للوصول إلى البيانات ويختار أرخصها، ثم التنفيذ.

مراحل تنفيذ الاستعلام: EXPLAIN يُريك ناتج المخطِّط دون تنفيذ.

الأمر EXPLAIN قبل الاستعلام يعرض الخطة التي اختارها المخطِّط. نستخدم في دروس الأداء قاعدة «متجر الريان: بيانات كبيرة»: 3000 عميل و20 ألف طلب.

٢قراءة الخطة: المسح الكامل

query.sql

EXPLAIN SELECT * FROM orders WHERE customer_id = 42;

خطة التنفيذ (EXPLAIN)

Seq Scan on orders (cost=0.00..373.00 rows=7 width=18)
Filter: (customer_id = 42)

Seq Scan يعني أن المحرك قرأ الجدول كله صفًّا صفًّا (20 ألف صف) ليجد طلبات عميل واحد. وسطر Filter هو الشرط الذي طبّقه على كل صف.

الجزء	معناه
`cost=0.00..373.00`	تكلفة تقديرية (وحدات نسبية): قبل أول صف .. حتى آخر صف. نقارن بها خطتين، لا نقرؤها بالثواني
`rows=7`	عدد الصفوف الذي يتوقعه المخطِّط من الإحصاءات
`width=18`	متوسط حجم الصف بالبايت

٣الوصول بالفهرس

البحث بالمفتاح الأساسي مختلف: كل مفتاح أساسي له فهرس تلقائي، فيقفز المحرك إلى الصف مباشرة:

query.sql

EXPLAIN SELECT * FROM orders WHERE id = 42;

خطة التنفيذ (EXPLAIN)

Index Scan using orders_pkey on orders (cost=0.29..8.30 rows=1 width=18)
Index Cond: (id = 42)

Index Scan using orders_pkey: استخدم فهرس المفتاح الأساسي، والتكلفة نحو 8 بدل 373.

والعمود customer_id لا فهرس عليه. لننشئ واحدًا ونرى كيف تتغير الخطة:

query.sql

CREATE INDEX idx_orders_customer ON orders (customer_id); EXPLAIN SELECT * FROM orders WHERE customer_id = 42;

✓ أُنشئ الفهرس

خطة التنفيذ (EXPLAIN)

Bitmap Heap Scan on orders (cost=4.34..27.42 rows=7 width=18)
Recheck Cond: (customer_id = 42)
-> Bitmap Index Scan on idx_orders_customer (cost=0.00..4.34 rows=7 width=0)
Index Cond: (customer_id = 42)

Bitmap Index Scan يجد مواقع الصفوف من الفهرس، ثم Bitmap Heap Scan يقرأ تلك الصفوف فقط. التكلفة انخفضت من 373 إلى نحو 27.

العقدة في الخطة	المعنى	مناسبة لـ
`Seq Scan`	قراءة الجدول كله	جداول صغيرة، أو حين تُطلب نسبة كبيرة من الصفوف
`Index Scan`	القفز بالفهرس إلى الصفوف	صف واحد أو صفوف قليلة
`Bitmap Index/Heap Scan`	جمع مواقع الصفوف من الفهرس ثم قراءتها مرتبة	عدد متوسط من الصفوف
`Index Only Scan`	الإجابة من الفهرس وحده دون الجدول	حين تكون كل الأعمدة المطلوبة في الفهرس

٤خطة الربط والتجميع

الخطة شجرة: تُقرأ من الداخل إلى الخارج. السطور المزاحة (بسهم ->) تُنفَّذ أولًا وتغذي ما فوقها:

query.sql

EXPLAINSELECT c.city, count(*)FROM customers c JOIN orders o ON o.customer_id = c.idGROUP BY c.city;

خطة التنفيذ (EXPLAIN)

HashAggregate (cost=572.08..572.16 rows=8 width=18)
Group Key: c.city
-> Hash Join (cost=96.50..472.08 rows=20000 width=10)
Hash Cond: (o.customer_id = c.id)
-> Seq Scan on orders o (cost=0.00..323.00 rows=20000 width=4)
-> Hash (cost=59.00..59.00 rows=3000 width=14)
-> Seq Scan on customers c (cost=0.00..59.00 rows=3000 width=14)

اقرأها من الأسفل: مسح العملاء وبناء جدول تجزئة Hash، ومسح الطلبات، ثم Hash Join يربطهما، ثم HashAggregate يجمّع حسب المدينة.

توقّع وتحقّق

ما الذي يعرضه EXPLAIN؟

Seq Scan يعني:

من أين تبدأ قراءة الخطة؟

أي عمود له فهرس تلقائيًّا؟

وفي PostgreSQL لا يُفهرس المفتاح الأجنبي تلقائيًّا.

الخلاصة

المخطِّط يقارن الطرق ويختار الأرخص؛ EXPLAIN يُريك اختياره.
Seq Scan يقرأ الجدول كله، و Index Scan يقفز بالفهرس.
cost وحدة نسبية للمقارنة، و rows تقدير من الإحصاءات.
الخطة شجرة تُقرأ من الداخل إلى الخارج.
المفتاح الأساسي مفهرس تلقائيًّا، والأجنبي لا.

تحدٍّ برمجي: اجعل البحث يستخدم فهرسًا

متوسط

استعلام يبحث عن بنود منتج معيّن في order_items يقرأ الجدول كله (40 ألف صف). أنشئ فهرسًا يجعل المخطِّط يستخدمه، ثم اعرض الخطة.

المطلوب:

فهرس على order_items (product_id).
ثم EXPLAIN SELECT * FROM order_items WHERE product_id = 7;
الخطة تحتوي Index لا Seq Scan.

👀 اعرض الناتج المطلوب

خطة التنفيذ (EXPLAIN)

Bitmap Heap Scan on order_items (cost=5.84..259.71 rows=200 width=17)
Recheck Cond: (product_id = 7)
-> Bitmap Index Scan on idx_items_product (cost=0.00..5.79 rows=200 width=0)
Index Cond: (product_id = 7)

✓ التحقق الآلي من حلّك:

الكود يعمل بلا أخطاء
يوجد فهرس على product_id
المخطِّط يستخدم الفهرس في البحث
عرض الخطة بـ EXPLAIN

💡 تلميح 1

الصيغة: CREATE INDEX اسم ON جدول (عمود);

💡 تلميح 2

شغّل الكود وانظر الخطة: تظهر Bitmap Index Scan.

حاول بنفسك أولًا؛ للمسألة أكثر من حلّ صحيح.

solution.sql

CREATE INDEX idx_items_product ON order_items (product_id); EXPLAIN SELECT * FROM order_items WHERE product_id = 7;

انتقل إلى تقييم الدرساختيار من متعدد، وصح وخطأ، وأكمل الفراغ، وتتبّع الناتج.ابدأ التقييم ←

📝 معلم؟ أرسل ملاحظة للمؤلف عن هذا الدرس