آموزش EXPLAIN ANALYZE در PostgreSQL؛ پیدا کردن دلیل کندی کوئریها
چرا باید EXPLAIN را بلد باشید؟ #
کوئریای که روی ۱۰۰ ردیف در چند میلیثانیه اجرا میشود، ممکن است روی ۱۰ میلیون ردیف چند ثانیه طول بکشد. بیشتر وقتها مشکل نه در سختافزار است و نه در PostgreSQL؛ بلکه در طرح اجرای کوئری (Query Plan) است. دستور EXPLAIN دقیقاً همین طرح را به شما نشان میدهد: PostgreSQL قرار است داده را چطور پیدا کند، از کدام ایندکس استفاده کند و جدولها را چطور به هم وصل کند.
این راهنما بر اساس مستندات رسمی PostgreSQL 18 دربارهٔ EXPLAIN نوشته شده است.
EXPLAIN در برابر EXPLAIN ANALYZE #
EXPLAIN: کوئری را اجرا نمیکند؛ فقط طرحی را که برنامهریز (planner) انتخاب کرده، همراه با تخمین هزینه و تعداد ردیف نشان میدهد.EXPLAIN ANALYZE: کوئری را واقعاً اجرا میکند و زمان و تعداد ردیفهای واقعی را کنار تخمینها میگذارد.
EXPLAIN SELECT * FROM tenk1;
Seq Scan on tenk1 (cost=0.00..445.00 rows=10000 width=244)
خطر
EXPLAIN ANALYZE روی UPDATE، DELETE یا INSERT واقعاً داده را تغییر میدهد. همیشه آن را داخل یک تراکنش اجرا و سپس ROLLBACK کنید.
BEGIN;
EXPLAIN ANALYZE UPDATE tenk1 SET hundred = hundred + 1 WHERE unique1 < 100;
ROLLBACK;
خواندن یک خط از طرح اجرا #
خط Seq Scan on tenk1 (cost=0.00..445.00 rows=10000 width=244) را تکهتکه بررسی کنیم:
| بخش | معنی |
|---|---|
cost=0.00 |
هزینهٔ شروع؛ کاری که قبل از برگرداندن اولین ردیف انجام میشود |
..445.00 |
هزینهٔ کل تخمینی تا پایان کار این گره |
rows=10000 |
تعداد ردیفهایی که این گره خروجی میدهد (نه لزوماً تعداد ردیفهای اسکنشده) |
width=244 |
میانگین اندازهٔ هر ردیف خروجی به بایت |
واحد هزینه دلخواه و نسبی است و با میلیثانیه قابلمقایسه نیست. هزینهٔ هر گره، هزینهٔ گرههای فرزندش را هم شامل میشود.
بخش actual در ANALYZE #
با ANALYZE بخشی مثل (actual time=0.003..0.003 rows=1.00 loops=10) اضافه میشود. نکتهٔ مهم: وقتی loops بزرگتر از ۱ است، زمان و تعداد ردیف میانگین هر بار اجرا هستند؛ برای زمان کل باید زمان را در loops ضرب کنید.
نکته
مهمترین چیزی که باید نگاه کنید فاصلهٔ بین rows تخمینی و rows واقعی است. اگر تخمین ۱۰ ردیف بوده اما ۵۰٬۰۰۰ ردیف برگشته، احتمالاً آمار جدول قدیمی است و اجرای ANALYZE table_name کمک میکند.
انواع اسکن #
Seq Scan #
کل جدول را از اول تا آخر میخواند. برای جدولهای کوچک یا وقتی بخش بزرگی از ردیفها لازم است، کاملاً منطقی است.
Index Scan #
با استفاده از ایندکس ردیفها را به ترتیب ایندکس پیدا میکند. برای کوئریهایی که ردیفهای کمی برمیگردانند یا ORDER BY آنها با ایندکس جور است، عالی است.
Index Scan using tenk1_unique1 on tenk1 (cost=0.29..8.30 rows=1 width=244)
Index Cond: (unique1 = 42)
Index Only Scan #
اگر همهٔ ستونهای مورد نیاز داخل خود ایندکس باشند، PostgreSQL اصلاً سراغ جدول نمیرود. عبارت Heap Fetches: 0 یعنی هیچ ردیفی از جدول اصلی خوانده نشده است.
Bitmap Heap Scan #
ابتدا با Bitmap Index Scan محل ردیفها را پیدا میکند، آنها را به ترتیب فیزیکی مرتب میکند و بعد یکجا از جدول میخواند. حد وسط بین Seq Scan و Index Scan است.
انواع Join #
- Nested Loop: برای هر ردیف جدول بیرونی، جدول درونی را جستجو میکند. وقتی ردیفهای بیرونی کم باشند و جدول درونی ایندکس داشته باشد، سریع است.
- Hash Join: از یک جدول، hash table در حافظه میسازد و جدول دیگر را با آن تطبیق میدهد. برای حجمهای متوسط و بزرگ رایج است.
- Merge Join: هر دو ورودی باید روی کلید join مرتب باشند؛ سپس مثل زیپ آنها را کنار هم میگذارد.
گزینهٔ BUFFERS؛ از PostgreSQL 18 پیشفرض است #
BUFFERS نشان میدهد چند بلوک از کش (shared hit) و چند بلوک از دیسک (read) خوانده شده است. در PostgreSQL 18، ANALYZE بهصورت ضمنی BUFFERS را فعال میکند. اگر نمیخواهید نمایش داده شود:
EXPLAIN (ANALYZE, BUFFERS OFF)
SELECT * FROM tenk1 WHERE unique1 < 100 AND unique2 > 9000;
عدد بالای read در مقایسه با hit یعنی داده در حافظه نبوده و از دیسک خوانده شده؛ همین میتواند دلیل کندی اجرای اول کوئری باشد.
EXPLAIN از داخل جنگو #
اگر با Django کار میکنید، لازم نیست SQL را دستی بنویسید. متد QuerySet.explain() روی PostgreSQL گزینههای EXPLAIN را بهصورت آرگومان میپذیرد:
from blog.models import Post
qs = Post.objects.filter(status="published").order_by("-published_at")[:10]
print(qs.explain(analyze=True, buffers=True))
اگر در خروجی Seq Scan روی یک جدول بزرگ دیدید، احتمالاً به ایندکس نیاز دارید:
class Post(models.Model):
# ...
class Meta:
indexes = [
models.Index(fields=["status", "-published_at"]),
]
یادداشت
تعداد کوئریها هم به اندازهٔ سرعت هر کوئری مهم است. برای کاهش تعداد کوئریها، مطلب مشکل N+1 در جنگو و select_related و prefetch_related را بخوانید.
چکلیست سریع تحلیل کوئری کند #
- کوئری را با
EXPLAIN (ANALYZE)اجرا کنید (برای کوئریهای تغییردهنده داخل تراکنش). - گرهی را پیدا کنید که بیشترین زمان واقعی را دارد (زمان × loops).
rowsتخمینی و واقعی را مقایسه کنید.- دنبال
Seq Scanروی جدولهای بزرگ باFilterبگردید. - ایندکس مناسب بسازید یا آمار جدول را با
ANALYZEبهروز کنید و دوباره اندازه بگیرید.
جمعبندی #
EXPLAIN ANALYZE مثل دیباگر برای کوئریهای SQL است. با یادگرفتن چند مفهوم پایه (هزینه، rows، loops، نوع اسکن و نوع join) میتوانید بیشتر کندیهای دیتابیس را خودتان پیدا و برطرف کنید. برای مطالعهٔ عمیقتر، فصل Using EXPLAIN در مستندات PostgreSQL بهترین منبع است.