دیتابیس و SQLدیتابیس در کار واقعیدرس ۷ از ۱۰

ایندکس و سرعت کوئری

کوئری کند را سریع کن: ساختن یک جدول یک‌میلیون‌سطری با generate_series، اندازه‌گیری زمان با \timing، خواندن نقشه‌ی اجرا با EXPLAIN و EXPLAIN ANALYZE، ساختن ایندکس، ایندکس چندستونی و قانون ستون اول، وقتی ایندکس استفاده نمی‌شود، هزینه‌ی ایندکس برای نوشتن و فضا، و ایندکس روی کلید خارجی.

حدود ۲۲ دقیقه مطالعه
۹ مثال اجراشده
۳ تمرین با جواب
مقدماتی تا متوسط
به‌روزرسانی: ۲۹ مهر ۱۴۰۵

توی این درس یاد می‌گیری چرا یک کوئری روی جدول بزرگ کند است و چطور با ایندکس آن را صدها برابر سریع کنی. یک جدول لاگ یک‌میلیون‌سطری می‌سازی، زمان کوئری را با \timing اندازه می‌گیری، با EXPLAIN می‌بینی دیتابیس واقعاً چه کار می‌کند (کل جدول را می‌خواند یا از ایندکس استفاده می‌کند)، ایندکس تک‌ستونی و چندستونی می‌سازی، و یاد می‌گیری کجا ایندکس کمکی نمی‌کند و چه هزینه‌ای دارد.

مسئله: روی ۱۲ سطر همه‌چیز سریع است

تا اینجا جدول‌هایمان چند سطر داشتند و هر کوئری فوری جواب می‌داد. روی یک سرور واقعی، جدول لاگ یا سفارش میلیون‌ها سطر دارد. کوئری WHERE ip = '...' که دیروز ۱ میلی‌ثانیه طول می‌کشید، حالا ۶۰ میلی‌ثانیه؛ با صد کاربر هم‌زمان، CPU سرور دیتابیس پر می‌شود و سایت کند. اغلب راه‌حل نه سرور قوی‌تر است و نه بازنویسی برنامه؛ یک خط CREATE INDEX است.

تشبیه: فهرست آخر کتاب

برای پیدا کردن همه‌ی صفحه‌هایی که کلمه‌ی «SSH» در یک کتاب ۸۰۰ صفحه‌ای آمده، دو راه داری: همه‌ی ۸۰۰ صفحه را ورق بزنی (Seq Scan)، یا به فهرست الفبایی آخر کتاب بروی، «SSH» را پیدا کنی (چون الفبایی است، سریع) و مستقیم سراغ صفحه‌های ۱۲۰ و ۳۴۵ بروی (Index Scan). ایندکس دیتابیس همان فهرست است: یک ساختار مرتب جدا از جدول که برای هر مقدار می‌گوید سطرش کجاست. فهرست هم مجانی نیست: جا می‌گیرد، و هر بار صفحه‌ای به کتاب اضافه شود، باید به‌روزش کنی.

ایندکس B-tree: ریشه و شاخه‌ها مقدارها را مرتب نگه می‌دارند و برگ‌ها به محل سطر در جدول اشاره می‌کنند؛ پیدا کردن یک مقدار بین یک میلیون فقط سه یا چهار قدم است.

مثال‌های عملی

مثال ۱: یک جدول بزرگ بساز

برای دیدن اثر ایندکس، داده‌ی زیاد لازم است. با generate_series (تابعی که یک سری عدد می‌سازد) و random() یک میلیون سطر لاگ ساختگی بساز. این را در ~/logs.sql بنویس:

DROP TABLE IF EXISTS access_log;
CREATE TABLE access_log (
    id BIGSERIAL PRIMARY KEY,
    ip TEXT NOT NULL,
    path TEXT NOT NULL,
    status INTEGER NOT NULL,
    bytes INTEGER NOT NULL,
    at TIMESTAMPTZ NOT NULL
);
SELECT setseed(0.42);
INSERT INTO access_log (ip, path, status, bytes, at)
SELECT '10.0.' || (random() * 255)::int || '.' || (random() * 255)::int,
       (ARRAY['/', '/login', '/products', '/cart', '/api/orders', '/about'])[1 + (random() * 5)::int],
       (ARRAY[200, 200, 200, 200, 200, 200, 200, 301, 404, 500])[1 + (random() * 9)::int],
       (random() * 50000)::int,
       '2026-10-01'::timestamptz + random() * interval '20 days'
FROM generate_series(1, 1000000);
ANALYZE access_log;

خط به خط: generate_series(1, 1000000) یک میلیون سطر تولید می‌کند و SELECT برای هر کدام مقدارهای تصادفی می‌سازد: IP تصادفی، یکی از شش مسیر از یک آرایه (ARRAY[...][شماره])، وضعیتی که ۲۰۰ در آن بیشتر است، و زمانی در ۲۰ روز اول اکتبر. setseed باعث می‌شود «تصادفی»‌ها همیشه یکسان باشند تا عددهایت با درس بخواند. ANALYZE (پایین‌تر توضیح داده شده) آمار جدول را جمع می‌کند.

psql shop -f ~/logs.sql

psql:/home/ali/logs.sql:1: NOTICE:  table "access_log" does not exist, skipping
DROP TABLE
CREATE TABLE
 setseed
---------

(1 row)

INSERT 0 1000000
ANALYZE

چند ثانیه طول می‌کشد. حالا در psql:

SELECT count(*) FROM access_log;
SELECT pg_size_pretty(pg_total_relation_size('access_log'));

  count
---------
 1000000
(1 row)

 pg_size_pretty
----------------
 94 MB
(1 row)

مثال ۲: اندازه بگیر

\timing on در psql زمان هر دستور را چاپ می‌کند:

\timing on
SELECT count(*) FROM access_log WHERE ip = '10.0.7.42';

Timing is on.
 count
-------
    15
(1 row)

Time: 59.557 ms

۶۰ میلی‌ثانیه برای پیدا کردن ۱۵ سطر. عددهای زمان روی سیستم تو فرق می‌کنند؛ چیزی که مهم است نسبت قبل و بعد است.

مثال ۳: `EXPLAIN ANALYZE`، دیتابیس چه کرد؟

EXPLAIN نقشه‌ای را که دیتابیس برای اجرای کوئری انتخاب کرده نشان می‌دهد؛ EXPLAIN ANALYZE کوئری را واقعاً اجرا می‌کند و زمان واقعی هر مرحله را هم می‌گوید:

EXPLAIN ANALYZE SELECT * FROM access_log WHERE ip = '10.0.7.42';

                                                        QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..15516.93 rows=16 width=43) (actual time=5.744..60.260 rows=15 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on access_log  (cost=0.00..14515.33 rows=7 width=43) (actual time=17.641..49.360 rows=5 loops=3)
         Filter: (ip = '10.0.7.42'::text)
         Rows Removed by Filter: 333328
 Planning Time: 0.094 ms
 Execution Time: 60.291 ms
(8 rows)

از پایین‌ترین و داخلی‌ترین خط شروع کن:

Parallel Seq Scan on access_log: کل جدول را خط به خط خواند (Seq یعنی sequential، ترتیبی)، با سه پروسه‌ی موازی (loops=3).
Rows Removed by Filter: 333328: هر پروسه حدود ۳۳۳ هزار سطر را خواند و دور ریخت؛ یعنی ۹۹۹٬۹۸۵ سطر بیهوده خوانده شد تا ۱۵ تا پیدا شود.
Gather نتیجه‌ی پروسه‌ها را جمع کرد.
cost=... تخمین دیتابیس از هزینه است (واحد دلخواه)، rows=16 تخمین تعداد سطر (واقعی: ۱۵؛ تخمین خوبی است، به لطف ANALYZE) و actual time زمان واقعی به میلی‌ثانیه.

مثال ۴: ایندکس بساز

CREATE INDEX access_log_ip_idx ON access_log (ip);
SELECT count(*) FROM access_log WHERE ip = '10.0.7.42';
EXPLAIN ANALYZE SELECT * FROM access_log WHERE ip = '10.0.7.42';

CREATE INDEX
Time: 997.687 ms
 count
-------
    15
(1 row)

Time: 0.877 ms
                                                         QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on access_log  (cost=4.55..66.76 rows=16 width=43) (actual time=0.015..0.032 rows=15 loops=1)
   Recheck Cond: (ip = '10.0.7.42'::text)
   Heap Blocks: exact=15
   ->  Bitmap Index Scan on access_log_ip_idx  (cost=0.00..4.54 rows=16 width=0) (actual time=0.011..0.011 rows=15 loops=1)
         Index Cond: (ip = '10.0.7.42'::text)
 Planning Time: 0.079 ms
 Execution Time: 0.047 ms
(7 rows)

ساختن ایندکس یک ثانیه طول کشید (یک بار)؛ کوئری از ۶۰ به کمتر از ۱ میلی‌ثانیه رسید. نقشه حالا Bitmap Index Scan on access_log_ip_idx است: ایندکس جای ۱۵ سطر را داد و بعد فقط همان ۱۵ بلوک از جدول خوانده شد (Heap Blocks: exact=15). Heap اسم خود جدول است. (اسم ایندکس را خودت انتخاب می‌کنی؛ قرارداد رایج: جدول_ستون_idx.)

کلید اصلی خودش ایندکس دارد، برای همین جست‌وجو با id از اول سریع بود:

\timing off
EXPLAIN SELECT * FROM access_log WHERE id = 500000;

Timing is off.
                                    QUERY PLAN
-----------------------------------------------------------------------------------
 Index Scan using access_log_pkey on access_log  (cost=0.42..8.44 rows=1 width=43)
   Index Cond: (id = 500000)
(2 rows)

⚡ بررسی سریع

در خروجی EXPLAIN خط «Seq Scan on orders» با «Rows Removed by Filter: 2999990» دیدی. یعنی چه؟

جواب درست: کل جدول خوانده شده و تقریباً همه‌اش دور ریخته شده؛ احتمالاً ایندکس لازم است

Seq Scan با فیلتر شدید روی جدول بزرگ، نشانه‌ی کلاسیک ایندکس گم‌شده است.

مثال ۵: ایندکس چندستونی و قانون ستون اول

سؤال رایج در لاگ: «درخواست‌های /login در یک روز مشخص». ایندکسی روی هر دو ستون:

CREATE INDEX access_log_path_at_idx ON access_log (path, at);
EXPLAIN SELECT count(*) FROM access_log
WHERE path = '/login' AND at >= '2026-10-10' AND at < '2026-10-11';

                                                                                    QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=429.50..429.51 rows=1 width=8)
   ->  Index Only Scan using access_log_path_at_idx on access_log  (cost=0.42..404.19 rows=10123 width=0)
         Index Cond: ((path = '/login'::text) AND (at >= '2026-10-10 00:00:00+03:30'::timestamp with time zone) AND (at < '2026-10-11 00:00:00+03:30'::timestamp with time zone))
(3 rows)

Index Only Scan: جواب کامل در خود ایندکس بود (فقط شمارش لازم بود) و حتی سراغ جدول نرفت. ایندکس چندستونی مثل دفترچه‌ی تلفنی است که اول بر اساس نام خانوادگی و بعد نام مرتب شده: پیدا کردن «رضایی، علی» سریع است، پیدا کردن «همه‌ی رضایی‌ها» هم سریع است، ولی «همه‌ی علی‌ها» نه، چون علی‌ها در کل دفترچه پخش‌اند. همین‌جا:

EXPLAIN SELECT count(*) FROM access_log WHERE at >= '2026-10-10' AND at < '2026-10-11';

                                                                          QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=16609.49..16609.50 rows=1 width=8)
   ->  Gather  (cost=16609.27..16609.48 rows=2 width=8)
         Workers Planned: 2
         ->  Partial Aggregate  (cost=15609.27..15609.28 rows=1 width=8)
               ->  Parallel Seq Scan on access_log  (cost=0.00..15557.00 rows=20909 width=0)
                     Filter: ((at >= '2026-10-10 00:00:00+03:30'::timestamp with time zone) AND (at < '2026-10-11 00:00:00+03:30'::timestamp with time zone))
(6 rows)

شرط فقط روی at (ستون دوم) است و ایندکس (path, at) به کار نیامد: Seq Scan. قانون: ایندکس چندستونی برای شرط‌هایی مفید است که از ستون اول شروع شوند. ترتیب ستون‌ها مهم است: ستونی که همیشه با = در شرط هست اول، ستون بازه‌ای (>=، <) آخر.

(در خروجی می‌بینی '2026-10-10' به 00:00:00+03:30 تبدیل شده: نیمه‌شب به وقت تهران، منطقه‌ی زمانی سرور. TIMESTAMPTZ همین را برایت مدیریت می‌کند.)

مثال ۶: وقتی ایندکس هست ولی استفاده نمی‌شود

یک ایندکس تک‌ستونی روی at بساز و این دو کوئری هم‌معنی را مقایسه کن:

CREATE INDEX access_log_at_idx ON access_log (at);
EXPLAIN SELECT count(*) FROM access_log WHERE at::date = '2026-10-10';
EXPLAIN SELECT count(*) FROM access_log WHERE at >= '2026-10-10' AND at < '2026-10-11';

CREATE INDEX
                                         QUERY PLAN
--------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=16562.42..16562.43 rows=1 width=8)
   ->  Gather  (cost=16562.21..16562.42 rows=2 width=8)
         Workers Planned: 2
         ->  Partial Aggregate  (cost=15562.21..15562.22 rows=1 width=8)
               ->  Parallel Seq Scan on access_log  (cost=0.00..15557.00 rows=2083 width=0)
                     Filter: ((at)::date = '2026-10-10'::date)
(6 rows)

                                                                      QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=1681.50..1681.51 rows=1 width=8)
   ->  Index Only Scan using access_log_at_idx on access_log  (cost=0.42..1556.04 rows=50181 width=0)
         Index Cond: ((at >= '2026-10-10 00:00:00+03:30'::timestamp with time zone) AND (at < '2026-10-11 00:00:00+03:30'::timestamp with time zone))
(3 rows)

at::date (تبدیل به تاریخ) روی ستون اعمال شده؛ ایندکس مقدارهای at را مرتب نگه می‌دارد، نه at::date را، پس به کار نمی‌آید. نسخه‌ی دوم ستون را دست‌نخورده می‌گذارد و یک بازه می‌پرسد: Index Only Scan. قانون: در WHERE روی ستون ایندکس‌دار تابع نزن؛ تبدیل را سمت مقدار انجام بده. (اگر واقعاً لازم است، می‌شود روی خود عبارت ایندکس ساخت: CREATE INDEX ... ON customers (lower(email)).)

موارد دیگری که ایندکس معمولی کمکی نمی‌کند:

EXPLAIN SELECT * FROM access_log WHERE ip LIKE '%.42';

                                   QUERY PLAN
---------------------------------------------------------------------------------
 Gather  (cost=1000.00..16525.23 rows=10099 width=43)
   Workers Planned: 2
   ->  Parallel Seq Scan on access_log  (cost=0.00..14515.33 rows=4208 width=43)
         Filter: (ip ~~ '%.42'::text)
(4 rows)

الگویی که با % شروع می‌شود («هر چیزی که به ‎.42 ختم شود») در فهرست الفبایی پیدا نمی‌شود؛ مثل اینکه در فهرست کتاب دنبال «همه‌ی کلمه‌هایی که به ‎-ing ختم می‌شوند» بگردی. (~~ همان LIKE است.)

مثال ۷: ایندکس روی مقدار پرتکرار

SELECT status, count(*) FROM access_log GROUP BY status ORDER BY status;
CREATE INDEX access_log_status_idx ON access_log (status);
EXPLAIN SELECT * FROM access_log WHERE status = 200;
EXPLAIN SELECT * FROM access_log WHERE status = 500;

 status | count
--------+--------
    200 | 722165
    301 | 110910
    404 | 111345
    500 |  55580
(4 rows)

CREATE INDEX
                             QUERY PLAN
--------------------------------------------------------------------
 Seq Scan on access_log  (cost=0.00..21807.00 rows=723633 width=43)
   Filter: (status = 200)
(2 rows)

                                        QUERY PLAN
------------------------------------------------------------------------------------------
 Bitmap Heap Scan on access_log  (cost=623.07..10624.66 rows=55567 width=43)
   Recheck Cond: (status = 500)
   ->  Bitmap Index Scan on access_log_status_idx  (cost=0.00..609.18 rows=55567 width=0)
         Index Cond: (status = 500)
(4 rows)

یک ایندکس، دو تصمیم: برای status = 200 (۷۲٪ جدول) دیتابیس ایندکس را نادیده گرفت، چون وقتی بیشتر جدول را باید بخوانی، خواندن ترتیبی از پریدن بین ایندکس و جدول سریع‌تر است. برای 500 (۵٪) از ایندکس استفاده کرد. این تصمیم را planner (بخش برنامه‌ریز دیتابیس) با کمک آمار جدول می‌گیرد؛ همان آماری که ANALYZE جمع کرد (PostgreSQL به‌طور خودکار هم هر از گاهی ANALYZE می‌زند). ایندکس روی ستونی با چند مقدار پرتکرار معمولاً ارزش نگه‌داری ندارد:

DROP INDEX access_log_status_idx;

مثال ۸: هزینه‌ی ایندکس

\di+ access_log*

                                                 List of relations
 Schema |          Name          | Type  | Owner |   Table    | Persistence | Access method |  Size   | Description
--------+------------------------+-------+-------+------------+-------------+---------------+---------+-------------
 public | access_log_at_idx      | index | ali   | access_log | permanent   | btree         | 21 MB   |
 public | access_log_ip_idx      | index | ali   | access_log | permanent   | btree         | 8800 kB |
 public | access_log_path_at_idx | index | ali   | access_log | permanent   | btree         | 34 MB   |
 public | access_log_pkey        | index | ali   | access_log | permanent   | btree         | 21 MB   |
(4 rows)

چهار ایندکس با هم حدود ۸۵ مگابایت، تقریباً هم‌اندازه‌ی خود داده. و هر INSERT باید همه‌ی ایندکس‌ها را هم به‌روز کند. یک آزمایش: ۳۰۰ هزار سطر را در دو جدول کپی کن، یکی بدون ایندکس و یکی با همین چهار ایندکس (LIKE ... INCLUDING INDEXES ساختار و ایندکس‌ها را کپی می‌کند):

CREATE TABLE t_noidx (LIKE access_log);
CREATE TABLE t_idx (LIKE access_log INCLUDING INDEXES);
\timing on
INSERT INTO t_noidx SELECT * FROM access_log WHERE id <= 300000;
INSERT INTO t_idx SELECT * FROM access_log WHERE id <= 300000;
\timing off
DROP TABLE t_noidx, t_idx;

CREATE TABLE
CREATE TABLE
Timing is on.
INSERT 0 300000
Time: 308.489 ms
INSERT 0 300000
Time: 3908.091 ms (00:03.908)
Timing is off.
DROP TABLE

با چهار ایندکس، نوشتن بیش از ده برابر کندتر شد. برای همین ایندکس را برای کوئری‌هایی که واقعاً داری بساز، نه «برای احتیاط روی همه‌ی ستون‌ها».

مثال ۹: ایندکس روی کلید خارجی

PostgreSQL برای کلید اصلی و UNIQUE خودکار ایندکس می‌سازد، ولی برای کلید خارجی نه:

SELECT indexname FROM pg_indexes
WHERE tablename IN ('orders', 'order_items', 'customers', 'products')
ORDER BY 1;

    indexname
------------------
 customers_pkey
 order_items_pkey
 orders_pkey
 products_pkey
(4 rows)

orders.customer_id ایندکس ندارد، در حالی که «سفارش‌های یک مشتری» پرتکرارترین کوئری فروشگاه است (همان سناریو). روی جدول بزرگ، ایندکس کلیدهای خارجی را تقریباً همیشه می‌خواهی:

CREATE INDEX orders_customer_id_idx ON orders (customer_id);

(order_items.order_id لازم ندارد، چون ستون اول کلید اصلی (order_id, product_id) است.) روی جدول ۶ سطری ما planner باز هم Seq Scan را انتخاب می‌کند، و درست هم هست: خواندن یک بلوک کوچک از هر ایندکسی سریع‌تر است.

پشت پرده: B-tree و planner

ایندکس پیش‌فرض PostgreSQL B-tree است: یک درخت متوازن که مقدارها در آن مرتب‌اند. هر گره صدها کلید دارد، پس درخت خیلی کوتاه است: برای یک میلیون سطر فقط سه سطح. پیدا کردن یک مقدار یعنی سه یا چهار خواندن از دیسک (یا حافظه) به‌جای خواندن ۹۴ مگابایت. چون مرتب است، برای =، <، >، BETWEEN، ORDER BY و LIKE 'abc%' (شروع ثابت) هم کار می‌کند.

Planner برای هر کوئری چند نقشه‌ی ممکن می‌سازد (Seq Scan، Index Scan، Bitmap، …)، هزینه‌ی هر کدام را با کمک آمار (چند سطر، مقدارهای پرتکرار، توزیع مقدارها) تخمین می‌زند و ارزان‌ترین را انتخاب می‌کند. برای همین:

EXPLAIN (بدون ANALYZE) فقط تخمین است و کوئری را اجرا نمی‌کند؛ EXPLAIN ANALYZE اجرا می‌کند. روی UPDATE و DELETE، EXPLAIN ANALYZE واقعاً داده را عوض می‌کند (داخل BEGIN و ROLLBACK بزنش).
اگر آمار کهنه باشد (مثلاً بعد از وارد کردن میلیون‌ها سطر)، تخمین‌ها غلط و نقشه بد می‌شود؛ ANALYZE جدول درستش می‌کند.
روی جدول کوچک، نبودن ایندکس در نقشه مشکل نیست.

ایندکس خواندن را سریع و نوشتن را کند می‌کند؛ تعادل را کوئری‌های واقعی تعیین می‌کنند.

جدول‌های مرجع

دستور	کار
`CREATE INDEX n ON t (c);`	ایندکس تک‌ستونی
`CREATE INDEX n ON t (a, b);`	چندستونی (ترتیب مهم)
`CREATE UNIQUE INDEX n ON t (c);`	ایندکس یکتا
`CREATE INDEX n ON t (lower(c));`	ایندکس روی عبارت
`CREATE INDEX CONCURRENTLY n ON t (c);`	بدون قفل نوشتن روی جدول (کندتر؛ برای سرور واقعی)
`DROP INDEX n;`	حذف
`\di+`	فهرست و اندازه‌ی ایندکس‌ها
`ANALYZE t;`	به‌روز کردن آمار

در نقشه	معنی
`Seq Scan`	خواندن کل جدول
`Index Scan`	پیدا کردن با ایندکس و خواندن سطرها
`Index Only Scan`	جواب از خود ایندکس، بدون جدول
`Bitmap Index/Heap Scan`	اول جای همه‌ی سطرها از ایندکس، بعد خواندن بلوک‌ها به ترتیب
`Rows Removed by Filter`	سطرهای خوانده‌شده و دورریخته‌شده
`Hash Join` / `Nested Loop`	روش اتصال جدول‌ها

اشتباهات رایج

۱) ایندکس روی همه‌چیز

نوشتن ده برابر کندتر و دیسک دو برابر (مثال ۸). راه‌حل: با EXPLAIN ANALYZE کوئری‌های کند واقعی را پیدا کن و فقط برای آن‌ها ایندکس بساز.

۲) تابع روی ستون در `WHERE`

WHERE at::date = ... یا WHERE lower(email) = ... از ایندکس معمولی استفاده نمی‌کند. راه‌حل: بازه به‌جای تبدیل، یا ایندکس روی عبارت.

۳) ترتیب اشتباه ستون‌ها در ایندکس چندستونی

ایندکس (at, path) برای «/login در همه‌ی زمان‌ها» کمکی نمی‌کند. راه‌حل: ستون‌های = اول، ستون بازه آخر؛ ستونی که به‌تنهایی هم در شرط می‌آید اول.

۴) نتیجه‌گیری از جدول کوچک

روی ۱۲ سطر، ایندکس هیچ فرقی ندارد و planner هم استفاده‌اش نمی‌کند. راه‌حل: با داده‌ای به اندازه‌ی واقعی (مثل generate_series) آزمایش کن.

۵) `CREATE INDEX` روی سرور واقعی در ساعت شلوغ

CREATE INDEX معمولی تا تمام شدن، نوشتن روی جدول را قفل می‌کند؛ روی جدول صدمیلیونی یعنی دقیقه‌ها توقف ثبت سفارش. راه‌حل: CREATE INDEX CONCURRENTLY.

تمرین

تمرینآسان

با EXPLAIN ANALYZE زمان کوئری «تعداد درخواست‌هایی که بیشتر از ۴۹۹۰۰ بایت بوده‌اند» را بگیر. ایندکس مناسب بساز و دوباره اندازه بگیر. نوع اسکن قبل و بعد چه بود؟

دیدن جواب

EXPLAIN ANALYZE SELECT count(*) FROM access_log WHERE bytes > 49900;
CREATE INDEX access_log_bytes_idx ON access_log (bytes);
EXPLAIN ANALYZE SELECT count(*) FROM access_log WHERE bytes > 49900;

               ->  Parallel Seq Scan on access_log  (cost=0.00..14515.33 rows=767 width=0) (actual time=0.067..69.899 rows=664 loops=3)
                     Filter: (bytes > 49900)
                     Rows Removed by Filter: 332670
 Execution Time: 81.914 ms
...
   ->  Index Only Scan using access_log_bytes_idx on access_log  (cost=0.42..41.17 rows=1871 width=0) (actual time=0.017..0.125 rows=1991 loops=1)
         Index Cond: (bytes > 49900)
         Heap Fetches: 0
 Execution Time: 0.220 ms

قبل: Seq Scan و حدود ۸۰ میلی‌ثانیه. بعد: Index Only Scan (شمارش فقط به خود ایندکس نیاز داشت؛ Heap Fetches: 0 یعنی حتی یک بار سراغ جدول نرفت) و کمتر از ۱ میلی‌ثانیه. حدود ۲۰۰۰ سطر از یک میلیون (۰٫۲٪) انتخابی است؛ ایندکس ارزش دارد. اگر شرط bytes > 1000 بود (۹۸٪ جدول)، planner باز هم Seq Scan را انتخاب می‌کرد. امتحان کن.

تمرینمتوسط

کوئری «۱۰ درخواست آخر یک IP» (WHERE ip = '10.0.7.42' ORDER BY at DESC LIMIT 10) را بررسی کن. ایندکسی بساز که هم شرط و هم مرتب‌سازی را پوشش دهد و ثابت کن مرحله‌ی Sort از نقشه حذف می‌شود.

دیدن جواب

EXPLAIN SELECT * FROM access_log WHERE ip = '10.0.7.42' ORDER BY at DESC LIMIT 10;
CREATE INDEX access_log_ip_at_idx ON access_log (ip, at);
EXPLAIN SELECT * FROM access_log WHERE ip = '10.0.7.42' ORDER BY at DESC LIMIT 10;

                                         QUERY PLAN
---------------------------------------------------------------------------------------------
 Limit  (cost=67.08..67.10 rows=10 width=43)
   ->  Sort  (cost=67.08..67.12 rows=16 width=43)
         Sort Key: at DESC
         ->  Bitmap Heap Scan on access_log  (cost=4.55..66.76 rows=16 width=43)
               Recheck Cond: (ip = '10.0.7.42'::text)
               ->  Bitmap Index Scan on access_log_ip_idx  (cost=0.00..4.54 rows=16 width=0)
                     Index Cond: (ip = '10.0.7.42'::text)
(7 rows)

CREATE INDEX
                                               QUERY PLAN
---------------------------------------------------------------------------------------------------------
 Limit  (cost=0.42..43.10 rows=10 width=43)
   ->  Index Scan Backward using access_log_ip_at_idx on access_log  (cost=0.42..68.70 rows=16 width=43)
         Index Cond: (ip = '10.0.7.42'::text)
(3 rows)

در ایندکس (ip, at)، سطرهای هر IP از قبل بر اساس زمان مرتب‌اند؛ دیتابیس از آخر ایندکس (Backward) شروع می‌کند و بعد از ۱۰ سطر می‌ایستد. حالا access_log_ip_idx اضافی است (ایندکس جدید همه‌ی کارهایش را می‌کند، چون ip ستون اول آن است) و می‌شود حذفش کرد: DROP INDEX access_log_ip_idx;.

تمرینسخت

می‌خواهی کاربرها را با ایمیل پیدا کنی، بدون حساسیت به حروف بزرگ و کوچک (Sara@Example.com = sara@example.com). یک جدول members با ۲۰۰ هزار ایمیل ساختگی بساز، نشان بده WHERE lower(email) = '...' با ایندکس معمولی روی email کند است، و با یک ایندکس روی عبارت درستش کن. در آخر جدول‌های بزرگ را پاک کن.

دیدن جواب

CREATE TABLE members (id SERIAL PRIMARY KEY, email TEXT NOT NULL);
INSERT INTO members (email)
SELECT 'User' || n || '@Example.com' FROM generate_series(1, 200000) AS n;
CREATE INDEX members_email_idx ON members (email);
ANALYZE members;

EXPLAIN ANALYZE SELECT * FROM members WHERE lower(email) = 'user123456@example.com';
CREATE INDEX members_email_lower_idx ON members (lower(email));
EXPLAIN ANALYZE SELECT * FROM members WHERE lower(email) = 'user123456@example.com';

   ->  Parallel Seq Scan on members  (cost=0.00..3234.71 rows=588 width=26) (actual time=31.092..37.870 rows=0 loops=2)
         Filter: (lower(email) = 'user123456@example.com'::text)
         Rows Removed by Filter: 100000
 Execution Time: 42.750 ms
...
   ->  Bitmap Index Scan on members_email_lower_idx  (cost=0.00..27.92 rows=1000 width=0) (actual time=0.023..0.023 rows=1 loops=1)
         Index Cond: (lower(email) = 'user123456@example.com'::text)
 Execution Time: 0.143 ms

AS n به ستون generate_series اسم می‌دهد. ایندکس members_email_idx مقدار خام را مرتب نگه می‌دارد و به lower(email) کمکی نمی‌کند؛ ایندکس روی عبارت lower(email) دقیقاً همان چیزی را نگه می‌دارد که در WHERE می‌پرسی. (rows=1000 تخمین بدی است، چون هنوز برای عبارت جدید آمار نیست؛ بعد از ANALYZE members درست می‌شود.) (قدم بعدی: CREATE UNIQUE INDEX ... ON members (lower(email)) تا Sara@ و sara@ دو حساب جدا نشوند.) پاک‌سازی:

DROP TABLE members, access_log;

آزمونک

ایندکس دقیقاً چه چیزی است؟
فرق EXPLAIN و EXPLAIN ANALYZE؟
ایندکس (path, at) برای کدام شرط کمکی نمی‌کند؟
چرا WHERE at::date = ... از ایندکس روی at استفاده نمی‌کند؟
planner برای status = 200 (۷۲٪ سطرها) ایندکس را نادیده گرفت. چرا؟
PostgreSQL برای کدام خودکار ایندکس نمی‌سازد؟

جمع‌بندی

ایندکس یک ساختار مرتب (B-tree) است که پیدا کردن را از «خواندن کل جدول» به «چند قدم» می‌رساند.
اول اندازه بگیر: \timing و EXPLAIN ANALYZE؛ Seq Scan با Rows Removed by Filter بزرگ روی جدول بزرگ یعنی ایندکس گم‌شده.
ایندکس چندستونی: ستون‌های = اول، بازه آخر؛ فقط شرط‌هایی که از ستون اول شروع شوند.
تابع روی ستون، LIKE '%...' و شرط‌های غیرانتخابی از ایندکس معمولی سود نمی‌برند.
ایندکس هزینه دارد: فضا و کندی نوشتن؛ فقط برای کوئری‌های واقعی.
کلید خارجی خودکار ایندکس ندارد؛ برای جدول بزرگ بسازش.

برگه‌ی تقلب این درس

دستور	کاری که می‌کند
`\timing on`	نمایش زمان دستورها
`EXPLAIN ANALYZE SELECT ...`	نقشه و زمان واقعی
`CREATE INDEX t_c_idx ON t (c);`	ایندکس
`CREATE INDEX ON t (a, b);`	چندستونی
`CREATE INDEX ON t (lower(c));`	روی عبارت
`CREATE INDEX CONCURRENTLY ...`	بدون قفل نوشتن
`\di+`	اندازه‌ی ایندکس‌ها
`ANALYZE t;`	به‌روز کردن آمار

مسئله: روی ۱۲ سطر همه‌چیز سریع است

تشبیه: فهرست آخر کتاب

مثال‌های عملی

مثال ۱: یک جدول بزرگ بساز

مثال ۲: اندازه بگیر

مثال ۳: EXPLAIN ANALYZE، دیتابیس چه کرد؟

مثال ۴: ایندکس بساز

مثال ۵: ایندکس چندستونی و قانون ستون اول

مثال ۶: وقتی ایندکس هست ولی استفاده نمی‌شود

مثال ۷: ایندکس روی مقدار پرتکرار

مثال ۸: هزینه‌ی ایندکس

مثال ۹: ایندکس روی کلید خارجی

پشت پرده: B-tree و planner

جدول‌های مرجع

اشتباهات رایج

۱) ایندکس روی همه‌چیز

۲) تابع روی ستون در WHERE

۳) ترتیب اشتباه ستون‌ها در ایندکس چندستونی

۴) نتیجه‌گیری از جدول کوچک

۵) CREATE INDEX روی سرور واقعی در ساعت شلوغ

تمرین

آزمونک

جمع‌بندی

مثال ۳: `EXPLAIN ANALYZE`، دیتابیس چه کرد؟

۲) تابع روی ستون در `WHERE`

۵) `CREATE INDEX` روی سرور واقعی در ساعت شلوغ