EXPLAIN ANALYZE في PostgreSQL: اكتشف سبب بطء الاستعلام في 5 دقايق بدل تخمين ساعتين

لو endpoint بياخد 12 ثانية وأنت متأكد إن الـ DB هي السبب، تخمين الإصلاح (ضيف index هنا، اقسم الـ query، غيّر JOIN) بياكل ساعتين بدون نتيجة مضمونة. EXPLAIN ANALYZE بيريك بالظبط في أي عقدة الزمن بيضيع، وبيخلي قرار الإصلاح في 5 دقايق بأرقام مش بحدس.

المشكلة باختصار

السيناريو المتكرر: dashboard بيعرض query بطيء، الـ engineer بيفتح pgAdmin، يضيف index عشوائي، يجرّب يكسر الـ JOIN، يحوّل الترتيب لـ application layer. الزمن بيقل قليلًا أو يزيد. اللي بيحصل فعلًا واحد من ثلاثة: الـ query planner اختار خطة استعلام مش متوقعة بسبب إحصاءات قديمة، الـ work_mem أصغر من اللازم فالعمليات بتسقط على القرص، أو فيه bloat في الجدول من UPDATEs كتيرة. EXPLAIN ANALYZE بيكشف الثلاثة في مخرجات واحدة.

تمثيل بسيط جداً قبل التعريف العلمي

تخيّل مديرة مكتبة محتاجة تعرف كم كتاب فيه فصل عن الكيمياء. لو طبعت قائمة كل كتب المكتبة بفصول كل واحد ومرّت عليهم، ده Sequential Scan. لو راحت لكاتالوج موضوعات عنده مؤشر للكيمياء وقعدت تطلع الكتب، ده Index Scan. القرار بين الاتنين بياخده الـ planner تلقائيًا بناءً على إحصاءات: لو 70% من الكتب فيها كيمياء، السكان أسرع. لو 2% بس، الـ Index أسرع. EXPLAIN ANALYZE بيعرض القرار اللي اتخذه فعلاً والوقت الفعلي لكل خطوة.

التعريف العلمي

EXPLAIN في PostgreSQL بيعرض شجرة العمليات (plan tree) اللي الـ planner اختارها لتنفيذ الاستعلام. كل عقدة تمثّل عملية: Scan، Join، Sort، Aggregate، Limit. الإضافة ANALYZE بتنفّذ الاستعلام فعلًا وبتسجّل زمن البداية والنهاية وعدد الصفوف الحقيقي لكل عقدة، فيظهر الفرق بين تقدير الـ planner والواقع. مرجع رسمي: postgresql.org/docs/current/using-explain.

الإعداد التقني — هذا الأمر هو القاعدة

-- لا تستخدم EXPLAIN ANALYZE وحده. دائماً BUFFERS معاه.
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, FORMAT TEXT)
SELECT u.id, u.email, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.created_at >= NOW() - INTERVAL '30 days'
GROUP BY u.id, u.email
ORDER BY order_count DESC
LIMIT 50;

الـ flags المستخدمة:

• ANALYZE — ينفّذ الاستعلام ويعطي زمن حقيقي لكل عقدة.
• BUFFERS — يعرض shared hit (من الـ cache) و read (من القرص). الفرق بينهم بيكشف هل المشكلة في I/O فعلي ولا في خطة سيئة.
• VERBOSE — يعرض schema الجدول والـ output columns صراحةً.
• FORMAT JSON — استخدمها لو هتلصق المخرجات في أداة بصرية مثل explain.depesz.com أو PEV2.

تحذير عملي: EXPLAIN ANALYZE بينفّذ الاستعلام بكل آثاره الجانبية. لو بتختبر INSERT أو UPDATE أو DELETE، لفّ الكلام في BEGIN ... ROLLBACK علشان متعدّلش الداتا. ده موثّق صراحةً في SQL EXPLAIN reference.

قراءة الخطة من الداخل للخارج

الخطة شجرة. اقرأها من الورقة الأعمق (أكبر indentation) للجذر. كل عقدة بتعرض سطرين أساسيين:

• actual time=0.024..18.412 rows=980 loops=1 — startup time إلى total time بالميلي ثانية، عدد الصفوف الفعلي، عدد التكرارات.
• Buffers: shared hit=128 read=4032 — 128 صفحة جت من الذاكرة، 4032 من القرص. الصفحة 8KB افتراضيًا، يعني قرأت ~32MB I/O فعلي.

الفرق بين rows الفعلي و estimated أهم رقم في الشاشة. لو estimated=10 و actual=980,000 معناه الإحصاءات قديمة و ANALYZE table_name; محتاج يجري قبل أي تحسين تاني.

الأنماط الستة الأكتر شيوعاً

1) Seq Scan على جدول كبير بدون داعي

الـ planner اختار يقرأ كل صف. السبب الشائع: الإحصاءات بتقول إن أكتر من 30% من الصفوف هترجع، فالـ Sequential I/O أرخص من Random. لو أنت متأكد إن الفلتر انتقائي ولسه بيختار Seq Scan، شغّل ANALYZE table_name; لتحديث الإحصاءات. لو لسه بيختار Seq Scan بعد كده، الإحصائيات صادقة والمشكلة في غياب index مناسب.

2) Nested Loop مع actual rows عالية

Nested Loop تعقيده O(n×m). ممتاز لما n صغيرة (أقل من 1000). كارثة لما n=50,000 و m=200,000 — بتطلع 10 مليار عملية. الـ planner المفروض يختار Hash Join في الحالة دي، لكن لو الإحصاءات بتقول إن n=50 فقط، هياخد القرار الغلط. الإصلاح: ANALYZE، أو ارفع default_statistics_target لو الجدول فيه توزيع غير منتظم.

3) Hash Join بـ disk spill

لو شفت Buckets: 8192 Batches: 16 Memory Usage: 64MB Disk: 412MB، الـ work_mem أصغر من المطلوب والـ hash table اتكتب على القرص، فالعملية بقت أبطأ 10-50×. إصلاح سريع للاستعلام ده فقط:

SET LOCAL work_mem = '512MB';
-- شغّل الـ query هنا
RESET work_mem;

متغيّرش work_mem على مستوى السيرفر بدون حساب الذاكرة الكلية: كل اتصال ممكن يستخدمه عدة مرات في نفس الـ query.

4) Sort بـ external merge

Sort Method: external merge Disk: 168MB معناه نفس مشكلة الذاكرة. الحلول بترتيب التفضيل: ضيف index على عمود الترتيب (الأرخص long-term)، ارفع work_mem للـ session، أو قلّل عدد الصفوف قبل الـ ORDER BY بـ WHERE أكثر انتقائية.

5) Filter بدلاً من Index Cond

لو الخطة فيها Index Scan ... Index Cond: (created_at >= ...) Filter: (status = 'paid')، ده معناه الـ index بيلاقي الصفوف بناءً على التاريخ، وبعدين يمر صف-صف يفلتر بـ status. composite index على (created_at, status) بيخلّي الشرطين يتنفذوا داخل الـ B-tree نفسه، أسرع 5-50 مرة في حالات الإنتاج العادية.

6) Rows Removed by Filter كبير

Rows Removed by Filter: 1,840,000 رقم مخيف. قرأت 1.84 مليون صف من القرص وفلترت أغلبهم. partial index على الشرط الشائع بينزّل ده لقريب من صفر:

CREATE INDEX CONCURRENTLY idx_orders_paid_recent
ON orders (created_at)
WHERE status = 'paid';

مثال تنفيذي بأرقام حقيقية

على جدول events فيه 18 مليون صف، استعلام التحليلات اليومي:

SELECT user_id, COUNT(*) AS purchase_count
FROM events
WHERE event_type = 'purchase'
  AND created_at >= '2026-04-01'
GROUP BY user_id
ORDER BY purchase_count DESC
LIMIT 100;

قبل التحسين (Seq Scan + Hash Aggregate):

• Execution Time: 11842.6 ms
• Buffers: shared hit=12 read=287,420 — 287 ألف صفحة من القرص = ~2.3 جيجا I/O.
• Rows Removed by Filter: 17,280,400

الإصلاح: partial index لأن purchase يمثّل ~4% من الصفوف، فالـ index هيبقى صغير جدًا.

CREATE INDEX CONCURRENTLY idx_events_purchase_date
ON events (created_at, user_id)
WHERE event_type = 'purchase';

بعد التحسين:

• Execution Time: 78.4 ms — تحسّن 151×.
• Buffers: shared hit=4128 read=312 — استهلاك I/O انخفض 99.7%.
• الخطة بقت Index Only Scan + GroupAggregate.

الـ index ده بياخد ~85MB بدلاً من 1.4GB لو كان full index، لأنه بيشمل صفوف purchase فقط. مرجع postgresql.org/docs/current/indexes-partial.

Trade-offs صريحة

• EXPLAIN ANALYZE بينفّذ الاستعلام فعلًا. على query تحديث، لازم BEGIN/ROLLBACK. على query بطيء جدًا في إنتاج، شغّله على read replica.
• overhead الـ timing. الـ instrumentation بيضيف 5-15% زمن إضافي (موثّق في using-explain docs). فالأرقام بتاعتك بتبقى تقريبية مش دقيقة لأقل من 1ms.
• كل index ليه ثمن. الـ index الجديد بيوفّر 100× في الـ read، لكنه بيبطّأ INSERT/UPDATE بـ 10-15% لأن الـ B-tree محتاج يتحدّث. على جدول 80% reads، ده مكسب صافي. على جدول 80% writes، فكّر مرتين.
• partial index محدود. بيشتغل بس لما الـ WHERE في الاستعلام مطابق للـ predicate. لو الـ query فلتر بـ event_type IN ('purchase','refund')، الـ partial index على purchase وحده مش هينفع.

متى لا تستخدم EXPLAIN ANALYZE

لو الـ query بطيء بسبب lock مش بسبب الخطة، EXPLAIN ANALYZE هيدّيك خطة سريعة وأنت لسه شايف 12 ثانية في إنتاج. الإجابة في:

SELECT pid, state, wait_event_type, wait_event, query, now() - query_start AS duration
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY duration DESC;

كذلك لو الاستعلام عنده تأثير جانبي مكلف (call إلى webhook عبر pg_net، إرسال email عبر trigger)، EXPLAIN ANALYZE هينفّذ التأثير ده. استخدم EXPLAIN بدون ANALYZE في الحالات دي، أو لفّ الكلام في BEGIN/ROLLBACK لو الـ trigger بيكتب في الـ DB نفسها.

الخطوة التالية

افتح الـ extension pg_stat_statements لو مش مفعّل، استخرج أبطأ 5 استعلامات (SELECT query, mean_exec_time FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 5;)، شغّل EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) ... على كل واحد، وألصق المخرجات في explain.depesz.com. لو لقيت Seq Scan على جدول > مليون صف، أو Sort بـ external merge، أو Rows Removed by Filter > 100K — عندك مكسب 10×+ في انتظارك.

مصادر

• PostgreSQL — Using EXPLAIN: postgresql.org/docs/current/using-explain.html
• PostgreSQL — EXPLAIN command reference: postgresql.org/docs/current/sql-explain.html
• PostgreSQL — Query Planning configuration: postgresql.org/docs/current/runtime-config-query.html
• PostgreSQL — Partial Indexes: postgresql.org/docs/current/indexes-partial.html
• PostgreSQL — pg_stat_statements: postgresql.org/docs/current/pgstatstatements.html
• PostgreSQL — pg_stat_activity view: postgresql.org/docs/current/monitoring-stats
• Depesz EXPLAIN visualizer: explain.depesz.com
• Dalibo PEV2 — PostgreSQL Explain Visualizer: github.com/dalibo/pev2