EXPLAIN ANALYZE والـ Planner: اقرا الـ plan وصلّح الـ query

01 / ملخص الدرس

إزاي الـ planner بيختار plan بالـ cost والإحصائيات. قراية الـ plan من جُوّه لبرة: Seq Scan و Index Scan و Bitmap و Hash Join و Nested Loop و HashAggregate، والـ rows المتوقعة مقابل الحقيقية. الفخاخ: function على العمود (date و lower و extract)، والـ sort اللي بيروح للـ disk لما work_mem تبقى صغيرة. والحل: expression index و range بدل function و composite index للـ ORDER BY + LIMIT.

ضمن مسارباك إند Node.js للإنتاج — من تحت الصفر41 / 122

02 / شرح الدرس مكتوبًا

نص شرح المعلم والكود الذي كتبه ونتيجة تشغيله، بنفس ترتيب الدرس.

أهلًا بيك. لما تبعت query، Postgres ما بيشغّلهاش على طول. فيه جزء اسمه الـ planner، بيفكر في كل الطرق الممكنة: يقرا الجدول كله؟ يستخدم index؟ يعمل الـ JOIN بأنهي طريقة؟ وبيحسب لكل طريقة cost، رقم تقديري بيعتمد على إحصائيات عن البيانات، وبيختار الأرخص. و EXPLAIN ANALYZE بيورّيك الخطة اللي اختارها، وبيشغّلها فعلًا، وبيحط جنب كل خطوة: المتوقع، والحقيقي. query فيها JOIN و WHERE و GROUP BY. المرة دي سايبين الـ costs كمان. وفي أول سطر قفلنا الـ parallel query للـ session دي: Postgres ممكن يقسم الـ scan الكبير على كام worker، وده بيكبّر الـ plan، فهنبسّطه دلوقتي.

queries.sql

SET max_parallel_workers_per_gather = 0; -- من غير parallel، علشان الـ plan يبقى أبسط
-- اقرا الـ plan من جُوّه لبرة: كل سطر بيدّي نتيجته للي فوقه
EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF)
SELECT u.country, count(*)
FROM orders o JOIN users u ON u.id = o.user_id
WHERE o.status = 'failed'
GROUP BY u.country;

المخرجات

$ psql -q -f seed.sql -f queries.sql
                                               QUERY PLAN                                                
---------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=4332.11..4332.13 rows=2 width=11) (actual rows=1 loops=1)
   Group Key: u.country
   Batches: 1  Memory Usage: 24kB
   ->  Hash Join  (cost=149.50..4321.87 rows=2047 width=3) (actual rows=2000 loops=1)
         Hash Cond: (o.user_id = u.id)
         ->  Seq Scan on orders o  (cost=0.00..4167.00 rows=2047 width=8) (actual rows=2000 loops=1)
               Filter: (status = 'failed'::text)
               Rows Removed by Filter: 198000
         ->  Hash  (cost=87.00..87.00 rows=5000 width=11) (actual rows=5000 loops=1)
               Buckets: 8192  Batches: 1  Memory Usage: 299kB
               ->  Seq Scan on users u  (cost=0.00..87.00 rows=5000 width=11) (actual rows=5000 loops=1)
(11 rows)

اقرا من جُوّه لبرة، ومن تحت لفوق. جُوّه خالص: Seq Scan على orders بـ Filter الـ failed، والـ rows المتوقعة جنب الـ actual rows ألفين، والـ Rows Removed كتير. وجنبه Seq Scan على users، وفوقه Hash: بيبني جدول hash في الـ memory. وفوقهم Hash Join: بيوصل كل order بالمستخدم بتاعه من الـ hash. وفوق خالص: HashAggregate بيجمّع بالبلد. والأرقام اللي بين الـ parentheses: cost الأول للبداية والتاني للنهاية، والـ rows المتوقعة، و width حجم الصف بالـ bytes. أهم حاجة تبص عليها: المتوقع قريب من الحقيقي؟ لو بعيد جدًّا، الإحصائيات قديمة، والـ planner ممكن يختار غلط. نفس النوع من الـ JOIN، بس لمستخدم واحد، وفيه index على user_id. المرة دي الـ planner هيختار حاجة تانية.

queries.sql

CREATE INDEX orders_user_id ON orders (user_id);

-- مستخدم واحد ← Nested Loop مع index
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT o.id FROM users u JOIN orders o ON o.user_id = u.id WHERE u.id = 42;

المخرجات

$ psql -q -f seed.sql -f queries.sql
                                QUERY PLAN                                 
---------------------------------------------------------------------------
 Nested Loop (actual rows=40 loops=1)
   ->  Index Only Scan using users_pkey on users u (actual rows=1 loops=1)
         Index Cond: (id = 42)
         Heap Fetches: 1
   ->  Bitmap Heap Scan on orders o (actual rows=40 loops=1)
         Recheck Cond: (user_id = 42)
         Heap Blocks: exact=40
         ->  Bitmap Index Scan on orders_user_id (actual rows=40 loops=1)
               Index Cond: (user_id = 42)
(9 rows)

Nested Loop: لكل صف من برة، بيدوّر في الجوّا بـ index. مع مستخدم واحد، ده أرخص حاجة: Index Scan على الـ primary key، وبعدين الـ orders بتاعته من الـ index. الـ Hash Join مناسب للجداول الكبيرة مع بعض، والـ Nested Loop مناسب لما ناحية صغيرة جدًّا وفيه index. والـ planner بيختار لوحده، طول ما الإحصائيات صح. دلوقتي أشهر فخين. الأول: function على العمود. date بتاعة created_at تبان بريئة، بس الـ index متعمل على created_at نفسه، مش على date بتاعته، فـ Postgres مش هيعرف يستخدمه. والحل: اكتب الشرط كـ range. التاني: الـ sort. لو البيانات اللي هتترتب أكبر من work_mem، Postgres بيكتبها على الـ disk ويرتبها هناك، وده بطيء جدًّا.

queries.sql

SET max_parallel_workers_per_gather = 0; -- من غير parallel، علشان الـ plan يبقى أبسط
CREATE INDEX orders_created ON orders (created_at);

-- 1) function على العمود بتقتل الـ index
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT count(*) FROM orders WHERE date(created_at AT TIME ZONE 'UTC') = '2026-03-01';

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT count(*) FROM orders WHERE created_at >= '2026-03-01' AND created_at < '2026-03-02';

-- 2) sort أكبر من work_mem بيروح للـ disk
SET work_mem = '1MB';
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT id FROM orders ORDER BY total_cents DESC LIMIT 100000;
SET work_mem = '32MB';
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT id FROM orders ORDER BY total_cents DESC LIMIT 100000;

المخرجات

$ psql -q -f seed.sql -f queries.sql
                                     QUERY PLAN                                     
------------------------------------------------------------------------------------
 Aggregate (actual rows=1 loops=1)
   ->  Seq Scan on orders (actual rows=1440 loops=1)
         Filter: (date((created_at AT TIME ZONE 'UTC'::text)) = '2026-03-01'::date)
         Rows Removed by Filter: 198560
(4 rows)

                                                                           QUERY PLAN                                                                           
----------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate (actual rows=1 loops=1)
   ->  Index Only Scan using orders_created on orders (actual rows=1440 loops=1)
         Index Cond: ((created_at >= '2026-03-01 00:00:00+00'::timestamp with time zone) AND (created_at < '2026-03-02 00:00:00+00'::timestamp with time zone))
         Heap Fetches: 1440
(4 rows)

                         QUERY PLAN                          
-------------------------------------------------------------
 Limit (act
…

بالـ date: Seq Scan، مع إن فيه index. بالـ range: Index Only Scan على orders_created. نفس النتيجة، وفرق ضخم. والـ sort بـ ميجا work_mem: Sort Method external merge، و Disk، يعني راح للـ disk. وبـ اتنين وتلاتين ميجا: top-N heapsort أو quicksort في الـ Memory. بس ما ترفعش work_mem لكل الـ server بالعافية، لأنها لكل sort في كل connection، يعني مِيّة connection ممكن يستخدموا مِيّة ضعف. ارفعها للـ session اللي محتاجاها، أو أحسن: index يجيبها مترتبة. التحدي: صلّح الـ queries البطيئة. تلات queries من الإنتاج: A بتدوّر بـ lower على الإيميل. B بتجيب شهر بـ extract. C بتجيب آخر خمس orders لمستخدم بالإيميل. نشغّل، ونقرا كل plan، ولاقي ليه Seq Scan، وصلّح بـ index مناسب أو بإعادة كتابة الشرط، من غير ما النتيجة تتغيّر. التفاصيل في الكومنت.

تمرين

A: expression index على lower(email). B: اكتبها range على created_at + index. C: index على users(email) و orders(user_id, created_at DESC). ومفيش Seq Scan على orders.

الحل. expression index على lower email لـ A. B اتكتبت range، والنتيجة نفسها لأن الوقت UTC. و C: index على الإيميل، و composite على user_id و created_at تنازلي. و ANALYZE، وبعده نفس سطر الـ parallel. نشغّل.

queries.sql

CREATE INDEX users_email_lower ON users (lower(email));
CREATE INDEX users_email ON users (email);
CREATE INDEX orders_created ON orders (created_at);
CREATE INDEX orders_user_created ON orders (user_id, created_at DESC);
ANALYZE users;
ANALYZE orders;
SET max_parallel_workers_per_gather = 0; -- من غير parallel، علشان الـ plan يبقى أبسط

-- A
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT id FROM users WHERE lower(email) = '[email protected]';
-- B
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT count(*) FROM orders WHERE created_at >= '2026-02-01' AND created_at < '2026-03-01';
-- C
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT o.id, o.total_cents FROM orders o JOIN users u ON u.id = o.user_id
WHERE u.email = '[email protected]' ORDER BY o.created_at DESC LIMIT 5;

المخرجات

$ psql -q -f seed.sql -f queries.sql
                             QUERY PLAN                              
---------------------------------------------------------------------
 Index Scan using users_email_lower on users (actual rows=1 loops=1)
   Index Cond: (lower(email) = '[email protected]'::text)
(2 rows)

                                                                           QUERY PLAN                                                                           
----------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate (actual rows=1 loops=1)
   ->  Index Only Scan using orders_created on orders (actual rows=40320 loops=1)
         Index Cond: ((created_at >= '2026-02-01 00:00:00+00'::timestamp with time zone) AND (created_at < '2026-03-01 00:00:00+00'::timestamp with time zone))
         Heap Fetches: 40320
(4 rows)

                                        QUERY PLAN                                         
-------------------------------------------------------------------------------------------
 Limit (actual rows=5 loops=1)
   ->  Sort (actual rows=5 loops=1)
  
…

A: Index Scan using users_email_lower. B: Index Only Scan on orders_created. C: Nested Loop: المستخدم من users_email، والـ orders بتاعته من orders_user_created، وفوقهم Sort صغير، top-N heapsort، على أربعين صف بس في الـ memory. الـ planner شاف إن ترتيب أربعين صف أرخص من حاجة تانية، ولو المستخدم عنده آلاف الـ orders، كان هيقرا الـ index مترتب ويقف بعد خمسة. كده خلصنا جزء بنية البيانات في Postgres. القسم الجاي: Postgres في التشغيل: الـ transactions، والـ MVCC، والـ locks، والـ RLS، والـ migrations، والـ backups.

03 / دروس المسار (122)