ترتيب أعمدة الـ Composite Index في PostgreSQL

لو عندك فهرس مركّب على (created_at, status) واستعلامك بيفلتر بـ status، الفهرس غالبًا مش بيتستخدم زي ما انت متخيّل. عكس ترتيب العمودين بس ينقّل نفس الاستعلام من 420ms لـ 2.8ms — من غير ما تغيّر سطر في الكود ولا تكبّر السيرفر.

هذا المقال يتطلب مستوى محترف. الافتراض إن عندك خبرة بـ SQL وEXPLAIN، وبتشتغل على جدول كبير (ملايين الصفوف) على PostgreSQL.

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

أغلب الـ ORMs بتسمحلك تعمل فهرس على أكتر من عمود بسطر واحد. الفريق بيفتكر إن أي استعلام بيلمس الأعمدة دي هيستفيد من الفهرس. ده غلط. الفهرس المركّب مش "مجموعة فهارس على كل عمود" — هو ترتيب واحد على تسلسل الأعمدة بالترتيب اللي كتبته بالظبط. لو الترتيب غلط، الـ planner بيتجاهل الفهرس ويرجع لـ Seq Scan على ملايين الصفوف.

المثال أولًا: دليل التليفونات

تخيّل دليل تليفونات ورقي مرتّب باسم العائلة الأول، وبعدين الاسم الشخصي. لو بتدوّر على "علي محمد"، بتلاقيه في ثواني: تفتح على حرف "ع" بتاع العائلة، وبعدين بتلاقي "علي" جوه صفحات العائلة. لكن لو طلبت منك "هاتلي كل الناس اللي اسمهم الشخصي محمد بغض النظر عن العائلة"، الدليل مايساعدكش خالص. هتضطر تقلب كل صفحة من الأول للآخر. مش لإن الأسماء مش موجودة، لكن لإن الترتيب مبني على العائلة الأول، والاسم الشخصي مبعتر جوه كل عائلة.

الفهرس المركّب بيشتغل بنفس المنطق بالظبط. الترتيب على العمود الأول هو اللي بيحكم، والعمود التاني مرتّب جوه قيم العمود الأول بس.

الشرح الدقيق: قاعدة الـ Leftmost Prefix

الفهرس المركّب في PostgreSQL هو B-tree مبني على tuple مرتّب: (col_a, col_b, col_c). الترتيب معجمي — يرتّب بـ col_a أولًا، وعند تساوي col_a يرتّب بـ col_b، وهكذا. النتيجة العملية إن الفهرس بيخدم بكفاءة الأنماط دي بس:

• WHERE col_a = ?
• WHERE col_a = ? AND col_b = ?
• WHERE col_a = ? AND col_b = ? AND col_c > ?

لكنه لا يخدم بكفاءة WHERE col_b = ? لوحده، لأن col_b مبعتر داخل قيم col_a — زي الاسم الشخصي في الدليل. ده اسمه قاعدة الـ leftmost prefix: الفهرس ينفع طالما استعلامك بيستخدم بادئة متصلة من الأعمدة من الشمال.

القاعدة العملية اللي بتحسم الترتيب اسمها Equality, Sort, Range (ESR): حُط أعمدة المساواة (=) الأول، بعدين عمود الترتيب (ORDER BY)، وآخر حاجة عمود المدى (>, <, BETWEEN). سبب الترتيب ده إن عمود المدى بيكسر إمكانية استغلال أي عمود بعده في الفهرس.

مثال تنفيذي بالأرقام

عندنا جدول orders فيه 12 مليون صف. حوالي 2% منهم حالتهم pending. الاستعلام المتكرر:

SELECT id, customer_id, total
FROM orders
WHERE status = 'pending'
  AND created_at >= now() - interval '7 days'
ORDER BY created_at DESC
LIMIT 50;

نبدأ بالفهرس الشائع الغلط — عمود المدى قبل عمود المساواة:

CREATE INDEX idx_orders_bad ON orders (created_at, status);

EXPLAIN (ANALYZE, BUFFERS)
SELECT ... ;  -- نفس الاستعلام فوق
-- Bitmap Heap Scan on orders  (actual time=412.7..418.9 rows=50)
--   Recheck Cond: (created_at >= ...)
--   Filter: (status = 'pending')
--   Rows Removed by Filter: 238412
-- Planning Time: 0.21 ms
-- Execution Time: 420.4 ms

لاحظ السطر Rows Removed by Filter: 238412. الفهرس قدر يقفز لنطاق الـ 7 أيام، لكنه اضطر يقرأ ربع مليون صف ويرمي 99% منهم لأن status مرتّب جوه التواريخ، مش قابل للـ seek.

دلوقتي نعكس الترتيب — المساواة الأول، بعدين عمود المدى/الترتيب:

CREATE INDEX idx_orders_good ON orders (status, created_at DESC);

EXPLAIN (ANALYZE, BUFFERS)
SELECT ... ;
-- Index Scan using idx_orders_good on orders  (actual time=0.05..2.61 rows=50)
--   Index Cond: (status = 'pending' AND created_at >= ...)
-- Planning Time: 0.19 ms
-- Execution Time: 2.8 ms

النتيجة: من 420ms لـ 2.8ms، أي تحسّن حوالي 150×. الفهرس قعد على status='pending' بالـ seek، وبعدين مشى على created_at بترتيب نازل جاهز للـ ORDER BY — فمفيش sort ولا rows removed.

الـ trade-offs اللي لازم تنتبه لها

• الفهرس مش مجاني. idx_orders_good هنا بياخد حوالي 280MB على القرص، وكل INSERT أو UPDATE بيلمس العمودين دول بيكلّف صيانة إضافية للـ B-tree. على جدول كتابة-كثيفة ده ثمن حقيقي.
• ترتيب واحد مايخدمش كل الأنماط. فهرس (status, created_at) ممتاز للاستعلام ده، لكنه مش هيفيد استعلام بيفلتر بـ created_at لوحده. هتحتاج تقرر أنماطك الأهم، مش تعمل فهرس لكل query.
• المكسب: ~150× في زمن الاستجابة. الثمن: ~280MB مساحة + كتابات أبطأ بنسبة بسيطة. لو الجدول قراءة-كثيفة، ده مكسب صافي. لو كتابة-كثيفة بقراءات نادرة، فكّر مرتين.

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

ترتيب أعمدة الفهرس مش هيفرق في الحالات دي: الجدول صغير (أقل من بضع آلاف صف) لأن الـ Seq Scan بيبقى أسرع أصلًا والـ planner هيتجاهل الفهرس؛ أو لما الـ selectivity واطية جدًا — يعني status='pending' بترجع 60% من الجدول، ساعتها الفهرس مش هيقلّل الشغل بشكل مفيد؛ أو لو الجدول كتابة-كثيفة وبيتقرأ نادر، فتكلفة صيانة الفهرس بتاكل أي مكسب.

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

شغّل EXPLAIN (ANALYZE, BUFFERS) على أبطأ استعلام عندك دلوقتي. لو شفت Seq Scan أو سطر Rows Removed by Filter برقم كبير، رتّب أعمدة الفهرس بقاعدة ESR: المساواة الأول، بعدين الترتيب، وآخر حاجة المدى. اعمل الفهرس على الإنتاج بـ CREATE INDEX CONCURRENTLY علشان متقفلش الجدول، وقارن الأرقام قبل وبعد.

المصادر

• PostgreSQL Documentation — Multicolumn Indexes: postgresql.org/docs/current/indexes-multicolumn.html
• PostgreSQL Documentation — Indexes and ORDER BY: postgresql.org/docs/current/indexes-ordering.html
• PostgreSQL Documentation — Using EXPLAIN: postgresql.org/docs/current/using-explain.html
• Markus Winand — Use The Index, Luke (Concatenated Indexes): use-the-index-luke.com