آموزش EXPLAIN ANALYZE در PostgreSQL؛ پیدا کردن دلیل کندی کوئری‌ها


چرا باید EXPLAIN را بلد باشید؟ #

کوئری‌ای که روی ۱۰۰ ردیف در چند میلی‌ثانیه اجرا می‌شود، ممکن است روی ۱۰ میلیون ردیف چند ثانیه طول بکشد. بیشتر وقت‌ها مشکل نه در سخت‌افزار است و نه در PostgreSQL؛ بلکه در طرح اجرای کوئری (Query Plan) است. دستور EXPLAIN دقیقاً همین طرح را به شما نشان می‌دهد: PostgreSQL قرار است داده را چطور پیدا کند، از کدام ایندکس استفاده کند و جدول‌ها را چطور به هم وصل کند.

این راهنما بر اساس مستندات رسمی PostgreSQL 18 دربارهٔ EXPLAIN نوشته شده است.

EXPLAIN در برابر EXPLAIN ANALYZE #

  • EXPLAIN: کوئری را اجرا نمی‌کند؛ فقط طرحی را که برنامه‌ریز (planner) انتخاب کرده، همراه با تخمین هزینه و تعداد ردیف نشان می‌دهد.
  • EXPLAIN ANALYZE: کوئری را واقعاً اجرا می‌کند و زمان و تعداد ردیف‌های واقعی را کنار تخمین‌ها می‌گذارد.
sql
EXPLAIN SELECT * FROM tenk1;
Seq Scan on tenk1  (cost=0.00..445.00 rows=10000 width=244)

خطر

EXPLAIN ANALYZE روی UPDATE، DELETE یا INSERT واقعاً داده را تغییر می‌دهد. همیشه آن را داخل یک تراکنش اجرا و سپس ROLLBACK کنید.

sql
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 را فعال می‌کند. اگر نمی‌خواهید نمایش داده شود:

sql
EXPLAIN (ANALYZE, BUFFERS OFF)
SELECT * FROM tenk1 WHERE unique1 < 100 AND unique2 > 9000;

عدد بالای read در مقایسه با hit یعنی داده در حافظه نبوده و از دیسک خوانده شده؛ همین می‌تواند دلیل کندی اجرای اول کوئری باشد.

EXPLAIN از داخل جنگو #

اگر با Django کار می‌کنید، لازم نیست SQL را دستی بنویسید. متد QuerySet.explain() روی PostgreSQL گزینه‌های EXPLAIN را به‌صورت آرگومان می‌پذیرد:

shellpython
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 روی یک جدول بزرگ دیدید، احتمالاً به ایندکس نیاز دارید:

models.pypython
class Post(models.Model):
    # ...
    class Meta:
        indexes = [
            models.Index(fields=["status", "-published_at"]),
        ]

یادداشت

تعداد کوئری‌ها هم به اندازهٔ سرعت هر کوئری مهم است. برای کاهش تعداد کوئری‌ها، مطلب مشکل N+1 در جنگو و select_related و prefetch_related را بخوانید.

چک‌لیست سریع تحلیل کوئری کند #

  1. کوئری را با EXPLAIN (ANALYZE) اجرا کنید (برای کوئری‌های تغییردهنده داخل تراکنش).
  2. گرهی را پیدا کنید که بیشترین زمان واقعی را دارد (زمان × loops).
  3. rows تخمینی و واقعی را مقایسه کنید.
  4. دنبال Seq Scan روی جدول‌های بزرگ با Filter بگردید.
  5. ایندکس مناسب بسازید یا آمار جدول را با ANALYZE به‌روز کنید و دوباره اندازه بگیرید.

جمع‌بندی #

EXPLAIN ANALYZE مثل دیباگر برای کوئری‌های SQL است. با یادگرفتن چند مفهوم پایه (هزینه، rows، loops، نوع اسکن و نوع join) می‌توانید بیشتر کندی‌های دیتابیس را خودتان پیدا و برطرف کنید. برای مطالعهٔ عمیق‌تر، فصل Using EXPLAIN در مستندات PostgreSQL بهترین منبع است.

این پست چطور بود؟

پست‌های مرتبط