إيه الـ index وليه الـ Seq Scan بطيء، و EXPLAIN ANALYZE لأول مرة. الـ B-tree: = و < و ORDER BY، والـ composite index وقاعدة الـ leftmost prefix. الـ GIN: للـ arrays والـ jsonb والبحث جُوّه الكلام بـ pg_trgm. الـ BRIN: index بالكيلوبايت لجداول الوقت الضخمة. وتمن كل index: مساحة وبطء في الكتابة.
نص شرح المعلم والكود الذي كتبه ونتيجة تشغيله، بنفس ترتيب الدرس.
أهلًا بيك. seed dot sql بيعمل جدول orders فيه مِيتين ألف صف. لو سألت على orders مستخدم واحد، من غير index، Postgres لازم يقرا المِيتين ألف صف كلهم ويشوف كل واحد. ده اسمه Seq Scan. الـ index زي فهرس الكتاب: قايمة مترتبة بتقولك القيمة دي في أنهي صفحة. و EXPLAIN ANALYZE بيشغّل الـ query ويقولك عمل إيه بالزبط، وقفلنا الـ timing علشان الناتج يبقى تابت. مرة من غير index، وبعدين نعمل B-tree على user_id، ومرة تانية. نشغّل.
queries.sql
-- من غير index: Postgres لازم يقرا الجدول كله
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT * FROM orders WHERE user_id = 42;
CREATE INDEX orders_user_id ON orders (user_id);
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT * FROM orders WHERE user_id = 42;
-- index على أكتر من عمود: الترتيب مهم
CREATE INDEX orders_user_created ON orders (user_id, created_at DESC);
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT id, created_at FROM orders WHERE user_id = 42 ORDER BY created_at DESC LIMIT 3;
-- نفس الـ index ما ينفعش لو السؤال من غير user_id
EXPLAIN (COSTS OFF) SELECT * FROM orders WHERE created_at > '2026-05-01' LIMIT 3;
المخرجات
$ psql -q -f seed.sql -f queries.sql
QUERY PLAN
---------------------------------------------
Seq Scan on orders (actual rows=40 loops=1)
Filter: (user_id = 42)
Rows Removed by Filter: 199960
(3 rows)
QUERY PLAN
--------------------------------------------------------------------
Bitmap Heap Scan on orders (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)
(5 rows)
QUERY PLAN
------------------------------------------------------------------------------
Limit (actual rows=3 loops=1)
-> Index Scan using orders_user_created on orders (actual rows=3 loops=1)
Index Cond: (user_id = 42)
(3 rows)
QUERY PLAN
-----------------------------------------------------------------------------------
Limit
-> Seq Scan on orders
Filter: (created_at > '2026-05-01 00:00:00+00'::timestamp with time zon
…
من غير index: Seq Scan، و Rows Removed by Filter: مِيّة تسعة وتسعين ألف وتسعمية وستين تقريبًا، يعني قرا كل حاجة علشان يلاقي أربعين. بعد الـ index: Bitmap Index Scan، راح على الأربعين على طول. والـ composite على user_id و created_at: Index Scan، ومفيش Sort، و LIMIT وقف بعد تلاتة، لأن الـ index أصلًا مترتب بالوقت جُوّه كل مستخدم. بس لما سألنا بالـ created_at لوحده: Seq Scan تاني، لأن الـ index مترتب بـ user_id الأول. الـ B-tree ممتاز للمساواة والمدى والترتيب. بس فيه أسئلة ما يعرفهاش. إنك تدوّر على كلمة في نص الاسم بـ ILIKE و two percent signs: الـ B-tree بيعرف يدوّر من أول الكلمة بس. و array فيها tag معين. للحاجات دي فيه GIN: index بيفك كل قيمة لأجزاء. pg_trgm بيقسّم الكلام لتلات حروف تلات حروف، فيعرف يدوّر جُوّه أي كلمة. و GIN على الـ tags.
نشغّل.
queries.sql
-- GIN: index لحاجات جُوّاها حاجات (arrays، jsonb، كلام)
CREATE EXTENSION IF NOT EXISTS pg_trgm;
DROP TABLE IF EXISTS customers;
CREATE TABLE customers (id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name text, tags text[]);
INSERT INTO customers (name, tags)
SELECT 'customer_' || i || CASE WHEN i % 1000 = 0 THEN '_mona' ELSE '' END,
CASE WHEN i % 7 = 0 THEN ARRAY['vip'] ELSE ARRAY['regular'] END
FROM generate_series(1, 100000) AS i;
CREATE INDEX customers_name_btree ON customers (name);
CREATE INDEX customers_name_trgm ON customers USING gin (name gin_trgm_ops);
CREATE INDEX customers_tags ON customers USING gin (tags);
ANALYZE customers;
-- B-tree ما بيساعدش في '%…%'، الـ trigram GIN بيساعد
EXPLAIN (COSTS OFF) SELECT count(*) FROM customers WHERE name ILIKE '%mona%';
SELECT count(*) FROM customers WHERE name ILIKE '%mona%';
EXPLAIN (COSTS OFF) SELECT count(*) FROM customers WHERE tags @> ARRAY['vip'];
المخرجات
$ psql -q -f seed.sql -f queries.sql
QUERY PLAN
------------------------------------------------------
Aggregate
-> Bitmap Heap Scan on customers
Recheck Cond: (name ~~* '%mona%'::text)
-> Bitmap Index Scan on customers_name_trgm
Index Cond: (name ~~* '%mona%'::text)
(5 rows)
count
-------
100
(1 row)
QUERY PLAN
-----------------------------------------------------
Aggregate
-> Bitmap Heap Scan on customers
Recheck Cond: (tags @> '{vip}'::text[])
-> Bitmap Index Scan on customers_tags
Index Cond: (tags @> '{vip}'::text[])
(5 rows)
ILIKE percent mona percent: Bitmap Index Scan على الـ trigram index، مش الـ B-tree، ولقى مِيّة. و tags الـ contains operator vip: الـ GIN بتاع الـ tags. من غير pg_trgm، أي search box في الموقع بـ LIKE كان هيعمل Seq Scan على الجدول كله في كل حرف المستخدم بيكتبه. BRIN. جدول بيكبر بالوقت: orders و logs و events. الصفوف بتتكتب بالترتيب، فالـ created_at مترتب طبيعي على الـ disk. الـ BRIN بيخزن أقل وأكبر قيمة لكل مجموعة صفحات بس، فحجمه صغير جدًّا. هنعمل B-tree و BRIN على نفس العمود ونقارن الحجم، وبعدين نسأل على يوم واحد بالـ BRIN. نشغّل.
queries.sql
-- BRIN: لجداول ضخمة البيانات فيها مترتبة طبيعي (زي الوقت)
CREATE INDEX orders_created_btree ON orders (created_at);
CREATE INDEX orders_created_brin ON orders USING brin (created_at);
SELECT indexrelname AS index, pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes WHERE relname = 'orders' ORDER BY pg_relation_size(indexrelid) DESC;
DROP INDEX orders_created_btree;
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT count(*) FROM orders WHERE created_at BETWEEN '2026-03-01' AND '2026-03-02';
المخرجات
$ psql -q -f seed.sql -f queries.sql
index | size
----------------------+---------
orders_pkey | 4408 kB
orders_created_btree | 4408 kB
orders_created_brin | 24 kB
(3 rows)
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (actual rows=1 loops=1)
-> Bitmap Heap Scan on orders (actual rows=1441 loops=1)
Recheck 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))
Rows Removed by Index Recheck: 13919
Heap Blocks: lossy=128
-> Bitmap Index Scan on orders_created_brin (actual rows=1280 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))
(7 rows)
الـ B-tree على created_at حوالي أربعة ميجا ونص، والـ BRIN أربعة وعشرين كيلو! وسؤال اليوم الواحد: Bitmap Index Scan على الـ BRIN، والـ Rows Removed by Index Recheck بيقول إنه قرا شوية صفوف زيادة وفلترها، وده التمن. BRIN ممتاز للجداول الضخمة المترتبة طبيعي، ووحش لو البيانات متلخبطة. وخد بالك من التمن. كل index بيتعدّل مع كل INSERT و UPDATE. خمس indexes على جدول يعني كل كتابة بتكتب ست مرات. والعمل على الإنتاج: CREATE INDEX العادي بيقفل الكتابة على الجدول لحد ما يخلص، فالموقع يقف. استخدم CREATE INDEX CONCURRENTLY، أبطأ بس من غير قفل. وده هنرجعله في درس الـ migrations. التحدي: أقل indexes لأسرع queries. تلات queries بتتشغّل آلاف المرات: آخر الـ pending. orders مستخدم بحالة معينة. ومجموع يوم واحد. اعمل أقل indexes تخلي التلاتة من غير Seq Scan ولا Sort، وفكّر في الترتيب جُوّه كل composite index. الشروط في الكومنت.
تمرين
اعمل indexes: (status, created_at DESC) و (user_id, status) و BRIN على created_at، و ANALYZE، واتأكد إن الـ EXPLAINs التلاتة من غير Seq Scan ولا Sort.
الحل. status و created_at تنازلي: بيجيب الـ pending مترتبة. user_id و status: user_id الأول لأنه أكتر عمود بيفرّق، يعني فيه قيم كتير مختلفة. و BRIN للوقت. و ANALYZE علشان Postgres يعرف الإحصائيات الجديدة. نشغّل.
queries.sql
CREATE INDEX orders_status_created ON orders (status, created_at DESC);
CREATE INDEX orders_user_status ON orders (user_id, status);
CREATE INDEX orders_created_brin ON orders USING brin (created_at);
ANALYZE orders;
EXPLAIN (COSTS OFF) SELECT id FROM orders WHERE status = 'pending' ORDER BY created_at DESC LIMIT 20;
EXPLAIN (COSTS OFF) SELECT id FROM orders WHERE user_id = 7 AND status = 'paid';
EXPLAIN (COSTS OFF) SELECT sum(total_cents) FROM orders WHERE created_at >= '2026-04-01' AND created_at < '2026-04-02';
SELECT indexrelname AS index, pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes WHERE relname = 'orders' ORDER BY pg_relation_size(indexrelid) DESC;
المخرجات
$ psql -q -f seed.sql -f queries.sql
QUERY PLAN
--------------------------------------------------------
Limit
-> Index Scan using orders_status_created on orders
Index Cond: (status = 'pending'::text)
(3 rows)
QUERY PLAN
-----------------------------------------------------------------
Bitmap Heap Scan on orders
Recheck Cond: ((user_id = 7) AND (status = 'paid'::text))
-> Bitmap Index Scan on orders_user_status
Index Cond: ((user_id = 7) AND (status = 'paid'::text))
(4 rows)
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate
-> Bitmap Heap Scan on orders
Recheck Cond: ((created_at >= '2026-04-01 00:00:00+00'::timestamp with time zone) AND (created_at < '2026-04-02 00:00:00+00'::timestamp with time zone))
-> Bitmap Index Scan on orders_cre
…
التلاتة بـ indexes، والأولى من غير Sort، والـ BRIN أصغرهم بكتير. الدرس الجاي: الـ partial indexes والـ covering indexes، يعني indexes أصغر وأشطر.