B-tree از درون: ترتیب ستون‌ها، INCLUDE، partial و expression index

ایندکس یعنی یک ساختار دوم که باید نگهداری شود

ایندکس پیش‌فرض پستگرس B-tree است: درختی مرتب از مقدارها که هر برگش به ctid (آدرس فیزیکی ردیف در heap) اشاره می‌کند. هر ایندکس خواندن را سریع و هر INSERT و UPDATE را کمی کندتر می‌کند و فضای دیسک و حافظه‌ی cache می‌گیرد؛ پس سؤال درست «کدام کوئری به این ایندکس نیاز دارد؟» است، نه «کدام ستون را ایندکس کنم؟».

ترتیب ستون‌ها در ایندکس ترکیبی

ایندکس (customer_id, created_at) مثل دفتر تلفنی است که اول بر اساس نام خانوادگی و بعد نام مرتب شده: برای «سفارش‌های مشتری ۴۲» و «سفارش‌های مشتری ۴۲ در اسفند» عالی است، اما برای «همه‌ی سفارش‌های اسفند» تقریباً بی‌فایده. قاعده‌ی عملی: ستون‌هایی که با تساوی فیلتر می‌شوند اول، ستون بازه یا مرتب‌سازی آخر.

-- سه سفارش آخر هر مشتری: یک Index Scan بدون Sort
CREATE INDEX orders_cust_created_idx ON factory.orders (customer_id, created_at DESC);

SELECT id, status, created_at
FROM factory.orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 3;

Index Only Scan و INCLUDE

اگر همه‌ی ستون‌های لازم داخل ایندکس باشند، پستگرس می‌تواند اصلاً سراغ جدول نرود (Index Only Scan)، به شرطی که صفحه‌های جدول در visibility map «همه‌قابل‌مشاهده» علامت خورده باشند؛ کاری که VACUUM انجام می‌دهد. با INCLUDE ستون‌هایی را به برگ‌ها اضافه می‌کنید که در جست‌وجو نقشی ندارند و فقط برای خواندن آن‌جا هستند:

CREATE INDEX order_items_order_cover ON factory.order_items (order_id)
  INCLUDE (qty, line_total_rial);

-- جمع هر سفارش بدون خواندن heap
SELECT sum(qty), sum(line_total_rial) FROM factory.order_items WHERE order_id = 1001;

-- در ایندکس UNIQUE، یکتایی فقط روی ستون‌های کلید است، نه ستون‌های INCLUDE
CREATE UNIQUE INDEX looms_code_cover ON factory.looms (code) INCLUDE (hall);

partial index: فقط ردیف‌هایی که مهم‌اند

در کارخانه، ۹۵ درصد سفارش‌ها delivered هستند و صفحه‌ی «کارهای باز» فقط بقیه را می‌خواهد. ایندکس جزئی کوچک‌تر، سریع‌تر و ارزان‌تر در نگهداری است:

CREATE INDEX orders_open_idx ON factory.orders (due_date)
WHERE status IN ('confirmed', 'weaving', 'finishing');

-- یکتایی شرطی: هر مشتری فقط یک سفارش draft
CREATE UNIQUE INDEX one_draft_per_customer ON factory.orders (customer_id)
WHERE status = 'draft';

برنامه‌ریز فقط وقتی از این ایندکس استفاده می‌کند که بتواند اثبات کند شرط کوئری زیرمجموعه‌ی شرط ایندکس است؛ WHERE status = 'weaving' کار می‌کند، اما اگر status را به صورت پارامتر بفرستید (‎$1) و پلن عمومی (generic plan) ساخته شود، ممکن است ایندکس کنار گذاشته شود.

expression index

CREATE INDEX customers_lower_name_idx ON factory.customers (lower(full_name));
CREATE INDEX orders_day_idx ON factory.orders (((created_at AT TIME ZONE 'Asia/Tehran')::date));

SELECT * FROM factory.customers WHERE lower(full_name) = lower('Reza Kashani');

شرط کوئری باید دقیقاً همان عبارت ایندکس باشد و تابع باید IMMUTABLE باشد؛ به همین دلیل created_at::date روی timestamptz قابل ایندکس نیست (به منطقه‌ی زمانی نشست وابسته است) اما نسخه‌ی AT TIME ZONE با منطقه‌ی ثابت هست.

Hash

ایندکس Hash فقط تساوی را پشتیبانی می‌کند و از نسخه‌ی 10 امن (WAL-logged) است. برای ستون‌های متنی بلند مثل توکن یا URL که فقط با = جست‌وجو می‌شوند گاهی کوچک‌تر از B-tree است، اما UNIQUE و مرتب‌سازی ندارد؛ در عمل B-tree انتخاب پیش‌فرض می‌ماند.

نکته‌هایی که کمتر کسی می‌داند

• از نسخه‌ی 13، B-tree مقدارهای تکراری را deduplicate می‌کند؛ ایندکس روی ستون کم‌تنوعی مثل status چند برابر کوچک‌تر از نسخه‌های قدیمی است، اما هنوز معمولاً partial index انتخاب بهتری است.
• B-tree در هر دو جهت پیمایش می‌شود؛ DESC در ایندکس تک‌ستونی بی‌اثر است و فقط در ایندکس چندستونی با جهت‌های مختلط (مثل a ASC, b DESC) معنا دارد.
• از نسخه‌ی 18، «skip scan» اجازه می‌دهد ایندکس (status, created_at) برای شرطی فقط روی created_at هم استفاده شود؛ در 16 و 17 هنوز باید ترتیب ستون‌ها را درست بچینید.
• نمای pg_stat_user_indexes با idx_scan = 0 ایندکس‌های بی‌استفاده را نشان می‌دهد؛ قبل از حذف، مطمئن شوید آمار از ماه‌ها پیش reset نشده و ایندکس پشتیبان UNIQUE یا PK نیست.
• ایندکس روی (a) وقتی (a, b) وجود دارد معمولاً زائد است؛ این ایندکس‌های تکراری را با مقایسه‌ی indkey در pg_index پیدا کنید.