المستوي: محترف — المقال ده بيفترض إن عندك خبرة عملية بـ PostgreSQL، كتبت SELECT و JOIN على جداول أكبر من مليون صف، وعندك إلمام بأساسيات B-Tree Index. لو لسه في البداية، ابدأ بمقال B-Tree Indexes للمبتدئ في نفس القسم وارجع هنا.

لو ضفت Index على جدول 12 مليون صف وفاجأك إن نفس الـ query لسه بياخد 2.87 ثانية، PostgreSQL مش بيهربلك. هو قرّر إن الـ Index بتاعك مش يستاهل الاستخدام. EXPLAIN ANALYZE بيوريك القرار ده بالأرقام، قبل ما تكتب CREATE INDEX تاني وتزوّد عبء الكتابة على الجدول من غير فايدة.

EXPLAIN ANALYZE: مش مجرد debug، ده عقد القرار

EXPLAIN لوحده بيطبع plan افتراضي بناءً على إحصائيات الـ planner. يعني تقدير، مش قياس فعلي. EXPLAIN ANALYZE بينفّذ الـ query فعلاً ويرجّعلك الأرقام الحقيقية: actual time، actual rows، عدد الـ buffers اللي اتقرت من الـ disk والـ cache. الفرق ده بيبان أوضح ما يكون لما الإحصائيات outdated أو لما الـ data distribution اتغير بعد كذا insert كبير.

مثال للمبتدئ على فكرة الـ planner: تخيّل سواق ديليفري عنده خريطة عمرها سنة. الخريطة بتقوله "الطريق ده فاضي" (تقدير). EXPLAIN ANALYZE معناه إنك ركبت العربية ودخلت الطريق فعلاً وسجّلت اتأخرت كام دقيقة. الخريطة القديمة بتغش أول ما الشارع يبقى مزدحم، وكل ما القرار يبقى مبني على خريطة قديمة، النتيجة بتبقى أسوأ.

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

الـ developers بيضيفوا indexes زي ما الـ ORM بيقترح: WHERE clause = index جديد. النتيجة الشائعة جداً: 6 indexes على جدول واحد، الكتابة بقت أبطأ بنسبة 40%، والـ SELECT لسه بطيء لأن الـ planner اختار Sequential Scan رغم وجود الـ index. الحل مش CREATE INDEX جديد، الحل تقرا الـ plan وتفهم ليه الـ planner اتخذ القرار ده.

قراءة المخرجات: الـ Nodes اللي لازم تركّز عليها

كل plan شجرة. كل عقدة (node) عملية. أهم 4 عقد هتشوفهم بشكل متكرّر:

• Seq Scan: قراءة كل صفوف الجدول صف صف. على جدول 50 ألف صف ممكن يكون أسرع من Index Scan. على جدول 12 مليون صف كارثة لو الـ filter بيرجّع نسبة صغيرة.
• Index Scan: قراءة عبر الـ B-Tree بعدها fetch من الـ heap. كويس لو النتائج قليلة (أقل من ~5% من الجدول).
• Bitmap Heap Scan: لما النتائج كثيرة لكن أقل من نص الجدول. الـ planner بيبني bitmap في الذاكرة لكل الصفحات اللي محتاجها ثم بيقراهم بترتيب على الـ disk.
• Nested Loop: مناسب لما الجدول الخارجي صغير (أقل من 1000 صف غالباً) والداخلي معاه index على الـ join key. لو الاتنين كبار، Hash Join أو Merge Join أفضل.

أهم سطر في كل عقدة هو: (actual time=X..Y rows=R loops=L). اضرب الزمن في loops علشان تعرف الزمن الفعلي اللي صرفته العقدة. الـ planner بيكذب لما يقدّر rows=50 وانت بتشوف actual rows=180000. الفرق ده لوحده مؤشر إن إحصائياتك محتاجة ANALYZE.

مثال عملي: query شكلها كويسة وفعلاً بطيئة

الـ schema: جدول orders فيه 12.4 مليون صف، عمود customer_id عليه B-Tree index قديم. السؤال: ارجّعلي طلبات عميل معيّن في آخر شهر.

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders
WHERE customer_id = 84210
  AND created_at > NOW() - INTERVAL '30 days';

المخرج الفعلي على PostgreSQL 16 و instance بحجم 16GB RAM:

Seq Scan on orders  (cost=0.00..382144.00 rows=120 width=148)
                    (actual time=1842.331..2873.118 rows=42 loops=1)
  Filter: ((customer_id = 84210) AND (created_at > now() - '30 days'::interval))
  Rows Removed by Filter: 12399958
  Buffers: shared hit=14 read=124318
Planning Time: 0.418 ms
Execution Time: 2873.964 ms

الـ Index على customer_id موجود، والـ query بياخد 2.87 ثانية، والـ planner لسه اختار Seq Scan. ليه؟ لأن الـ planner شاف إن الـ Bitmap Scan هيحتاج يقرا 124 ألف buffer page علشان يفلتر بـ created_at بعد كده، فحسب إن قراءة الجدول كله sequentially أرخص. Rows Removed by Filter: 12399958 هي الكارثة: قرى 12.4 مليون صف علشان يرجّع 42 فقط.

الحل: composite index بترتيب صح

CREATE INDEX CONCURRENTLY idx_orders_customer_created
ON orders (customer_id, created_at DESC);

بعد الـ index الجديد، نفس الـ query:

Index Scan using idx_orders_customer_created on orders
  (cost=0.56..18.34 rows=120 width=148)
  (actual time=0.038..0.184 rows=42 loops=1)
  Index Cond: (customer_id = 84210 AND created_at > now() - '30 days'::interval)
  Buffers: shared hit=8
Planning Time: 0.392 ms
Execution Time: 0.218 ms

من 2873ms لـ 0.218ms — تحسّن 13,180 ضعف. السبب: العمود الأول في الـ index هو الـ equality condition (customer_id =)، والتاني هو الـ range (created_at >)، وده الترتيب الصحيح لـ B-Tree متعدد الأعمدة. لو عكست الترتيب وعملت (created_at, customer_id)، الـ query هياخد حوالي 80ms لأن الـ index هيمشي على الـ range الأول ثم يفلتر داخلياً. CONCURRENTLY ضروري في الإنتاج علشان مايقفلش الجدول.

Trade-offs مش بتظهر في الـ tutorials

• كل index بيبطّأ الكتابة. 6 indexes على جدول بيخلّي INSERT أبطأ بمتوسط 35-45%. على جدول بيستقبل 800 write/sec ده فرق حقيقي بيظهر في P95 latency.
• VACUUM بياخد وقت أطول. autovacuum على جدول 100GB بـ 5 indexes ممكن يفضل ساعتين، وخلال الوقت ده الـ I/O بيتأثر وممكن تشوف ارتفاع في زمن queries تانية.
• الإحصائيات بتخدع الـ planner. لو الـ statistics outdated، EXPLAIN ANALYZE هيوريك زمن صحيح لكن استنتاج الـ planner للسبب مش بالضرورة الأمثل. شغّل ANALYZE orders; قبل أي قرار جدّي على index.
• BUFFERS أهم من time. الزمن بيتأثر بحالة الـ cache. Buffers: shared hit=124000 بيقولك حاجة ثابتة عن حجم الـ I/O، لكن actual time ممكن يتضاعف لو الـ DB رجعت من restart والـ pages مش في الذاكرة.

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

على أي query بيعدّل بيانات (INSERT / UPDATE / DELETE) في الإنتاج — EXPLAIN ANALYZE بينفّذ فعلاً، مش بيقدّر. لفّها في transaction واعمل ROLLBACK:

BEGIN;
EXPLAIN (ANALYZE, BUFFERS)
UPDATE orders SET status = 'shipped' WHERE id = 91244;
ROLLBACK;

كمان مش مناسب على query تقديرها بياخد 30 ثانية وانت بتختبر local والوقت محدود. استخدم EXPLAIN العادي الأول علشان تفهم الـ plan، صلّح اللي يتصلّح، وبعدين شغّل ANALYZE لما الـ plan يبقى معقول. ولو الجدول صغير (أقل من 50K صف)، Seq Scan ممكن يكون الاختيار الصح. متحاولش تجبر Index Scan بـ SET enable_seqscan = off في الإنتاج.

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

افتح أبطأ endpoint في تطبيقك دلوقتي، شغّل عليه EXPLAIN (ANALYZE, BUFFERS)، وانسخ المخرج في explain.dalibo.com علشان تشوف الـ plan visually. لو شفت Seq Scan على جدول أكبر من 100K صف، أو Rows Removed by Filter أكتر من 80% من الصفوف المقروءة، عندك مكان مباشر للتحسين. متضفش index قبل ما تتأكد إن الـ planner مش هيتجاهله بسبب ترتيب الأعمدة أو الإحصائيات.

المصادر

• توثيق PostgreSQL 16 الرسمي عن EXPLAIN: postgresql.org/docs/16/using-explain.html
• توثيق Query Planning و cost estimation: postgresql.org/docs/16/runtime-config-query.html
• أداة Dalibo لقراءة الـ plan بصرياً: explain.dalibo.com
• ورقة Bayer & McCreight 1972: Organization and Maintenance of Large Ordered Indices (الأصل العلمي لـ B-Tree)
• توثيق CONCURRENTLY في CREATE INDEX: postgresql.org/docs/16/sql-createindex.html