B-tree Indexes في PostgreSQL للمبتدئ: من 4.2 ثانية لـ 5 مللي ثانية بسطر واحد

-- 1) إنشاء الجدول وحقنه ببيانات اختبار
CREATE TABLE users (
  id BIGSERIAL PRIMARY KEY,
  email TEXT NOT NULL,
  full_name TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

INSERT INTO users (email, full_name)
SELECT 
  'user_' || g || '@example.com',
  'User ' || g
FROM generate_series(1, 2000000) AS g;

-- 2) قبل الـ Index — Sequential Scan
EXPLAIN ANALYZE
SELECT * FROM users WHERE email = 'user_1999998@example.com';

-- Seq Scan on users  (cost=0.00..43196.00 rows=1 width=58)
--   Filter: (email = 'user_1999998@example.com'::text)
--   Rows Removed by Filter: 1999999
-- Execution Time: 4187.342 ms

-- 3) إنشاء الـ Index في سطر واحد
CREATE INDEX idx_users_email ON users (email);

-- 4) بعد الـ Index — Index Scan
EXPLAIN ANALYZE
SELECT * FROM users WHERE email = 'user_1999998@example.com';

-- Index Scan using idx_users_email on users  (cost=0.43..8.45 rows=1 width=58)
--   Index Cond: (email = 'user_1999998@example.com'::text)
-- Execution Time: 4.812 ms

الفرق هنا واضح: Sequential Scan قرا 1,999,999 صف وفضل يفلتر، الـ Index Scan راح للصف المطلوب مباشرة. 4,187ms بقت 4.8ms. ده مش رقم نظري، ده مقاس على PostgreSQL 16 على Hetzner CPX21 (4 vCPU, 8GB RAM, NVMe).

ازاي تقرا EXPLAIN ANALYZE بدون لخبطة

لما تكتب EXPLAIN ANALYZE قبل query، PostgreSQL بينفّذه ويرجّعلك خطة التنفيذ مع أرقام حقيقية. ركّز في 3 حاجات:

1. نوع الـ Scan: لو شفت "Seq Scan" على جدول كبير في WHERE، ده علم أحمر. لو شفت "Index Scan" أو "Index Only Scan" أو "Bitmap Heap Scan"، الـ DB بيستخدم index.
2. Rows Removed by Filter: الرقم ده بيقولّك كم صف اتقرا واتفلتر بدون فايدة. لو الرقم 1,999,999 معناها قريت الجدول كله علشان ترجع صف واحد.
3. Execution Time: الزمن الفعلي بالـ مللي ثانية. ده اللي بتقارن قبله وبعده.

الـ Trade-offs: إيه الثمن اللي بتدفعه

الـ Index مش حاجة مجانية. كل توصية معاها ثمنها، وده اللي لازم تفهمه قبل ما تعمل CREATE INDEX على كل عمود في الـ schema:

1. الـ INSERT و UPDATE بيبقوا أبطأ. كل index زيادة معناه إن الـ DB لازم يحدّث الشجرة مع كل عملية كتابة. على جدول فيه 5 indexes، الـ INSERT ممكن يبقى أبطأ 30-40% من جدول بدون index.
2. المساحة على القرص بتزيد. Index على عمود text عربي بياخد تقريباً 30-50% من حجم العمود الأصلي. على جدول 50GB، 4 indexes ممكن يضيفوا 20-25GB إضافية.
3. الـ Index بيبهت مع الوقت. مع كثرة الـ UPDATE، الشجرة بتدخل في حالة اسمها bloat وأداؤها بينخفض. لازم REINDEX دوري كل شهر-شهرين على جداول write-heavy.
4. الـ Index مش بيشتغل دايماً. لو كتبت WHERE LOWER(email) = ...، الـ index على email مش هيشتغل. لازم تعمل functional index: CREATE INDEX ON users (LOWER(email)).

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

الـ Index غير مفيد، وأحياناً ضار، في الحالات دي:

• جدول صغير (أقل من 10,000 صف). الـ Sequential Scan أسرع من الـ Index Scan على الأحجام دي، لأن الجدول كله بيقع جوّه الذاكرة.
• عمود فيه قيم متكررة كتير (Low Cardinality). مثلاً عمود is_active فيه قيمتين بس true/false. الـ B-tree هنا مش هيوفّر حاجة. لو محتاج تفلتر على عمود زي ده، استخدم Partial Index.
• Workload كتابة بحت. لو الجدول بياخد 50,000 INSERT في الثانية و قراءة نادرة جداً، تكلفة تحديث الشجرة هتأكل كل مكسب.
• أنت بتعمل SELECT * بدون WHERE. مفيش index في الدنيا هيساعدك هنا. الـ DB لازم يقرا كل الصفوف على أي حال.

المصادر

• PostgreSQL 16 Official Documentation — Chapter 11: Indexes (postgresql.org/docs/16/indexes.html).
• Rudolf Bayer, Edward McCreight — "Organization and Maintenance of Large Ordered Indexes", Acta Informatica, 1972.
• Markus Winand — "Use The Index, Luke!" (use-the-index-luke.com)، مرجع مفصّل عن الـ B-tree في الإنتاج.
• PostgreSQL Wiki — "Performance Optimization" و "Index Maintenance" (wiki.postgresql.org).

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

افتح أبطأ query في تطبيقك دلوقتي، حط قبله EXPLAIN ANALYZE وشغّله. لو لقيت "Seq Scan" على جدول كبير و"Rows Removed by Filter" بأرقام عالية، انت قدام مرشّح ممتاز لـ index. اعمل CREATE INDEX على العمود اللي في الـ WHERE، وأعد قياس الـ EXPLAIN ANALYZE. لو الفرق مش 10× على الأقل، فيه حاجة تانية محتاجة تتراجع — اكتبلي تفاصيل خطة التنفيذ.