ایندکس و بهینهسازی کوئری در PostgreSQL: از EXPLAIN تا ایندکس ترکیبی
روش عملی خواندن EXPLAIN ANALYZE، انتخاب B-tree و partial/covering index، و جلوگیری از ایندکسهایی که فقط نوشتن را کند میکنند.
علی مرتضوی
بنیانگذار پارادایس کد
کندی معمولاً از نبود ایندکس نیست؛ از ایندکس اشتباه است
اضافه کردن ایندکس روی هر ستون filter، نوشتن را گران و vacuum را شلوغ میکند بدون اینکه خواندن را نجات دهد. ایندکس خوب از شکل واقعی WHERE، JOIN و ORDER BY میآید—نه از حدس. اول کوئریهای پرک تکرار و کند را از لاگ/`pg_stat_statements` پیدا کنید، بعد برای همان الگو ایندکس طراحی کنید.
هدف، کم کردن صفحهٔ خواندهشده و جلوگیری از sort/seq scan غیرضروری است؛ نه «داشتن ایندکس زیاد».
خواندن EXPLAIN ANALYZE بدون جادو
به `Seq Scan` وحشتزده نشوید؛ روی جدول کوچک گاهی درست است. خطرناک وقتی است که ردیفهای برآوردی و واقعی فاصله دارند، یا `Nested Loop` روی مجموعهٔ بزرگ با هزینهٔ بالا تکرار میشود. به `Buffers` و زمان واقعی هر نود نگاه کنید تا بفهمید I/O است یا CPU/sort.
آمار قدیمی قاتل خاموش است. بعد از تغییرات حجیم داده، `ANALYZE` را جدی بگیرید. اگر planner با واقعیت فاصله دارد، اول آمار و selectivity را درست کنید، بعد ایندکس جدید بسازید.
B-tree، ایندکس ترکیبی و ترتیب ستونها
برای برابری و بازه روی اسکالرها، B-tree پیشفرض درست است. در ایندکس ترکیبی، ترتیب ستونها باید با فیلترهای برابرتیِ پرتکرار و سپس ستونهای range/sort همخوان باشد. `(tenant_id, created_at)` برای «همهٔ رکوردهای یک تننت در بازه زمان» منطقی است؛ برعکسش اغلب به درد نمیخورد.
اگر کوئری فقط چند ستون برمیگرداند، covering/index-only scan با `INCLUDE` میتواند heap fetch را کم کند. آن را برای مسیرهای داغ اندازه بگیرید، نه برای همه SELECTها.
Partial و expression index برای SaaS
در چندمستأجری، partial index مثل `WHERE deleted_at IS NULL` یا `WHERE status = 'active'` ایندکس را کوچک و مرتبط نگه میدارد. expression index برای `LOWER(email)` وقتی جستجوی case-insensitive دارید ضروری است—به شرطی که کوئری همان expression را استفاده کند.
ایندکس جزئی را با الگوی دسترسی محصول همنام کنید تا تیم بداند چرا وجود دارد. ایندکس بدون owner در مهاجرها یتیم میماند.
قفل، نوشتن و هزینه نگهداری ایندکس
هر ایندکس اضافی روی INSERT/UPDATE/DELETE مالیات میگیرد. در جدول نوشتنبالا، دو ایندکس متوسطِ درست بهتر از شش ایندکس «شاید لازم شود» است. برای migration ایندکس روی جدول بزرگ از `CREATE INDEX CONCURRENTLY` استفاده کنید تا قفل طولانی سرویس را نخواباند.
bloat را مانیتور کنید. ایندکس متورم خواندن را هم کُند میکند. در جدولهای append-heavy، استراتژی partition زمانی گاهی از ایندکس غولپیکر بهتر عمل میکند.
بازنویسی کوئری قبل از ایندکس بیشتر
گاهی مشکل ایندکس نیست: `SELECT *`، N+1 از ORM، یا فیلتر روی ستون محاسبهشده در کلاینت. فیلتر را به SQL نزدیک کنید، join را صریح کنید، و pagination کلیددار (`keyset`) را جایگزین OFFSET عمیق کنید. OFFSET 100000 یعنی کار بیهوده تکرارشونده.
برای گزارشهای سنگین، مسیر آنلاین را جدا کنید: جدول خلاصه، materialized view، یا انبار تحلیلی. همان OLTP را برای BI نخهکش نکنید.
حلقهٔ عملی بهینهسازی در تیم
هفتهای یکبار top queries را مرور کنید: p95، فراخوانی، و تغییرات پلن. هر ایندکس جدید باید با کوئری قبل/بعد و دلیل کسبوکاری ثبت شود. این نظم جلوی «ایندکسهای افسانهای» را میگیرد.
بهینهسازی پایدار بخشی از تعریف Done است: فیچر جدید با فیلتر تازه، بدون طرح ایندکس، یعنی بدهی عمدی.
سؤالات متداول
آیا روی هر foreign key ایندکس بگذاریم؟
اگر روی آن JOIN/DELETE/WHERE دارید معمولاً بله. FK بدون الگوی دسترسی تضمینی برای ایندکس جدا نیست؛ با اندازهگیری تصمیم بگیرید.
GIN برای چیست؟
برای JSONB، آرایه و جستجوی تماممتن. برای برابری ساده روی ستون اسکالر، B-tree مناسبتر و ارزانتر است.
چرا بعد از ایندکس هنوز seq scan میبینم؟
ممکن است جدول کوچک باشد، شرط sargable نباشد، آمار قدیمی باشد، یا selectivity پایین باشد. EXPLAIN ANALYZE را با فیلتر واقعی بررسی کنید.
دانش و مقالات
نیاز به اجرای همین مفاهیم در محصولتان دارید؟
پارادایس کد از مشاوره تا پیادهسازی کامل کنار شماست.