ایندکس‌های پستگرس‌اس‌کیو‌ال: تنها راهنمایی که لازم داری

بیشتر کوئری‌های کند با یک ایندکس خوب‌جای‌گذاری‌شده حل می‌شوند. بیشتر دیتابیس‌ها هم با ده ایندکس بدجای‌گذاری‌شده خراب می‌شوند. این مسیر وسط است.

با مدل ذهنی شروع کن

ایندکس یک کپی مرتب‌شده از یک ستون است که به پستگرس اجازه می‌دهد از اسکن کامل بپرد.

CREATE INDEX idx_posts_slug ON posts (slug);

قبل از هر کاری EXPLAIN بخوان

EXPLAIN ANALYZE SELECT * FROM posts WHERE slug = 'hello';

به دنبال Seq Scan (برای جدول بزرگ بد است) در برابر Index Scan (خوب است) باش.

B-tree: پیش‌فرض

برای =، <، >، BETWEEN، ORDER BY. ۹۵٪ نیازها را می‌پوشاند.

GIN: برای آرایه و متن کامل

CREATE INDEX idx_posts_tags ON posts USING GIN (tags);

ایندکس partial: دامنه را کوچک کن

CREATE INDEX idx_posts_published ON posts (created_at)
WHERE published = true;

ایندکس covering: اسکن بدون دسترسی به جدول

CREATE INDEX idx_posts_title ON posts (title) INCLUDE (excerpt);

حقیقت نگه‌داری

• ایندکس روی نوشتن و دیسک هزینه می‌گذارد؛ هر ایندکس اضافه، insert را کند می‌کند.
• VACUUM و ANALYZE مهم‌اند؛ autovacuum بیشتر کارها را می‌کند.
• به ستونی که کوئری نمی‌زنی ایندکس نده.

جعبه‌ابزار سه‌ایندکسی

1. slug (یکتا) — هر بلاگ و هر CMS.
2. کلیدهای خارجی که join می‌شوند.
3. ستون‌های داغ‌ترین WHERE.

همین. فقط وقتی EXPLAIN ثابت کرد، ایندکس اضافه کن.