قراءة EXPLAIN ANALYZE في PostgreSQL: كيف نجد الفهرس الناقص وراء نقطة API بطيئة في Node.js

أهم النقاط

الأمر EXPLAIN ANALYZE ينفّذ الاستعلام الذي يشرحه فعليًا، لذلك لا يشغّله فريقنا على INSERT أو UPDATE أو DELETE إلا داخل BEGIN ثم ROLLBACK.

نبدأ من pg_stat_statements مرتّبًا حسب total_exec_time، لا من الاستعلام الذي نشكّ فيه، لأن استعلامًا سريعًا يعمل مع كل طلب يكلّف غالبًا في المجموع أكثر من استعلام بطيء يعمل مرتين في اليوم.

السطر Rows Removed by Filter هو أوضح مؤشر على فهرس ناقص في خطة PostgreSQL: قاعدة البيانات قرأت هذه الصفوف فقط لتتخلّص منها.

في الفهرس المركّب من نوع B-tree نضع أعمدة المساواة أولًا وعمود الترتيب أو النطاق أخيرًا، حتى يخدم فهرس واحد شرط WHERE وعبارة ORDER BY مع LIMIT معًا.

نبني فهارس بيئة الإنتاج باستخدام CREATE INDEX CONCURRENTLY، الذي لا يوقف عمليات الكتابة لكنه لا يعمل داخل كتلة معاملة (transaction).

لماذا نقرأ خطط الاستعلام بدل تخمين الفهارس؟

في Devya نقرأ خطة الاستعلام قبل إضافة أي فهرس في PostgreSQL، لأن الفهارس المضافة بالتخمين تسرّع استعلامًا واحدًا وتبطئ كل عملية كتابة. من الأنماط المتكررة في مشاريع Node.js نقطة API كانت سريعة أثناء التطوير ثم تزداد بطئًا كل أسبوع في الإنتاج دون أي تغيير في الكود: الاستعلام نفسه لم يتغير، لكن الجدول كبر. تشرح هذه الملاحظات كيف يقرأ فريقنا خطط استعلامات PostgreSQL كمطوّري تطبيقات، باستخدام node-postgres وDrizzle ORM على PostgreSQL 17، والخطوات نفسها تنطبق على Prisma وTypeORM وSQL المباشر.

ما الفرق بين EXPLAIN وEXPLAIN ANALYZE؟

يعرض EXPLAIN الخطة التي اختارها مخطِّط PostgreSQL (planner) مع تقديرات التكلفة دون تشغيل الاستعلام. أما EXPLAIN ANALYZE فيشغّل الاستعلام ويضيف إلى كل عقدة في الخطة الزمن الفعلي وعدد الصفوف الفعلي وعدد الدورات (loops). خطة الاستعلام هي شجرة الخطوات، مثل المسح والربط والترتيب، التي ينفّذها PostgreSQL لإنتاج النتيجة.

قيم التكلفة في الخطة وحدات اعتباطية خاصة بالمخطِّط وليست ملّي ثانية، لذلك لا نقارن التكاليف إلا بين خطط الاستعلام نفسه. ويضيف الخيار BUFFERS عدد الصفحات (حجم كل منها 8 كيلوبايت) التي وجدتها كل عقدة في الذاكرة المشتركة (shared hit) وعدد ما جلبته من نظام التشغيل أو القرص (read). يتضمّن PostgreSQL 18 أعداد BUFFERS في EXPLAIN ANALYZE افتراضيًا، أما في PostgreSQL 17 وما قبله فنمرّر BUFFERS صراحة.

لأن ANALYZE ينفّذ الاستعلام فعلًا، فإن EXPLAIN ANALYZE على أمر UPDATE يعدّل الصفوف ويشغّل الـ triggers. لذلك تفتح الدالة المساعدة لدينا معاملة بـ BEGIN، ثم تشغّل EXPLAIN (ANALYZE, BUFFERS) مع نص الاستعلام ومعاملاته، وتنتهي دائمًا بـ ROLLBACK. ولشرح SQL الذي يولّده الـ ORM بالضبط نستدعي toSQL() على استعلام Drizzle، فتعيد نص SQL ومعاملاته، وفي Prisma يوفّر حدث سجلّ الاستعلامات (query log event) القيمتين نفسيهما.

كيف نعرف أي استعلام نشرحه؟

نحدّد الاستعلام عبر pg_stat_statements، وهي إضافة (extension) في PostgreSQL تسجّل إحصاءات التنفيذ لكل استعلام بعد توحيد صيغته. يجب إدراجها في shared_preload_libraries وتفعيلها بالأمر CREATE EXTENSION pg_stat_statements، ومعظم خدمات PostgreSQL المُدارة توفّرها. نختار الأعمدة calls وtotal_exec_time وmean_exec_time ونص الاستعلام، مرتّبة تنازليًا حسب total_exec_time، لأن الزمن الإجمالي هو متوسط الزمن مضروبًا في عدد مرات الاستدعاء.

يخزّن pg_stat_statements الاستعلامات بعناصر نائبة مثل $1، لذلك نأخذ قيم المعاملات الحقيقية من سجلات التطبيق قبل تشغيل EXPLAIN ANALYZE. والقيم مهمة: مع البيانات غير المتوازنة قد يحصل عميل لديه عشرة صفوف وعميل لديه مليون صف على خطتين مختلفتين تمامًا لنفس SQL. وللخطط التي لا تسوء إلا تحت حمل الإنتاج، تسجّل الوحدة auto_explain المرفقة مع PostgreSQL خطة كل استعلام أبطأ من auto_explain.log_min_duration.

ما الأسطر المهمة فعلًا في خطة استعلام PostgreSQL؟

نقرأ الخطة من العقدة الأعمق إلى الخارج، لأن العقد الفرعية تعمل أولًا وتمرّر الصفوف إلى العقد الأعلى. الخطة البطيئة المعتادة لاستعلام مثل WHERE tenant_id = 42 AND status = 'pending' ORDER BY created_at DESC LIMIT 20 تكون Limit فوق Sort فوق Seq Scan على جدول orders، ويظهر في Seq Scan رقم كبير في Rows Removed by Filter: قرأ PostgreSQL الجدول كله، وتخلّص من معظم الصفوف تقريبًا، ورتّب الباقي، ثم أعاد 20 صفًا.

قيمة Rows Removed by Filter أكبر بكثير من actual rows: المسح يقرأ صفوفًا فقط ليرفضها، وهذا يعني عادة فهرسًا ناقصًا أو غير قابل للاستخدام.

عدد الصفوف المقدَّر بعيد عن العدد الفعلي: فرق بعشرة أضعاف أو أكثر يعني أن المخطِّط اختار خطته بأرقام خاطئة. نشغّل ANALYZE على الجدول، ونضيف CREATE STATISTICS عندما تكون الأعمدة المُرشَّحة مترابطة.

قيمة loops أكبر من 1: الصفوف والزمن الفعليان يُعرضان لكل دورة، لذلك نضربهما في loops. عقدة تبدو رخيصة داخل Nested Loop وتعمل آلاف المرات تكون غالبًا هي التكلفة الحقيقية.

Sort Method بقيمة external merge مع Disk: عملية الترتيب تجاوزت work_mem وانتقلت إلى القرص. الفهرس الذي يعيد الصفوف مرتّبة مسبقًا يلغي عملية الترتيب بالكامل.

قيمة shared read مرتفعة مقارنة بـ shared hit: البيانات لم تكن في ذاكرة PostgreSQL المؤقتة، فالاستعلام محدود بسرعة القرص وسيكون أبطأ مع ذاكرة باردة مما يظهر في الاختبارات المتكررة.

كيف نختار بين الفهرس المركّب والجزئي وفهرس التغطية؟

نختار الفهرس حسب شكل الاستعلام: أعمدة المساواة أولًا، ثم عمود النطاق أو الترتيب، ولا نضيف شرطًا جزئيًا أو أعمدة INCLUDE إلا عندما تُظهر الخطة عملًا إضافيًا متبقيًا. للاستعلام السابق على orders يكون الفهرس المركّب CREATE INDEX CONCURRENTLY orders_tenant_status_created_idx ON orders (tenant_id, status, created_at). ومعه تصبح الخطة Limit فوق Index Scan Backward: تختفي عقدة Sort ولا يقرأ PostgreSQL إلا الصفوف العشرين التي يحتاجها LIMIT. ويمكن مسح فهرس B-tree بالاتجاه العكسي، لذلك لا نحتاج created_at DESC في تعريف الفهرس إلا عندما يخلط الاستعلام بين اتجاهَي ترتيب.

الفهرس المركّب B-tree: يخدم الاستعلامات التي تُرشِّح على بادئة من أعمدته تبدأ بالعمود الأول. الفهرس على (tenant_id, status, created_at) يساعد استعلامًا على tenant_id وحده، لكنه لا يساعد استعلامًا على status وحده؛ وميزة skip scan لفهارس B-tree المضافة في PostgreSQL 18 تقلّص هذه الفجوة فقط عندما يكون للعمود الأول عدد قليل من القيم المختلفة.

الفهرس الجزئي، مثل ON orders (tenant_id, created_at) WHERE status = 'pending': أصغر حجمًا وأرخص صيانة عندما يقرأ الاستعلام المتكرر مجموعة صغيرة من الصفوف. لا يستخدمه المخطِّط إلا إذا استطاع إثبات أن شرط WHERE في الاستعلام يستلزم شرط الفهرس، لذلك نكتب الشرط قيمةً ثابتة في SQL لا معاملًا.

فهرس التغطية باستخدام INCLUDE، مثل ON orders (tenant_id, created_at) INCLUDE (total_cents): متاح منذ PostgreSQL 11، ويتيح لـ Index Only Scan الإجابة عن الاستعلام دون زيارة الجدول، ما دام autovacuum يحافظ على تحديث خريطة الرؤية (visibility map).

فهرس التعبير، مثل ON users (lower(email)): ضروري كلما غلّف الاستعلام العمود بدالة، لأن الفهرس على email لا يخدم الشرط lower(email) = $1.

لماذا يتجاهل PostgreSQL الفهرس؟

يتجاهل PostgreSQL الفهرس عندما يقدّر المخطِّط أن المسح المتسلسل أرخص، أو عندما يُكتب الاستعلام بطريقة لا يستطيع الفهرس خدمتها. أكثر الأسباب التي يصادفها فريقنا: بيانات تطوير صغيرة جدًا (مع بضع مئات من الصفوف يكون Seq Scan أرخص فعلًا، لذلك نولّد أحجامًا واقعية باستخدام generate_series ثم نشغّل ANALYZE)، وشروط تطابق نسبة كبيرة من الجدول، وأعمدة مغلّفة بدالة أو بتحويل نوع مثل created_at::date = $1 (نعيد كتابته كنطاق على created_at)، وغياب العمود الأول في الفهرس عن الاستعلام، وأنماط LIKE التي تبدأ بحرف بدل (يخدمها فهرس GIN مع pg_trgm)، وإحصاءات قديمة بعد تحميل كمية كبيرة من البيانات.

للتحقق مما إذا كان الفهرس قابلًا للاستخدام أصلًا، نشغّل SET enable_seqscan = off في جلسة psql واحدة ثم نعيد الشرح. هذا الإعداد أداة تشخيص فقط ولا مكان له في إعدادات الإنتاج.

كيف نضيف فهرسًا إلى جدول في الإنتاج بأمان؟

نبني الفهارس على الجداول الحيّة باستخدام CREATE INDEX CONCURRENTLY، الذي يسمح باستمرار عمليات الإدراج والتحديث والحذف أثناء البناء، بينما يوقف CREATE INDEX العادي عمليات الكتابة على الجدول حتى ينتهي. البناء المتزامن يستغرق وقتًا أطول ولا يعمل داخل كتلة معاملة، لذلك يجب تعطيل التغليف بالمعاملة لترحيل الفهرس تحديدًا في أدوات الترحيل (migrations) التي تغلّف كل ترحيل بمعاملة.

البناء المتزامن الفاشل يترك فهرسًا بحالة INVALID يظل يبطئ عمليات الكتابة. نجد هذه الفهارس في pg_index حيث indisvalid تساوي false، ونحذفها بـ DROP INDEX CONCURRENTLY ثم نعيد المحاولة. وبعد كل فهرس جديد نشغّل EXPLAIN ANALYZE نفسه مرة أخرى للتأكد من تغيّر الخطة، ونراجع pg_stat_user_indexes بحثًا عن فهارس تبقى قيمة idx_scan فيها 0، مع فحص نسخ القراءة (read replicas) التي تحتفظ بإحصاءاتها الخاصة قبل حذف أي شيء. أما الفهارس الفريدة والفهارس التي تدعم القيود فتبقى في كل الأحوال.

الأسئلة الشائعة

س: هل تشغيل EXPLAIN ANALYZE في الإنتاج آمن؟

ج: مع SELECT نعم، لكنه يشغّل الاستعلام كاملًا، فالاستعلام البطيء يستهلك زمنه الكامل مرة أخرى. ومع INSERT وUPDATE وDELETE شغّل EXPLAIN ANALYZE داخل BEGIN ثم ROLLBACK، لأن ANALYZE ينفّذ الكتابة ويشغّل الـ triggers المرتبطة بها.

س: ما رقم التكلفة الجيد في خطة PostgreSQL؟

ج: لا توجد تكلفة مطلقة جيدة. التكاليف وحدات خاصة بالمخطِّط نسبةً إلى seq_page_cost الذي قيمته الافتراضية 1.0، ولا معنى لها إلا عند مقارنة خطط الاستعلام نفسه؛ أما الأداء الحقيقي فيظهر في الزمن الفعلي من EXPLAIN ANALYZE.

س: لماذا يكون الاستعلام سريعًا في psql وبطيئًا من تطبيق Node.js؟

ج: قد يرسل التطبيق قيم معاملات تصيب شريحة أكبر بكثير من بيانات غير متوازنة، أو قد يتحوّل استعلام مُعَدّ مسبقًا ومُسمّى (named prepared statement) إلى خطة عامة (generic plan) بعد خمس مرات تنفيذ. وقد يُستهلك الزمن خارج PostgreSQL أصلًا، في انتظار اتصال من الـ pool أو في رحلات الشبكة أو في نمط استعلامات N+1، لذلك قارن mean_exec_time في pg_stat_statements بالزمن المقاس داخل التطبيق.

س: كم فهرسًا يجب أن يكون في جدول PostgreSQL؟

ج: أقل عدد يخدم الاستعلامات التي يشغّلها التطبيق فعلًا. كل فهرس يضيف عملًا إلى كل INSERT وإلى تحديثات الأعمدة المفهرسة وإلى vacuum، وينافس على الذاكرة المؤقتة، لذلك تكون الفهارس التي تبقى قيمة idx_scan فيها 0 مرشّحة للحذف.