استعلام PostgreSQL بطيء؟ حسّنه بالأرقام بدل التخمين

هتطلع من المقال بخطة واضحة لتقليل زمن Query بطيئة في PostgreSQL من حوالي 1800ms إلى 230ms في سيناريو واقعي، بدون ما تبدأ بترقية السيرفر.

مستوى القارئ: متوسط

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

لو عندك endpoint بيعرض آخر طلبات عميل معيّن، وبدأ ياخد ثانيتين تحت الضغط، أسوأ رد فعل هو إنك تزود CPU أو RAM فورًا. الطريقة دي بتفشل لأن المشكلة غالبًا في شكل الوصول للبيانات، مش في حجم السيرفر.

الافتراض هنا إن عندك جدول orders فيه حوالي 1.2 مليون صف، وموقع بيخدم 50K زائر يوميًا. الـ API المطلوب بيرجع آخر 50 طلب لعميل واحد. القياس الأولي: p95 latency = 1800ms، وقراءة حوالي 42000 shared blocks. بعد فهرس مناسب وقياس جديد وصلنا إلى p95 = 230ms وقراءة حوالي 4800 shared blocks. الأرقام مثال عملي، ولازم تعيد القياس على بياناتك.

ابدأ من الاستعلام الحقيقي

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

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT
  calls,
  round(mean_exec_time::numeric, 2) AS mean_ms,
  round(total_exec_time::numeric, 2) AS total_ms,
  query
FROM pg_stat_statements
WHERE query ILIKE '%orders%'
ORDER BY total_exec_time DESC
LIMIT 5;

لو ظهر الاستعلام ده في الأعلى، يبقى عندك هدف واضح:

SELECT id, status, total_cents, created_at
FROM orders
WHERE tenant_id = $1
ORDER BY created_at DESC
LIMIT 50;

اقرأ الخطة قبل ما تضيف فهرس

EXPLAIN (ANALYZE, BUFFERS) بيشغّل الاستعلام فعليًا ويعرض زمن التنفيذ وعدد الصفوف وقراءات الـ buffers. PostgreSQL نفسه يوضح إن ANALYZE يعرض الأرقام الحقيقية، وإن BUFFERS يوضح استخدام الذاكرة والقراءة من التخزين.

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, status, total_cents, created_at
FROM orders
WHERE tenant_id = 'acme'
ORDER BY created_at DESC
LIMIT 50;

لو الخطة بتقول Seq Scan on orders ثم Sort، اللي بيحصل فعلاً إن PostgreSQL يلف على جزء كبير من الجدول، يفلتر tenant، ثم يرتّب النتائج. ده مكلف جدًا لو الجدول كبير والطلب متكرر.

مثال بسيط: كأنك بتدور على آخر 50 فاتورة لعميل واحد داخل أرشيف فيه مليون ورقة. لو الورق مش مترتب حسب العميل والوقت، هتفتح أدراج كتير جدًا. الفهرس المركب هو دفتر جانبي مرتب: العميل أولًا، ثم أحدث تاريخ.

الحل: فهرس مركب مطابق لنمط القراءة

أفضل طريقة هنا هي فهرس يبدأ بـ tenant_id لأن الشرط عليه مساواة، ثم created_at DESC لأن الترتيب مطلوب من الأحدث للأقدم. توثيق PostgreSQL عن multicolumn indexes يوضح إن فهارس B-tree تكون أكثر كفاءة عندما تستخدم الشروط الأعمدة اليسرى الأولى في الفهرس.

CREATE INDEX CONCURRENTLY idx_orders_tenant_created_at
ON orders (tenant_id, created_at DESC)
INCLUDE (status, total_cents);

ANALYZE orders;

INCLUDE هنا مش عشان الفلترة. الغرض إنه يقلل رجوع PostgreSQL للجدول الأصلي في بعض الحالات. الـ trade-off هنا واضح: بتكسب قراءة أسرع للاستعلام، وبتخسر مساحة تخزين إضافية وزمن كتابة أعلى عند INSERT وUPDATE. لو الجدول عليه 300 كتابة في الثانية، راقب تأثير الفهرس قبل تعميمه.

قِس بعد التغيير بنفس الطريقة

بعد إنشاء الفهرس، شغّل نفس EXPLAIN مرة ثانية. المتوقع إنك تشوف Index Scan أو Index Only Scan بدل Seq Scan، ويقل عدد الـ buffers المقروءة. في المثال ده، الزمن نزل من 1800ms إلى 230ms. ده تحسن حوالي 87%.

لكن خليك دقيق. لو أول تشغيل بعد الفهرس كان سريعًا جدًا، ممكن يكون السبب إن البيانات دخلت cache. لذلك كرر القياس 5 مرات، وسجل p50 وp95. لا تعتمد على قراءة واحدة.

متى لا تستخدم هذه الطريقة

لا تستخدم فهرسًا مركبًا لكل Query بطيئة. لو الاستعلام نادر جدًا، أو الجدول أقل من 20K صف، أو الكتابة أهم من القراءة، ممكن تكلفة الفهرس تكون أعلى من مكسبه. كمان لو عندك شروط مختلفة كل مرة، مثل بحث حر على 8 أعمدة، فكر في تصميم بحث منفصل أو فهرس مختلف بدل ما تكدّس فهارس لا تُستخدم.

المصادر

• PostgreSQL: Using EXPLAIN
• PostgreSQL: pg_stat_statements
• PostgreSQL: Multicolumn Indexes

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

افتح أبطأ endpoint عندك، هات الاستعلام من pg_stat_statements، ثم شغّل EXPLAIN (ANALYZE, BUFFERS). لو لقيت Seq Scan على جدول كبير مع ORDER BY، صمّم فهرسًا واحدًا مطابقًا لنمط القراءة وقِس الفرق قبل أي ترقية سيرفر.