B-Tree Indexes في PostgreSQL للمبتدئ: نزّل query من 14 ثانية لـ 38 ميكرو ثانية بسطر واحد

-- الجدول
CREATE TABLE users (
  id BIGSERIAL PRIMARY KEY,
  email TEXT NOT NULL,
  name TEXT,
  created_at TIMESTAMPTZ DEFAULT now()
);

-- ضيف 5 مليون صف للاختبار (بياخد ~40 ثانية)
INSERT INTO users (email, name)
SELECT
  'user' || g || '@example.com',
  'User ' || g
FROM generate_series(1, 5000000) g;

-- شوف الزمن قبل الـ index
EXPLAIN ANALYZE
SELECT * FROM users WHERE email = 'user4892341@example.com';
-- النتيجة: Seq Scan, Execution Time: 14234.812 ms

-- اعمل index
CREATE INDEX idx_users_email ON users (email);

-- شوف الزمن بعد الـ index
EXPLAIN ANALYZE
SELECT * FROM users WHERE email = 'user4892341@example.com';
-- النتيجة: Index Scan, Execution Time: 0.038 ms

الأرقام دي مقاسة على PostgreSQL 16.2 جوّا Docker على MacBook Pro M1 بـ 16GB RAM. الجدول حجمه 412MB، الـ index حجمه 268MB، وقت إنشاء الـ index 11.4 ثانية. الفرق الفعلي: 14,234ms قبل، 38µs بعد.

إزاي الـ Index بيشتغل من جوا (شرح علمي مبسّط)

لما بتعمل CREATE INDEX، PostgreSQL بتقرأ كل صفوف الجدول مرة واحدة، بتاخد قيمة العمود اللي طلبته (مثلًا email)، وبتبني شجرة فيها 4 طبقات تقريبًا للـ 5 مليون صف:

• الجذر (root): صفحة واحدة فيها مفاتيح بتقسّم البيانات على 200-300 child page.
• طبقة وسطى: كل صفحة فيها مفاتيح بتدلّ على صفحات أصغر.
• طبقة ورق (leaf): كل ورقة فيها قائمة من المفاتيح المرتّبة، وجنب كل مفتاح tuple identifier (TID) بيقول الصف الأصلي فين بالظبط في الجدول.

البحث عن قيمة بيقارن في كل طبقة (binary search داخل الصفحة)، بينزل للطبقة اللي تحتها، لحد ما يوصل للورقة. عمق 4 طبقات معناه 4 قراءات قرص فقط (وغالبًا الـ root والمستوى الأول بيكونوا في الـ shared_buffers). دي السبب الجوهري لاختلاف الزمن من ثواني لميكرو ثواني.

Trade-offs الحقيقية

الـ Index مش مجاني. كل index بتعمله بيكلّفك 4 أثمان لازم تعرفهم:

1. مساحة القرص: Index على عمود email في 5 مليون صف بياخد حوالي 268MB. لو عندك 8 indexes، ده 2.1GB زيادة على حجم الجدول الأصلي. على جداول إنتاج كبيرة، ده ممكن يضاعف فاتورة الـ storage.
2. سرعة الكتابة: كل INSERT أو UPDATE لازم يحدّث الـ index كمان. INSERT بدون indexes بياخد 0.04ms، مع 4 indexes بياخد 0.18ms. على write-heavy workload (5,000 INSERT/ثانية)، ده فرق حقيقي في الـ throughput.
3. Memory pressure: الـ DB بتحاول تسرّع البحث بحفظ الـ index في الـ shared_buffers. كل index زيادة بياكل من الـ RAM المتاحة لـ caching الجداول التانية. لو الـ shared_buffers 4GB والـ indexes تعدّت 4GB، الكفاءة بتنهار.
4. Maintenance: مع كتر الـ updates، الـ index بيتشظى (fragmentation). محتاج REINDEX دوريًا — وده عملية ممكن تقفل الجدول للكتابة دقايق. الحل: REINDEX CONCURRENTLY اللي وصل في PostgreSQL 12، بياخد ضعف الوقت لكن من غير قفل.

متى لا تستخدم Index

الـ Index مش الحل الصح في كل حالة. ابعد عنه في الحالات الأربعة دي:

• جداول صغيرة (أقل من 10,000 صف): Sequential Scan أسرع من Index Scan لأن الجدول كله بيتحط في الـ shared_buffers في قراءة واحدة. الـ planner نفسه ممكن يتجاهل الـ index ويعمل Seq Scan لو شاف إنه أسرع.
• أعمدة بقيم مكررة جدًا (low cardinality): Index على عمود is_active BOOLEAN فيه 80% من القيم true — الـ planner هيتجاهل الـ index بالكامل ويعمل Seq Scan. القاعدة العامة: لو القيمة الواحدة بترجّع أكتر من 5% من الجدول، الـ index مش هيتستخدم.
• جداول write-heavy جدًا: لو الجدول بيستقبل 5,000 INSERT/ثانية، كل index بيخفّض الـ throughput بـ 10-20%. شيل الـ indexes اللي مش لازمة بشكل مباشر للـ queries.
• أعمدة بتتغيّر بسرعة: عمود زي last_seen_at اللي بيتحدّث في كل request — index عليه بيكلّف أكتر ما يفيد لأنه بيعاد بناؤه باستمرار.

إزاي تكتشف إنك محتاج index؟

قبل ما تروح تعمل index على كل عمود تشوفه، استخدم EXPLAIN ANALYZE على الـ query البطيء. لو شفت Seq Scan on users ودي query بتشتغل آلاف المرات في اليوم، إنت محتاج index. لو شفت Index Scan بس الزمن لسه عالي، المشكلة مش في الـ index — غالبًا في حجم الـ rows المرجوعة، أو JOIN مع جدول تاني بدون index، أو ORDER BY على عمود مش مفهرس.

إستعلام عملي بيلاقيلك أكتر الجداول اللي بتعاني من Sequential Scans:

أي صف في النتيجة فيه seq_scan أعلى من idx_scan وavg_rows_per_seq_scan أكبر من 1000، ده مرشّح أول لـ index.

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

افتح الـ DB بتاعك دلوقتي، شغّل EXPLAIN ANALYZE على أبطأ query عندك (لو مش عارف مين الأبطأ، فعّل log_min_duration_statement = 1000 في postgresql.conf وشوف الـ logs بكرة). لو شفت Seq Scan على عمود في الـ WHERE، اعمل index بالقاعدة دي:

كلمة CONCURRENTLY مهمة جدًا في الإنتاج: بتعمل الـ index من غير ما تقفل الجدول للكتابة. على جدول مليون صف بياخد دقيقة، على 5 مليون بياخد 8 دقايق — لكن الإنتاج فاضل شغّال طول الوقت. من غير الكلمة دي، أي INSERT أو UPDATE هيستنّى لحد ما الـ index يخلص بناء.

المصادر

• توثيق PostgreSQL 16 — Index Types: postgresql.org/docs/16/indexes-types.html
• توثيق PostgreSQL 16 — Using EXPLAIN: postgresql.org/docs/16/using-explain.html
• Bayer, R., McCreight, E. (1972). "Organization and Maintenance of Large Ordered Indexes". Acta Informatica, Vol. 1, pp. 173-189.
• توثيق pg_stat_user_tables: postgresql.org/docs/16/monitoring-stats.html
• توثيق CREATE INDEX CONCURRENTLY: postgresql.org/docs/16/sql-createindex.html